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

Question

I'm getting to grips with EF4 code first, and liking it so far. But I'm having trouble mapping an entity to a table with a composite primary key.

The configuration I've tried looks like this:

public SubscriptionUserConfiguration()

    {
                Property(u => u.SubscriptionID).IsIdentity();
                Property(u => u.UserName).IsIdentity();
    }

Which throws this exception: Unable to infer a key for entity type 'SubscriptionUser'.

What am I missing?

Clerc

Answer 1

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?

Trompe

Just had this same problem, my symptoms were that it was returning the right amount of entities but all the entities that had the same first key returned with the data contents of the first record, ignoring the second. Also seemed to do funny things with my inserts and plug in the data of other records already in the database. – Heliotropism

You can just add the foreign key onto the entity. For example, instead of u.Project.Id you can add Project and ProjectId to the parent entity. – Keister

Answer 2

I will try to explain it step by step, using the following Entity

public class Account
{
    public int AccountId1 { get; set; }
    public int AccountId2 { get; set; }
    public string Description { get; set; }
}

1. Create a class derived from the EntityTypeConfiguaration<TEntity> Object to override the conventions

   class AccountEntityTypeConfiguration : EntityTypeConfiguration<Account>
   {
   
       public AccountEntityTypeConfiguration()
       {
         // The Key
         // The description of the HasKey Method says
         // A lambda expression representing the property to be used as the primary key.
         // If the primary key is made up of multiple properties then specify an anonymous type including the properties.
         // Example C#: k => new { k.Id1, k.Id2 }
         // Example VB: Function(k) New From { k.Id1, k.Id2 }
         this.HasKey(k => new { k.AccountId1, k.AccountId2 } );  // The Key
   
         // Maybe the key properties are not sequenced and you want to override the conventions
         this.Property(p => p.AccountId1).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.None);
         this.Property(p => p.AccountId2).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.None);
   
         this.Property(p => p.Description).IsRequired();  // This property will be required
         this.ToTable("Account");  // Map the entity to the table Account on the database
       }
   }
   

2. When create the class derived from the DbContext Object, override OnModelCreating Method and add a new AccountEntityTypeConfiguration object to the Configurations of the model Builder.

   public class MyModelAccount : DbContext
   {
       public DbSet<Account> Accounts { get; set;}
   
       protected override void OnModelCreating(DbModelBuilder modelBuilder)
       {
           // Add a new AccountEntityTypeConfiguration object to the configuration of the model, that will be applied once the model is created. 
           modelBuilder.Configurations.Add(new AccountEntityTypeConfiguration());
       }
   
   }
   

Hope it helps you!

Stillhunt

Answer 3

You can also use the Column attribute

public class UserProfileRole
{
    [Key, Column(Order = 0)]
    public int UserId { get; set; }

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

Hilar

just a heads up for those searching - ColumnAttribute requires .NET 4.5 – Antananarivo

Answer 4

Solved it: I should be using HasKey, not Identity. This works:

public SubscriptionUserConfiguration()
{
     HasKey(u => u.SubscriptionID);
     HasKey(u => u.UserName);
}

Clerc

Or maybe it's necessary to use the form suggested by Daniel? #3299768 I'm assuming it worked for you fine, though. – Egger