Entity Framework Composite Primary Key

Entity Framework composite primary keys could be tricky to handle. Although I recommend avoid such conditions ,both defining the keys and referencing that Class in another class are discussed in this tutorial. Let’s consider an example, my favorite Hotel case, where you have three classes: Guest, Reservation and Room. Reservation Model needs to have a composite primary key, Guest Id and Date of reservation. Room class is actually a middle table and keeps track of the Guest’s room for a specific reservation thus it needs a foreign key to the reservation table. Classes’ codes are as follow:

    public class Guest

    {

        public int Id { get; set; }

        public string FirstName { get; set; }

        public string SurName { get; set; }

        public DateTime DateOfBirth { get; set; }

        public byte Gender { get; set; }

        public int ReservationId { get; set; }

        public Reservation Reservation { get; set; }

    }

    public class Reservation

    {

        public DateTime Date { get; set; }

        public int GuestId { get; set; }

        public virtual Guest Guest { get; set; }

    }

    public class Room

    {

        public int RoomNumber { get; set; }

        public DateTime Date { get; set; }

        public int GuestId { get; set; }

        public Reservation Reservation { get; set; }

    }

 

Now we need to specify the multiple primary key for reservation class and multiple foreign key for the room class. We use fluent API to do this:

Composite Primary Key

    public class ReservationMap : EntityTypeConfiguration<Reservation>

    {

        public ReservationMap()

        {

            // Defining multiple primary key

            this.HasKey(t => new {t.Date,t.GuestId});

            this.HasRequired(t => t.Guest).WithMany();

        }

    }

 

Composite Foreign Key

    public class RoomnMap : EntityTypeConfiguration<Room>

    {

        public RoomnMap()

        {

            // Defining multiple primary key

            this.HasKey(t => new { t.RoomNumber,t.Date, t.GuestId });

            // Referencing Reservation Class

            this.HasRequired(t => t.Reservation);

        }

    }

 

Please note that Room Class also has Composite primary key. Room class has 2 fields which are the same name of those of Reservation class. This is the key point here, after we define the relationship with the Reservation class EF automatically uses these two fields for foreign keys.

Complex Primary Key/Foreign Key

The generated scripts by EF are like this:

CREATE TABLE [dbo].[Rooms] (

    [RoomNumber] INT      NOT NULL,

    [Date]       DATETIME NOT NULL,

    [GuestId]    INT      NOT NULL,

    CONSTRAINT [PK_dbo.Rooms] PRIMARY KEY CLUSTERED ([RoomNumber] ASC, [Date] ASC, [GuestId] ASC),

    CONSTRAINT [FK_dbo.Rooms_dbo.Reservations_Date_GuestId] FOREIGN KEY ([Date], [GuestId]) REFERENCES [dbo].[Reservations] ([Date], [GuestId]) ON DELETE CASCADE

);

 

 

GO

CREATE NONCLUSTERED INDEX [IX_Date_GuestId]

    ON [dbo].[Rooms]([Date] ASC, [GuestId] ASC);

 

Although we successfully created the relation and tables you should have noticed the design pitfall I deliberately put there. The Room class is redundant because you just want to assign the room number to a reservation and this easily can be done using the Reservation table (placing the RoomNumber in Reservation). This is actually what happens with most of the designs like this. As I said in the introduction I strongly recommend you avoid Multiple Primary/Foreign keys.

Read 1494 times Last modified on Monday, 17 August 2015 06:13
Rate this item
0
(0 votes)
About Author
Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

Advanced Programming Concepts
News Letter

Subscribe our Email News Letter to get Instant Update at anytime