如何将 EF6 关联添加到不是主键的候选键/唯一键? [英] How to add an EF6 Association to a Candidate Key / Unique Key which is not the Primary Key?

问题描述

使用Schema First，我有一个数据库结构，所以

Using Schema First, I have a database structure, as so

ExternalDataItems
---
edataitem_id   PK -- surrogate auto-increment - NOT for FK relation here
datahash       UX -- Candidate Key / Unique Index (binary(20))

ExternalMaps
---
emap_id        PK
ext_datahash   FK on ExternalDataItems.datahash - NOT referencing the surrogate PK

在生成 SSDL/CSDL¹ 后有这个

and after generating the SSDL/CSDL¹ has this

    <Association Name="FK_ExtMaps_ExtDataItems">
      <End Multiplicity="1" Role="ExternalDataItems" Type="Store.ExternalDataItems" />
      <End Multiplicity="*" Role="ExternalMaps" Type="Store.ExternalMaps" />
      <ReferentialConstraint> <!-- error on this element -->
        <Principal Role="ExternalDataItems">
          <PropertyRef Name="datahash" />
        </Principal>
        <Dependent Role="ExternalMaps">
          <PropertyRef Name="ext_datahash" />
        </Dependent>
      </ReferentialConstraint>
    </Association>

在 <ReferentialConstraint> 元素上生成错误

运行转换:主体角色 ExternalDataItems 引用的属性必须与关系 FK_ExtMaps_ExtDataItems 的关系约束中主体角色所引用的 EntityType ExternalDataItem 的键完全相同.确保在 Principal Role 中指定了所有关键属性.

Running transformation: Properties referred by the Principal Role ExternalDataItems must be exactly identical to the key of the EntityType ExternalDataItem referred to by the Principal Role in the relationship constraint for Relationship FK_ExtMaps_ExtDataItems. Make sure all the key properties are specified in the Principal Role.

ExternalDataItems SSDL 的主要角色"(?) 对于 PK 如下所示，UX 似乎不存在²，除了作为一个简单的标量属性:

The "Principal Role" (?) for ExternalDataItems SSDL looks like the following, for the PK, and the UX does not appear to be present², except as a simple scalar property:

  <EntityType Name="ExternalDataItems">
      <Key>
        <PropertyRef Name="edataitem_id" />
      </Key>
      ..
      <Property Name="datahash" Type="binary" MaxLength="20" Nullable="false" />
  </EntityType>

如何添加这个关系 - 使用 FK 到非 PK 候选键?(在这个工作"之后，我还希望在 CSDL 中也有一个导航属性.)

How can I add this Relation - using a FK to a non-PK Candidate Key? (After this "works" I'll also want to also have a Navigation Property to the CSDL.)

此外，关联线不会出现在设计表面中 - 我怀疑这只是此错误的后果.我正在使用实体框架版本 6.1.1(最新发布于 nuget)和 Visual Studio 2013 Ultimate Update 4.

Furthermore, the association line does not appear in the design surface - which I suspect is just fallout from this error. I am using Entity Framework version 6.1.1 (latest published on nuget) and Visual Studio 2013 Ultimate Update 4.

¹标准的 EDMX从数据库更新"似乎没有拾取这些 FK 关系(可能与这个bug有关)，上面的结果是使用Huagati DBML/EDMX工具后的结果.如果我之前尝试添加关联"，设计者会错误地尝试使用主键 - 任何 FK 关系都不支持 - 并且没有提供选择替代属性的选项.

¹The standard EDMX "Update from Database" didn't appear to pick up these FK relations (which may be related to this bug) and the results above are after using the Huagati DBML/EDMX tooling. If I tried to "Add Association" prior the designer would incorrectly try and use the primary key - which is not supported by any FK relation - and did not provide options for choosing alternative properties.

²尝试添加 <UniqueConstraint> 元素，如 "实体框架中的唯一约束" 导致友好的 XML 验证错误:

²Attempting to add a <UniqueConstraint> element as described in "Unique Constraints in the Entity Framework" results in the friendly XML validation error:

元素 'ElementType' .. 有一个无效的子元素 'UniqueConstraint'.

The element 'ElementType' .. has an invalid child element 'UniqueConstraint'.

推荐答案

看起来像 "缺少重要功能"

.. Entity Framework 目前仅支持基于主键的引用约束，没有唯一约束的概念.

.. The Entity Framework currently only supports basing referential constraints on primary keys and does not have a notion of a unique constraint.

您可以在 DB 方案中删除外键并使用 LINQ 连接表:

You can remove foreign key in DB scheme and use LINQ to join tables:

from item in ExternalDataItems
join map in ExternalMaps on item.datahash = map.ext_datahash
select new { item.edataitem_id, map.emap_id };

您还可以使用这些连接表创建 VIEW 并使用其中一个.

Also you can create the VIEW with these joined tables and use the one.

这篇关于如何将 EF6 关联添加到不是主键的候选键/唯一键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！