创建复合键实体框架 [英] Creating Composite Key Entity Framework

问题描述

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

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

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

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：
  ：性能在一个从属和主要角色的数量
  关系约束必须是相同的。

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

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

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.

推荐答案

如果的设备的表有复合主键，那么你需要在你的 NotificationMessageDevice 的表相同的外键。如何将SQL发现设备不完整的主键？你也应该让这些字段是 NotificationMessageDevice 的表的主键的一部分。否则，你不能保证主键将是独一无二的：

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

这篇关于创建复合键实体框架的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！