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

entity-framework entity-framework-6 foreign-keys multiplicity unique-key

Question

Switch to Entity Framework Core for the 2019 Update (or whatever else)

While lacking some "Features," EF Core correctly honors Alternate Keys (also known as Unique Constraints) in addition to Primary Keys, which honors Relational Algebra much better. If not, YMMV; at least it correctly supports a lot more SQL techniques.

It is somewhat surprising that the original EF never had this design(ed!) problem corrected. This support was added to the (very obsolete) Release of EF Core 1.0.


Perhaps this has something to do with my another query, which appears to be that either:

  1. The Relational Algebra mapper Entity Framework is either terrible or;

  2. I am missing something with SSDL/CSDL, the EDMX paradigm, or EF mappings in general (which is what I am aiming for).

I have a Schema Initial model, and this is how the schema is laid out:

ExternalMaps
---
emap_id - PK

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

These are programmed as follows for verification, and the end result is a variety ofExternalMaps:1 <-> 0..1:Melds 2.

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)

The Association / Foreign Key relation is created, however, when I use the EDMX designer to update from scratch from the database (SQL Server 2012).ExternalMap:1 <-> M:Meld .

If I use the "Association Set" attributes in the designer to manually adjust the multiplicity for the Meld side to either1 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 *.

This appears to be connected to Unique Constraints not being correctly registered/honored as Candidate Keys (as with my other question).

According to the model, what is How can I get EF to respect the1 <-> 0..1/1 multiplicity?


1 Although I sincerely hope not, I am experiencing unimaginable difficulty when attempting to map an EF model into a perfectly good RA model: LINQ to SQL (L2S) does not have this problem. I am beginning to lose faith in this tooling because the answer to my other issue was not trivially given for such a well-known ORM.

2 The FK is not the other way by design because it says, "Though thou shalt not have nullable foreign keys." - Additionally, as this response from 2009 repair suggests, it is a "shared" PK, as stated in not.

I'm using EF 6.1.1, Visual Studio 2013 Ultimate, and I'm going to employ any "OO subtype features" to see if that makes a difference.


Zzz-167-Zzz EDIT:

Is the EF "Microsoft-endorsed Enterprise-ready" ORM in 2015 still Because the dependent role qualities are not the primary properties, multiplicity is invalid. the same as it was in 2011?

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

1
15
3/29/2019 5:09:06 PM

Accepted Answer

The issue is that Unique Constraints and everything they include are not "understood" by Entity Framework (from EF4 to EF6.1, and for how much longer? ): Instead of mapping Relational Algebra, EF does such. *sigh*

Zzz-10-Zzz links to Zzz-15-ZZZ and summarizes it for my related question:

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

This includes the multiplicity problem raised in this topic as well as pretty much all areas dealing with Unique Constraints and Candidate Keys.


Since EF is said to support Schema First and/or replace L2S, I would be happy if this serious constraint of EF was discussed openly and made "well known." I see Code First as the sole component that EF is focused on mapping (and supporting) as a first-class citizen. Possibly in 4 more years.

19
5/23/2017 11:46:54 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow