如何让 EF6 在关联/关系多重性中遵守唯一约束(在 FK 上)? [英] How to get EF6 to honor Unique Constraint (on FK) in Association/Relationship multiplicity?

问题描述

2019 年更新/TLDR；切换到实体框架Core(或其他)

2019 Update / TLDR; switch to Entity Framework Core (or whatever else)

虽然缺少一些功能"，但 EF Core 除了主键之外还正确地支持备用键(也称为唯一约束)，因此在支持关系代数方面做得更好.否则为 YMMV；至少它正确支持更多的 SQL 方案.

While missing some "Features", EF Core properly honors Alternate Keys (aka Unique Constraints) in addition to Primary Keys and thus does a much better job of honoring Relational Algebra. YMMV otherwise; at least it supports many more SQL schemes correctly.

添加的这种支持在(非常过时的)EF Core 1.0 版本.. 有点令人失望的是，原来的 EF 从未解决过这个设计(编辑！)缺陷.

This support added was in the (very outdated) EF Core 1.0 release.. a bit disappointing that the original EF never had this design(ed!) flaw addressed.

这可能与我的 其他问题 - 这似乎是:

This may be related to my other question - which seems to be that either:

1. 实体框架是一个糟糕的关系代数映射器¹ 或者;

1. Entity Framework is a terrible Relational Algebra mapper¹ or;

(我希望如此)我忽略了 SSDL/CSDL 和 EDMX 模型或一般的 EF 映射.

(which I am hoping for) I am overlooking something with SSDL/CSDL and the EDMX model or EF mappings in general.

我有一个 Schema First 模型，其架构如下所示:

I have a Schema First model and the schema looks like this:

ExternalMaps
---
emap_id - PK

Melds
---
meld_id - PK
emap_id - >>UNIQUE INDEX<< over not-null column, FK to ExternalMaps.emap_id

为了验证，这些脚本编写如下，应该导致 ExternalMaps:1 <-> 的多样性.0..1:融合².

For verification, these are scripted as the following, which should result in a multiplicity of ExternalMaps:1 <-> 0..1:Melds².

ALTER TABLE [dbo].[Melds] WITH CHECK ADD CONSTRAINT [FK_Melds_ExternalMaps]
FOREIGN KEY([emap_id]) REFERENCES [dbo].[ExternalMaps] ([emap_id])

CREATE UNIQUE NONCLUSTERED INDEX [IX_Melds] ON [dbo].[Melds] ([emap_id] ASC)

但是，当我使用 EDMX 设计器从数据库 (SQL Server 2012) 进行更新时，它错误地 将关联/外键关系创建为 ExternalMap:1 <->M:融合.

However, when I use the EDMX designer to update from the database (SQL Server 2012), from scratch, it incorrectly creates the Association / Foreign Key relation as ExternalMap:1 <-> M:Meld.

当我尝试手动将 Meld(通过设计器中的关联集"属性)一侧的多重性更改为 1 或 0..1 时，我明白了:

When I try to change the multiplicity manually for the Meld (via the "Association Set" properties in the designer) side to either 1 or 0..1, I get:

运行转换:多重性在关系FK_Melds_ExternalMaps"中的角色Meld"中无效.因为依赖角色属性不是关键属性，所以依赖角色的多重性的上限必须是*.

Running transformation: Multiplicity is not valid in Role 'Meld' in relationship 'FK_Melds_ExternalMaps'. Because the Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be *.

(与我的其他问题一样，这似乎与唯一约束不被正确注册/授予候选键有关.)

(As with my other question, this seems to be related to Unique Constraints not being correctly registered/honored as Candidate Keys.)

我怎样才能让 EF 兑现 1 <->0..1/1 多重性，由模型确定?

How can I get EF to honor the 1 <-> 0..1/1 multiplicity, as established by the model?

¹ 虽然我希望不是这种情况，但当我试图让 EF 映射到一个完全有效的 RA 模型时，我感到无所适从:LINQ to SQL (L2S) 确实如此没有有这个问题.由于对于如此流行的 ORM，我的另一个问题没有得到简单的回答，因此我对这种工具失去了信心.

¹ While I hope this is not the case, I am having no end to grief when trying to get EF to map onto a perfectly valid RA model: LINQ to SQL (L2S) does not have this problem. Since my other question was not trivially answered for such a popular ORM, I am losing faith in this tooling.

² FK 并非相反:尽管不应有可为空的外键"，这是设计使然.- 不是它是一个共享"PK的情况，因为这个2009年的回答建议作为修复.

² It is by design that the FK is not the other way: "Though shalt not have nullable foreign keys." - It is also not the case that it's a "shared" PK as this answer from 2009 suggests as a fix.

我正在使用 EF 6.1.1、VS 2013 Ultimate，并且我不会使用任何OO 子类型功能" - 如果这有任何改变的话.

I am using EF 6.1.1, VS 2013 Ultimate, and am not going to use any "OO subtype features" - if that changes anything.

编辑叹息:

多重性无效，因为从属角色属性不是关键属性?(从 2011 年开始)- 是这仍然在 2014 2015 年的 EFMicrosoft 认可的企业就绪"ORM 的情况下?

Multiplicity is not valid because the Dependent Role properties are not the key properties? (from 2011) - is this still the case for the EF "Microsoft-endorsed Enterprise-ready" ORM in 2014 2015?

按照这个速度，下次有人问为什么不使用 EF 时，除了LINQ to SQL 工作正常"之外，我还有一大堆理由......

At this rate the next time someone asks why EF wasn't used I'll have a large set of reasons other than "LINQ to SQL works just fine" ..

推荐答案

问题是实体框架(从 EF4 到 EF6.1，不知道还要多久)不理解"唯一约束的概念和所有它们暗示:EF 映射代码优先，而不是关系代数*sigh*

The problem is that Entity Framework (from EF4 through EF6.1, and who knows how much longer) does not "understand" the notion of Unique Constraints and all that they imply: EF maps Code First, not Relational Algebra *sigh*

这个答案 我的相关问题提供了一个链接到 请求添加缺少的功能 并总结:

This answer for my related question provides a link to a request to add the missing functionality and sums it up:

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

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

这可以扩展到几乎所有处理唯一约束和候选键的领域，包括这个问题中提出的多重性问题.

This can be expanded to pretty much all realms dealing with Unique Constraints and Candidate Keys, including the multiplicity issue brought up in this question.

如果 EF 的这种严重限制被公开讨论并众所周知"，我会很高兴，尤其是当 EF 被吹捧为支持 Schema First 和/或取代 L2S 时.从我的角度来看，EF 的中心是仅将 Code First 映射(和支持)作为一等公民.也许再过 4 年..

I would be happy if this severe limitation of EF was discussed openly and made "well known", especially when EF is touted to support Schema First and/or replace L2S. From my viewpoint, EF is centered around mapping (and supporting) only Code First as a first-class citizen. Maybe in another 4 years ..

这篇关于如何让 EF6 在关联/关系多重性中遵守唯一约束(在 FK 上)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！