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:
Entity Framework is a terrible Relational Algebra mapper1 or;
(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:Melds2.
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
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?
1 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.
2 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.
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
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" ..
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*
.. 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 ..