Mapping Composite Keys Using Ef Code First

Mapping composite keys using EF code first

You definitely need to put in the column order, otherwise how is SQL Server supposed to know which one goes first? Here's what you would need to do in your code:

public class MyTable
{
  [Key, Column(Order = 0)]
  public string SomeId { get; set; }

  [Key, Column(Order = 1)]
  public int OtherId { get; set; }
}

You can also look at this SO question. If you want official documentation, I would recommend looking at the official EF website. Hope this helps.

EDIT: I just found a blog post from Julie Lerman with links to all kinds of EF 6 goodness. You can find whatever you need here.

How do I map a composite primary key in Entity Framework 4 code first?

You could also use

HasKey(u => new { u.SubscriptionID, u.UserName });

Edit:

One limitation I have found is that the following do not work:

public ProjectAssignmentConfiguration()
{
    HasKey(u => u.Employee.EmployeeId);
    HasKey(u => u.Project.ProjectId);
}

or

public ProjectAssignmentConfiguration()
{
    HasKey(u => new { u.Employee.EmployeeId, u.Project.ProjectId });
}

So how do you set up an entity where the join table has a primary key that is composed of foreign keys?

Composite primary keys and foreign keys Code First

You have to use the ReverseProperty annotation.

public class UserReview
{
    [Key, Column(Order = 0), ForeignKey("Reviewer")]
    public string ReviewerId { get; set; } //The user that wrote the review

    [Key, Column(Order = 1), ForeignKey("ReviewedUser")]
    public string ReviewedUserId { get; set; } //The user who was reviewed

    [Required]
    [Range(1, 5)]
    public int Rating { get; set; }

    [Required]
    public DateTimeOffset DateCreated { get; set; }

    [Required]
    public virtual User Reviewer { get; set; }

    [Required)]
    public virtual User ReviewedUser { get; set; }
}

public class User
{
    [Key]
    [Required]
    public string SubjectId { get; set; }

    public User()
    {                
        Reviews = new List<UserReview>();
    }

    [InverseProperty("ReviewedUser")] // or Reviewer.
    public virtual ICollection<UserReview> Reviews { get; set; }    
}

In my opinon, that's the kind of thing that are easier to configure when using fluent mapping.

Using Composite Keys with Entity Framework Core and Using part of them as foreign keys

Answering your concrete question

Is there anyway to get efcore to do something like this using the code first approach?

Sure there is. Just the conventional foreign key names apparently don't work, so you have to configure the FK properties explicitly (via HasForeignKey fluent API).

e.g. either

builder.Entity<PurchaseOrderLine>()
    .DefaultConfigure(p => new { p.Company, p.PONum, p.LineNum })
    .HasOne(e => e.PurchaseOrder)
    .WithMany(e => e.PurchaseOrderLines)
    .HasForeignKey(e => { e.Company, e.PONum }); // <--

or

builder.Entity<PurchaseOrder>()
    .DefaultConfigure(p => new { p.Company, p.PONum })
    .HasMany(e => e.PurchaseOrderLines)
    .WithOne(e => e.PurchaseOrder)
    .HasForeignKey(e => { e.Company, e.PONum }); // <--

Note that both Has / With pairs represent one and the same relationship, so it's better to do it only in one place in order to avoid conflicting configurations.

Mapping composite foreign key to composite primary key where the foreign key is also a primary key

The main difference between your question and the one I suggested as duplicate is that your ForeignKey attributes don't refer -

• from a primitive property to a navigation property
• from a navigation property to a primitive property

In your case, the reference is from a primitive property to another primitive property, in another type. Also, little detail, VirtualMachine.Datetime should be a property, not a member. But I have to admit that the "duplicate" didn't cover your case.

So let's try to make this into a comprehensive answer how to handle this situation in Entity Framework 6. I'll use an abstracted model to explain the various options:

public class Parent
{
    public int Id1 { get; set; } // Key
    public int Id2 { get; set; } // Key   
    public string Name { get; set; }   
    public virtual List<Child> Children { get; set; }
}

public class Child
{
    public int Id1 { get; set; } // Key
    public int Id2 { get; set; } // Key
    public int Id3 { get; set; } // Key
    public string Name { get; set; }
    public virtual Parent Parent { get; set; }
} 

There are three options to setup the mappings.

Option 1

Data annotations, ForeignKey attribute:

