创建复合键实体框架(Creating Composite Key Entity Framework)

不久，我想在为了提高SQL Server的搜索性能与主键剩下的我的表中创建组合键。 发生在200K数据表中的性能问题，每当我搜索没有主键的实体（即GUID的字符串）。 假设我有3个班

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

有什么问题是，每当我想要的ID，UDID，ApplicationKey和PlatformKey定义为复合键与模型构建器它提供了以下错误。

NotificationMessageDevice_Device_Target_NotificationMessageDevice_Device_Source：在从属和主角色的属性有关系约束的数目必须是相同的

我认为这个问题是因为NotificationMessageDevice导航性能是无法识别主键是在器表的内容。 我怎样才能解决这个问题呢？ 除此之外，如果你分享您的经验提高了对实体框架的搜索性能，我会很高兴。 通常发生在每当我用第一种方法没有主键的性能问题。

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

文章来源: Creating Composite Key Entity Framework