110

I'm creating a POCO model to use with entity framework code first CTP5. I'm using the <key()> decoration to make a property map to a PK column. But how can I define a PK on more then one column, and specifically, how can I control order of the columns in the index? Is it a result of the order of properties in the class?

Edit: look at @kara's answer for an updated solution.

Thanks!

GilShalit
  • 6,175
  • 9
  • 47
  • 68

5 Answers5

167

NOTE: As of 2019 this answer became non-valid for later EntityFramework versions.

You can specify the column order in the attributes, for instance:

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

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

    [Key, Column(Order=2)]
    public string MyThirdKeyProperty { get; set; }

    // other properties
}

If you are using the Find method of a DbSet you must take this order for the key parameters into account.

Jari Turkia
  • 1,184
  • 1
  • 21
  • 37
Slauma
  • 175,098
  • 59
  • 401
  • 420
  • 2
    InvalidOperationException: Entity type 'XXX' has composite primary key defined with data annotations. To set composite primary key, use fluent API. – Luca Ziegler Nov 17 '19 at 18:07
71

To complete the correct answer submitted by Slauma, you can use the HasKey method to specify an order for composite primary keys as well:

public class User
{        
    public int UserId { get; set; }       
    public string Username { get; set; }        
}        

public class Ctp5Context : DbContext
{
    public DbSet<User> Users { get; set; }        

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>().HasKey(u => new 
        { 
            u.UserId, 
            u.Username 
        });
    }
}
Morteza Manavi
  • 33,026
  • 6
  • 100
  • 83
  • 2
    Thanks - both methods do work fine. I prefer the Attributes because I'm generating my classes from code, and attributes are much more concise. – GilShalit Feb 10 '11 at 08:29
  • I personally also add the Propety(x...).HasColumnOrder(0...n) to each of the keyed properties. Is that good, bad, indifferent? – Suamere Mar 26 '15 at 15:13
  • This answer will work for latest EF-versions. – Jari Turkia Feb 20 '23 at 12:39
12

If, like me, you prefer to use a configuration file you can do that in this way (based on Manavi's example):

public class User
{
    public int UserId { get; set; }
    public string Username { get; set; }
}  

public class UserConfiguration : EntityTypeConfiguration<User>
{
    public UserConfiguration()
    {
        ToTable("Users");
        HasKey(x => new {x.UserId, x.Username});
    }
}

Obviously you have to add the configuration file to your context:

public class Ctp5Context : DbContext
{
    public DbSet<User> Users { get; set; }        

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
         modelBuilder.Configurations.Add(new UserConfiguration());
    }
}
Daniele Armanasco
  • 7,289
  • 9
  • 43
  • 55
2

In EF Core 7.0 a new attribute for classes [PrimaryKey] was introduced.

Example:

[PrimaryKey(nameof(FirstName),nameof(LastName))]
public class Person
{
    public string FirstName { get; set; }

    public string LastName { get; set; }

    public string SomeProperty { get; set; }
}

This way, you don't have to use the fluent-api again.

kara
  • 3,205
  • 4
  • 20
  • 34
  • 1
    Thank you @kara. This question was asked in 2011, referring to ef5 and was consistently popular. It documents the change through ef6 and now efc7, and should be turned into a wiki entry... – GilShalit May 26 '23 at 15:04
0

Use as a anonymous object:

modelBuilder.Entity<UserExamAttemptQuestion>().ToTable("Users").HasKey(o => new { o.UserId, o.Username });
Kos
  • 4,890
  • 9
  • 38
  • 42
WACS kumara
  • 1
  • 2