Creating Composite Key Entity Framework

Question

Shortly, I want to create composite keys on my table remaining with the primary key in order to improve sql server search performance. The performance issue occurs on 200k data table whenever I search an entity without primary key (i.e a string of GUID). Assume that I have 3 classes

public class Device{      public int ID { get; set; }      public string UDID { get; set; }     public string ApplicationKey { get; set; }     public string PlatformKey { get; set; }      public ICollection<NotificationMessageDevice> DeviceMessages { get; set; }  }  public class NotificationMessageDevice {       [Column(Order = 0), Key, ForeignKey("NotificationMessage")]     public int NotificationMessage_ID { get; set; }      [Column(Order = 1), Key, ForeignKey("Device")]     public int Device_ID { get; set; }      public virtual Device Device { get; set; }     public virtual NotificationMessage NotificationMessage { get; set; } }  public class NotificationMessage {       public int ID { get; set; }     public string Text { get; set; }     public DateTime CreateDate { get; set; } }          modelBuilder.Entity<Device>().HasKey(t => new { t.ID, t.ApplicationKey, t.PlatformKey, t.UDID }); 

What the problem is that whenever I want to make ID , UDID , ApplicationKey and PlatformKey define as a Composite Key with modelBuilder it gives the following error.

NotificationMessageDevice_Device_Target_NotificationMessageDevice_Device_Source: : The number of properties in the Dependent and Principal Roles in a relationship constraint must be identical

I think the problem is because the navigation property on NotificationMessageDevice is not able to recognize what the primary key is on Device table. How can I resolve this problem? In addition to this I will be glad if you share your experiences improving the search performance on Entity framework. Usually the performance issue occurs on whenever I use First method without primary keys.

Answer 1

If Device table has composite primary key, then you need same composite foreign key on your NotificationMessageDevice table. How would SQL find Device without full primary key? Also you should make these fields to be part of NotificationMessageDevice table primary key. Otherwise you can't guarantee primary key will be unique:

public class NotificationMessageDevice {     [Column(Order = 0), Key, ForeignKey("NotificationMessage")]     public int NotificationMessage_ID { get; set; }      [Column(Order = 1), Key, ForeignKey("Device")]     public int Device_ID { get; set; }     [Column(Order = 2), Key, ForeignKey("Device")]     public string Device_UDID { get; set; }     [Column(Order = 3), Key, ForeignKey("Device")]     public string Device_ApplicationKey { get; set; }      public virtual Device Device { get; set; }     public virtual NotificationMessage NotificationMessage { get; set; } }