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.

Phalanx

I'm not much for EF development, but don't you have the ForeignKeyAttribute on the wrong thing? – Engird

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; }
}

Dreamy

Is this must that we need to implement all the properties defined on composite key should be implemented at target table whenever we use Device object as a navigation property. – Phalanx

Yes, this will be foreign key in database. Foreign key is a primary key from parent table, so it should be exactly same. Btw if you want PlatformKey to be part of device PK, you need that field in NotificationMessageDevice also – Dreamy

Hmm one more question, is there any way to create super key on Entity Framework then ? – Phalanx

@Phalanx as far as I know, nope, no super keys – Dreamy

@Phalanx & SergeyBerezovskiy SQL PK and UNIQUE NOT NULL declare superkeys. The relational meaning of PK is some CK you picked for that, and a CK is a superkey containing no smaller CK. SQL allows a PK or UNIQUE NOT NULL to be declared that contains a smaller one. – District