复合键作为外键

Question

我在 MVC 3 应用程序中使用实体框架 4.1。我有一个实体，其中主键由两列（复合键）组成。这在另一个实体中用作外键。如何建立关系？在正常情况下，我们使用：

public class Category
{
    public string CategoryId { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Product> Products { get; set; }
}

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }
    public string CategoryId { get; set; }

    public virtual Category Category { get; set; }
} 

但是如果类别有两列键怎么办？

Answer 1

您可以使用 Fluent API：

public class Category
{
    public int CategoryId1 { get; set; }
    public int CategoryId2 { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Product> Products { get; set; }
}

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }
    public int CategoryId1 { get; set; }
    public int CategoryId2 { get; set; }

    public virtual Category Category { get; set; }
}

public class Context : DbContext
{
    public DbSet<Category> Categories { get; set; }
    public DbSet<Product> Products { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Category>()
            .HasKey(c => new {c.CategoryId1, c.CategoryId2});

        modelBuilder.Entity<Product>()
            .HasRequired(p => p.Category)
            .WithMany(c => c.Products)
            .HasForeignKey(p => new {p.CategoryId1, p.CategoryId2});

    }
}

或数据注释：

public class Category
{
    [Key, Column(Order = 0)]
    public int CategoryId2 { get; set; }
    [Key, Column(Order = 1)]
    public int CategoryId3 { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Product> Products { get; set; }
}

public class Product
{
    [Key]
    public int ProductId { get; set; }
    public string Name { get; set; }
    [ForeignKey("Category"), Column(Order = 0)]
    public int CategoryId2 { get; set; }
    [ForeignKey("Category"), Column(Order = 1)]
    public int CategoryId3 { get; set; }

    public virtual Category Category { get; set; }
}

Answer 2

我相信最简单的方法是在导航属性上使用数据注释，如下所示：
[ForeignKey("CategoryId1, CategoryId2")]

public class Category
{
    [Key, Column(Order = 0)]
    public int CategoryId1 { get; set; }
    [Key, Column(Order = 1)]
    public int CategoryId2 { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Product> Products { get; set; }
}

public class Product
{
    [Key]
    public int ProductId { get; set; }
    public string Name { get; set; }
    public int CategoryId1 { get; set; }
    public int CategoryId2 { get; set; }

    [ForeignKey("CategoryId1, CategoryId2")]
    public virtual Category Category { get; set; }
}

Answer 3

在 .NET Core 和 .NET 5 < 中，文档仅显示数据注释（简单键）。

https://learn.microsoft.com/en-us/ef/core/modeling/relationships?tabs= Fluent-api%2C Fluent-api-composite-key%2Csimple-key#foreign-key< /a>

但是，使用 @LadislavMrnka 的示例，您将收到如下错误消息：

System.InvalidOperationException：有多个属性
[ForeignKey] 属性指向导航“”。定义一个
使用数据注释的复合外键，使用 [ForeignKey]
导航上的属性。

使用该错误消息，您可以编写如下代码：

public class Product
{
    [Key]
    public int ProductId { get; set; }
    public string Name { get; set; }

    public int CategoryId2 { get; set; }

    public int CategoryId3 { get; set; }

    [ForeignKey("CategoryId2,CategoryId3")]
    public virtual Category Category { get; set; }
}

来自 Microsoft 的 Fluent API（复合键） 示例：

internal class MyContext : DbContext
{
    public DbSet<Car> Cars { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Car>()
            .HasKey(c => new { c.State, c.LicensePlate });

        modelBuilder.Entity<RecordOfSale>()
            .HasOne(s => s.Car)
            .WithMany(c => c.SaleHistory)
            .HasForeignKey(s => new { s.CarState, s.CarLicensePlate });
    }
}

public class Car
{
    public string State { get; set; }
    public string LicensePlate { get; set; }
    public string Make { get; set; }
    public string Model { get; set; }

    public List<RecordOfSale> SaleHistory { get; set; }
}

public class RecordOfSale
{
    public int RecordOfSaleId { get; set; }
    public DateTime DateSold { get; set; }
    public decimal Price { get; set; }

    public string CarState { get; set; }
    public string CarLicensePlate { get; set; }
    public Car Car { get; set; }
}