entity framework - How to get EF6 to honor Unique Constraint (on FK) in Association/Relationship multiplicity?

Question

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

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.

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. Entity Framework is a terrible Relational Algebra mapper¹ or;

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

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

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)

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.

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:

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.)

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

---

¹ 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.

² 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.

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

---

EDIT sigh:

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?

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" ..

Answer 1

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:

.. 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.

---

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 ..