Saturday, November 20, 2010

Creating Composite Keys using Code First with Entity Framework

Update: For Entity Framework 4.1 RTM, the exception message is a little bit different. It suggests to use the ColumnAttribute instead of DataMemberAttribute. At the time of EF CTP 4, the Column attribute did not exist yet, so they were temporary using the DataMember attribute. I have updated this post to use Column attribute as the latest EF version.

I am starting using the Entity Framework CTP 4, that includes code first approach with data annotations.
When creating composite primary keys, you need specify the order of the primary keys, otherwise you will get an exception similar to:
System.InvalidOperationException : Unable to determine composite primary key ordering for type 'PlaylistTrack'. Use the ColumnAttribute or the HasKey method to specify an order for composite primary keys.

As the exception message describes, one option is to use data annotations and add the Key and Column attributes to define the composite key as shown below:

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

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

        [RelatedTo(ForeignKey = "PlaylistId")]
        public Playlist Playlist { get; set; }

        [RelatedTo(ForeignKey = "TrackId")]
        public Track Track { get; set; }
    }

Another option is to define the composite key using the HasKey method. In this option, the entity class will be:

public class PlaylistTrack
    {
        public int PlaylistId { get; set; }
        public int TrackId { get; set; }

        [RelatedTo(ForeignKey = "PlaylistId")]
        public Playlist Playlist { get; set; }

        [RelatedTo(ForeignKey = "TrackId")]
        public Track Track { get; set; }
    }

And the composite key is defined using the HasKey method when building the model:

var builder = new ModelBuilder();

    //...

    builder.Entity<PlaylistTrack>().HasKey(p=>new {p.PlaylistId, p.TrackId});

    //...

    model = builder.CreateModel();

My personal choice is to use data annotations which were introduced since EF4 CTP3.

7 comments:

Anonymous said...

Thank you for your excellent article. I googled blogs as well as forums, but they are not providing clear examples like yours.
johnaspnet

Anonymous said...

Your comment about having the composite primary key defined in the mapping class appear in the same order as the database table is right on. I had been tripped up with this until I stumbled upon this article. Much thanks!

web hosting said...

Hi, Thank you for your excellent article. I googled blogs as well as forums, but they are not providing clear examples like yours... webhosting

Bob said...

I tried both methods and then tried the equivalent of:

var rec = db.PlaylistTrack.Find(new { nPlayListId, nTrackId });

but it throws an ArgumentException: "The number of primary key values passed must match number of primary key values defined on the entity.
Parameter name: keyValues"

Any ideas?

Luis Rocha said...

Bob,

Are you trying to find an entity object by the primary key? If so, the DbSet Find takes a “params object[]” as it’s parameter so if you have composite keys you just specify the values for each key property in the order you defined them. In the example above, it would be:

var rec = db.PlaylistTrack.Find(nPlayListId, nTrackId);

I hope this helps. Thanks,
Luis

Anonymous said...

Hi, I noticed that when I use composite keys, no Ids are generated by EF. Is there a way to tell EF to generate the keys?

Morten Hoffmann Sørensen said...

Nice write up! In my opinion the fluent approach wins, since this enables you to free the domain (the POCO classes) from EF dependency polution (PI).