composite key as foreign key

Question

I am using Entity framework 4.1 in MVC 3 application. I have an entity where I have primary key consists of two columns ( composite key). And this is being used in another entity as foreign key. How to create the relationship ? In normal scnerios we use :

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; }
} 

but what if category has two columns key ?

Solution 1

You can use either 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});

    }
}

Or data annotations:

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; }
}

Solution 2

I believe the easiest way is to use Data Annotation on the Navigation property like this: [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; }
}

Solution 3

In .NET Core and .NET 5 < the documentation only shows Data annotations (simple key).

https://docs.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-composite-key%2Csimple-key#foreign-key

However using the example from @LadislavMrnka you will get a error message like this:

System.InvalidOperationException: There are multiple properties with the [ForeignKey] attribute pointing to navigation ''. To define a composite foreign key using data annotations, use the [ForeignKey] attribute on the navigation.

Using that error message you can write the code like this:

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; }
}

Fluent API (composite key) example from Microsoft:

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; }
}

Comments

[DotnetSparrow]

Do I need to keep the virtual properties ( public virtual Category Category { get; set; }) as well as data annovations ?

[Ladislav Mrnka]

virtual on navigation properties is necessary for lazy loading. virtual on scalar properties helps with change tracking of attached objects.

[Admin]

What would you do if the foreign key table's column names were different than what is in the parent? Fore example, In product, how would you label the ForeignKey attribute if the column names looked like: PCategoryId2, PCategoryId3?

[gdoron is supporting Monica]

Regarding to this line: .HasRequired(p => p.Category) but Product doesn't have a property of the Entity Catagory but two ids which make the composite key of a catagory. Can you please explain, because I believe it won't even compile... Thanks!

[Ladislav Mrnka]

@gdoron: Product has Category in my answer.

[gdoron is supporting Monica]

@Ohhh sorry, There is a line break so I didn't see it... Just like banner blindness :) Thanks.

[Amin Ghaderi]

Hi, Ladislav Mrnka. According to the entity framework skills that you have, please see my question. Maybe you can , introduce me to a solution. stackoverflow.com/q/23665133/1395101 Thanks a lot.

[Luke]

In your Data Annotation solution both keys come from the same class, but what if the composite PK refer to FK from different tables? For instance: In my project CategoryTrans composite PK refer to Category PK and ISO_Language PK.

[Najeeb]

@LadislavMrnka, what if the referenced foreign keys are of two different entities? I am grappling with precisely such a problem now.

[RoLYroLLs]

This worked great. I too prefer to use this on Navigation properties. However, how can I set cascadeDelete: false for this property only, not site-wide? Thanks

[D. Kermott]

In some cases the foreign key is also part of the current table's composite key. This way worked. The other way (@Ladislov) did not. I got the error: "Duplicate Column attribute"

[Christophe]

RoLYroLLs: cascadeDelete is set in the migration file (after using the add-migration package manager command). An example: AddForeignKey("dbo.Product", "GuidedActivityID", "dbo.GuidedActivity", "ID", cascadeDelete: false);

[zolty13]

Is it possible to convert non-primitive value object containing two fields? : public class MyId { public int CompanyId; public int UserId }