Mapping composite keys using EF code first
Solution 1
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.
Solution 2
For Mapping Composite primary key using Entity framework we can use two approaches.
1) By Overriding the OnModelCreating() Method
For ex: I have the model class named VehicleFeature as shown below.
public class VehicleFeature
{
public int VehicleId { get; set; }
public int FeatureId{get;set;}
public Vehicle Vehicle{get;set;}
public Feature Feature{get;set;}
}
The Code in my DBContext would be like ,
public class VegaDbContext : DbContext
{
public DbSet<Make> Makes{get;set;}
public DbSet<Feature> Features{get;set;}
public VegaDbContext(DbContextOptions<VegaDbContext> options):base(options)
{
}
// we override the OnModelCreating method here.
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<VehicleFeature>().HasKey(vf=> new {vf.VehicleId, vf.FeatureId});
}
}
2) By Data Annotations.
public class VehicleFeature
{
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Key]
public int VehicleId { get; set; }
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Key]
public int FeatureId{get;set;}
public Vehicle Vehicle{get;set;}
public Feature Feature{get;set;}
}
Please refer the below links for the more information.
1) https://msdn.microsoft.com/en-us/library/jj591617(v=vs.113).aspx
2) How to add a composite unique key using EF 6 Fluent Api?
Solution 3
I thought I would add to this question as it is the top google search result.
As has been noted in the comments, in EF Core there is no support for using annotations (Key attribute) and it must be done with fluent.
As I was working on a large migration from EF6 to EF Core this was unsavoury and so I tried to hack it by using Reflection to look for the Key attribute and then apply it during OnModelCreating
// get all composite keys (entity decorated by more than 1 [Key] attribute
foreach (var entity in modelBuilder.Model.GetEntityTypes()
.Where(t =>
t.ClrType.GetProperties()
.Count(p => p.CustomAttributes.Any(a => a.AttributeType == typeof(KeyAttribute))) > 1))
{
// get the keys in the appropriate order
var orderedKeys = entity.ClrType
.GetProperties()
.Where(p => p.CustomAttributes.Any(a => a.AttributeType == typeof(KeyAttribute)))
.OrderBy(p =>
p.CustomAttributes.Single(x => x.AttributeType == typeof(ColumnAttribute))?
.NamedArguments?.Single(y => y.MemberName == nameof(ColumnAttribute.Order))
.TypedValue.Value ?? 0)
.Select(x => x.Name)
.ToArray();
// apply the keys to the model builder
modelBuilder.Entity(entity.ClrType).HasKey(orderedKeys);
}
I haven't fully tested this in all situations, but it works in my basic tests. Hope this helps someone
Solution 4
Through Configuration, you can do this:
Model1
{
int fk_one,
int fk_two
}
Model2
{
int pk_one,
int pk_two,
}
then in the context config
public class MyContext : DbContext
{
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Model1>()
.HasRequired(e => e.Model2)
.WithMany(e => e.Model1s)
.HasForeignKey(e => new { e.fk_one, e.fk_two })
.WillCascadeOnDelete(false);
}
}
Comments
-
loyalflow almost 2 years
Sql server table:
SomeId PK varchar(50) not null OtherId PK int not null
How should I map this in EF 6 code first?
public class MyTable { [Key] public string SomeId { get; set; } [Key] public int OtherId { get; set; } }
I've seen some examples where you have to set the order for each column, is that required?
Is there official documentation on this somewhere?
-
Mir almost 10 yearsHow do you do this through an EntityConfiguration? I don't actually have an entity for the join table... I just have the two entities and an EntityConfiguration on one of them with a .Map() to set up the mapping.
-
Davor about 9 years
otherwise how is SQL Server supposed to know which one goes first?
- why not the same way it knows the order for every other column? -
Charlie over 7 yearsWhere in the context config?
-
philn5d about 7 yearsIf you are configuring context via code using Fluent API... section 7. public class MyContext : DbContext { protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<Model1>() .HasRequired(e => e.Model2) .WithMany(e => e.Model1s) .HasForeignKey(e => new { e.fk_one, e.fk_two }) .WillCascadeOnDelete(false); } }
-
kiml42 over 5 yearsI found I had to use ModelBuilder instead of DbModelBuilder on dotnet core.
-
Tobias J over 5 yearsFYI for EF Core, Option #2 is not possible, "Composite keys can only be configured using the Fluent API - conventions will never setup a composite key and you can not use Data Annotations to configure one."
-
Sylvain Gantois about 5 yearsEF doesn't know the order of other columns, you can insert with columns in any order as long as the names are specified. If EF requires the order for the composite PK it has to be related to indexing.
-
Jacob Stamm about 5 years@Davor I imagine the EF creators could have used reflection to infer key/column order, but perhaps there are performance considerations for not doing this. I'll take design-time specificity over slower performance any day, especially in my DAL.
-
fjch1997 over 3 yearsI think it makes sense to require order in advance. If you one day reorder those properties, you'd be quite mad to find out that entity framework stopped working.
-
MarkMYoung almost 3 years@Davor, the order is used by
TEntity Find(params object[] keyValues)
to know the order of the values inkeyValues
.