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 ?

score 203 · Accepted Answer · answered Mar 26 '11 at 09:56

203

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

answered Mar 26 '11 at 09:56

Ladislav Mrnka

360,892
59
660
670

Do I need to keep the virtual properties ( public virtual Category Category { get; set; }) as well as data annovations ? – DotnetSparrow Mar 26 '11 at 16:24
4

`virtual` on navigation properties is necessary for lazy loading. `virtual` on scalar properties helps with change tracking of attached objects. – Ladislav Mrnka Mar 26 '11 at 16:27
4

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? – Feb 29 '12 at 21:13
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! – gdoron Aug 29 '12 at 11:56
@gdoron: `Product` has `Category` in my answer. – Ladislav Mrnka Aug 29 '12 at 12:26
@Ohhh sorry, There is a line break so I didn't see it... Just like [banner blindness](http://en.wikipedia.org/wiki/Banner_blindness) :) Thanks. – gdoron Aug 31 '12 at 11:55
Hi, Ladislav Mrnka. According to the entity framework skills that you have, please see my question. Maybe you can , introduce me to a solution. http://stackoverflow.com/q/23665133/1395101 Thanks a lot. – Amin Ghaderi May 19 '14 at 20:48
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. – Luke May 21 '17 at 13:55
@LadislavMrnka, what if the referenced foreign keys are of two different entities? I am grappling with precisely such a problem now. – Najeeb Nov 12 '17 at 14:40
Is it possible to convert non-primitive value object containing two fields? : `public class MyId { public int CompanyId; public int UserId } ` – zolty13 Jul 29 '20 at 13:13
Do the column orders in both `Category` and `Product` need to match? – emilaz Apr 10 '23 at 17:01

score 36 · Answer 2 · answered Aug 26 '17 at 22:30

36

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

answered Aug 26 '17 at 22:30

Christophe

597
4
14

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 – RoLYroLLs Mar 21 '18 at 21:00
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" – D. Kermott Jun 21 '18 at 13:53
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); – Christophe Aug 18 '18 at 22:35

Ogglas · Answer 3 · 2022-02-25T13:47:28.667

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

https://learn.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; }
}

composite key as foreign key

3 Answers3

Linked