public class Parent
{
    [Key]
    [Column(Order = 1)]
    public int Id1 { get; set; }
    [Key]
    [Column(Order = 2)]
    public int Id2 { get; set; }
    
    public string Name { get; set; }

    public virtual List<Child> Children { get; set; }
}

public class Child
{
    [Key]
    [Column(Order = 0)]
    public int Id1 { get; set; }
    [Key]
    [Column(Order = 1)]
    public int Id2 { get; set; }
    [Key]
    [Column(Order = 2)]
    public int Id3 { get; set; }

    public string Name { get; set; }

    [ForeignKey("Id1,Id2")]
    public virtual Parent Parent { get; set; }
}

As you see, here the ForeignKey attribute refers from a navigation property to primitive properties. Also, the absolute numbers in the column order don't matter, only their sequence.

Option 2

Data annotations, InverseProperty attribute:

public class Parent
{
    [Key]
    [Column(Order = 1)]
    public int Id1 { get; set; }
    [Key]
    [Column(Order = 2)]
    public int Id2 { get; set; }
    
    public string Name { get; set; }

    public virtual List<Child> Children { get; set; }
}

public class Child
{
    [Key]
    [Column(Order = 0)]
    [InverseProperty("Children")]
    public int Id1 { get; set; }
    [Key]
    [Column(Order = 1)]
    [InverseProperty("Children")]
    public int Id2 { get; set; }
    [Key]
    [Column(Order = 2)]
    public int Id3 { get; set; }

    public string Name { get; set; }

    public virtual Parent Parent { get; set; }
}

InverseProperty points from one or more properties in a type at one end of a relationship to a navigation property in the type on the other end of the relationship. Another way to achieve the same mapping is to apply [InverseProperty("Parent")] on both key properties of Parent.

Option 3

Fluent mapping:

modelBuilder.Entity<Parent>().HasKey(p => new { p.Id1, p.Id2 });
modelBuilder.Entity<Child>().HasKey(p => new { p.Id1, p.Id2, p.Id3 });
modelBuilder.Entity<Parent>()
    .HasMany(p => p.Children)
    .WithRequired(c => c.Parent)
    .HasForeignKey(c => new { c.Id1, c.Id2 });

As said in the comments, fluent mapping is less error-prone than data annotations. Data annotations offer too many options to configure mappings and it's not always easy to see which parts are connected. That's why fluent mapping is my favorite.

Entity Framework Core

In EF-core (current version 3.1.6) composite primary keys can't be modeled by data annotations. It throws a run-time exception:

Entity type 'Parent' has composite primary key defined with data annotations. To set composite primary key, use fluent API.

So for EF-core only option 3 is feasible. The mapping is almost identical:

modelBuilder.Entity<Parent>().HasKey(p => new { p.Id1, p.Id2 });
modelBuilder.Entity<Child>().HasKey(p => new { p.Id1, p.Id2, p.Id3 });
modelBuilder.Entity<Parent>()
    .HasMany(p => p.Children)
    .WithOne(c => c.Parent) // Different here
    .HasForeignKey(c => new { c.Id1, c.Id2 });

Entity Framework Core Composite Key In One To Many Mapping

So, to close this out -

This is something not currently possible in EF Core (2.1) and remains to be seen if it will be added in future versions - as present it only supports mapping through a single property

Entity Framework Code First mapping composite key in join table where foreign keys are not exposed in model

Each column of the composite primary key has to be mapped to a scalar property in the entity. Your ideal solution will not work.

In Option 2 you would have to create a unique key also and check for unique key violations. EF does not have built in support for unique keys. Hence I would pick option 1.

Creating Composite Key Entity Framework

If Device table has composite primary key, then you need same composite foreign key on your NotificationMessageDevice table. How would SQL find Device without full primary key? Also you should make these fields to be part of NotificationMessageDevice table primary key. Otherwise you can't guarantee primary key will be unique:

public class NotificationMessageDevice
{
    [Column(Order = 0), Key, ForeignKey("NotificationMessage")]
    public int NotificationMessage_ID { get; set; }

    [Column(Order = 1), Key, ForeignKey("Device")]
    public int Device_ID { get; set; }
    [Column(Order = 2), Key, ForeignKey("Device")]
    public string Device_UDID { get; set; }
    [Column(Order = 3), Key, ForeignKey("Device")]
    public string Device_ApplicationKey { get; set; }

    public virtual Device Device { get; set; }
    public virtual NotificationMessage NotificationMessage { get; set; }
}

---

Related Topics