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.

Tuesday, November 16, 2010

Chinook Sample Database 1.3 Released

The Chinook Database is a sample database available for multiple database systems. It can be created by running a single SQL script. Chinook database is an alternative to the Northwind database, being ideal for demos and testing ORM tools targeting single and multiple database servers.


The Chinook Database represents a media store, including tables for artists, albums, media tracks, invoices and customers. The media information was generated from my iTunes library file, but anyone can download its source code and use their own iTunes library information. The sales information is auto-generated for a 4 years period. You can see the Chinook data model here.


This new version includes support for SQLite and EffiProz. EffiProz is a cross-platform embedded database written entirely in C#. Chinook database is available for:


  • EffiProz
  • MySQL
  • Oracle
  • SQL Server
  • SQL Server Compact
  • SQLite
You can download the latest release from here. There are available SQL scripts for each database system and also embedded database files for SQL Server Compact and SQLite.