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

Singspiel

When you remove the uniqueness constraint...does it generate the right database / model for you? – Outworn

@YoupTube It appears to generate the same model - only this time the model is actually correct wrt. the schema due to missing the UX in the database (but then it breaks the RI data rules). I made sure I was editing the correct database by changing a column name, which did come through correctly. – Singspiel

And, although it's a weak approach, adding the UX manually afterwards??? – Outworn

EF does not, and has never supported this. EF only supports 1:1 or 1:0..1 on shared primary keys. There has been talk of fixing this issue, and it may actually get fixed in EF7, but as of right now it is unsupported (there are a number of questions here on SO about this). L2S also does not support this, although it may work as it appears to have worked for you. It's not guaranteed to work, so you may find your code broken for no apparent reason with L2S someday. If you're doing new EF development, you shouldn't be using an EDMX either, since the EDMX file will be going away in EF7. – Gonyea

@ErikFunkenbusch Thanks for that information. I wish the ES6 documentation / praise guides would be more clear on this .. What will replace EDMX for Schema-first in EF7? Anyway, L2S does appear support this scenario - at least with the TT drop-in on codeplex. (Rather, I've never had it "not work".) – Singspiel

As I said, it may "work", but it's not officially supported. L2S is a simpler system than EF, so you can often get away with things that EF wouldn't allow because it has more validation. EF7 will still support model and database first, but it will generate code models rather than EDMX files. – Gonyea

@ErikFunkenbusch This design flat out works in L2S (I know having spent several years developing a "not small" application using such), perhaps because L2S is simpler .. but the complexity added (for something that I do not care about) is irrelevant to the system not even being designed to cover real-world RA use cases (that I need handled). Basically put: with such hushed-over limitations, how is EF even used with existing DBs of any reasonable schema complexity or variety? – Singspiel

@ErikFunkenbusch Because if the answer is "it isn't" or "shouldn't be" or "code first or bust", then is there a good reason for such so that I can re-align this terrible EF-oriented (as in "we only use 'approved' MS practices") direction that the MS-sway has laid? – Singspiel

The point here is that you're praising L2S for something that you are getting lucky with. It's not an officially supported scenario, and if it works, great, but there's no guarantee it will keep working. The next version of the framework could break it at any time, because the scenario has no official support you're basically playing Russian roulette. What would you do if a serious security vulnerability were found and the "fix" broke your code? You would have to rewrite your entire data model somehow on a moments notice, or else run with a vulnerable app. – Gonyea

The difference between "supported" and "works" is that you are on your own if it breaks. Particularly since L2S is essentially on life support. ORM's in general do not support every possible data modeling scenario. Most have limitations in one way or another. For instance, EF requires that all rows have primary keys. While this is a good design practice, not all data models have them for all tables. So EF can't be used for those situations. nHibernate, another mature ORM has it's own list of limitations (although it does support the 1:1 scenario) – Gonyea

There are lots of ORM's out there, all of them have limitations, problems, issues, etc.. you have to choose the one that best fits your needs. Frankly, I've never needed to pathologically model a 1:1 non-shared primary key, I've been happy to just treat them like 1:* with only one record, and defining a unique constraint to ensure no duplicates. You could achieve the same by using a unique Index on your FK, but just dealing with the collection semantics. – Gonyea

@ErikFunkenbusch Uh - I'm not praising. I'm saying a simple statement backed by actual experience. If there is a references saying this doesn't work or is ill-defined behavior, please share an authorative resource. From experience, this works in L2S (using l2s tt) from a Model generated from a Database. (And thanks to EF, L2S has not an improvement / change in over half a decade.) – Singspiel

@ErikFunkenbusch In any case, if the answer is "EF does not, cannot" support this - then that should be an answer. If the answer is "EF does not, but can hack this [by..]" - then that should be an answer. Authoritative/external references should back such, and such should provide details in context of EF6. – Singspiel

Great, so keep using L2S. Why are you evening bothering if you find it so suitable to your needs? Obviously, it must not be so perfect if you are looking for alternatives. – Gonyea

@ErikFunkenbusch Because I've changed employers and "EF is the way forward", even if it is not actually used except as a thin SQL wrapper. While I'm upset at the mind-share campaign that makes using L2S (or rather, not using EF) "frowned upon" in a "modern Microsoft shop", the question and problem (needing a solution or work-about or enough clout to ditch EF) still remains, this trite divergence aside. – Singspiel

It's not like L2S doesn't have its own problems. For instance, you complain about lack of 1:1 in EF, but L2S has no *:* support, which I find a much bigger problem. EF is the way forward, and EF7 is going to be a pretty big change.. so if I were you i'd just deal with EF as it is today and wait for EF7.. it won't be perfect, but it will improve at a much faster rate than EF has previously due to the basic complete rewrite. – Gonyea

@ErikFunkenbusch Let's drop this L2S stuff. I did not praise L2S. I said it did not have this issue - basically, because it does honor UX FK constraints. Anyway, this is not what the question is about. (Also, L2S has no implicit M:M support, but supports such just fine explicitly - but again, not related to the question and the real world problem I am trying to solve within a set of artificial constraints.) – Singspiel

@ErikFunkenbusch The problem is I don't know how to "deal" with EF as it is today - if I did I would not have asked a question. Perhaps the question(s) with no answers indicate that I'm not the only one .. – Singspiel

@ErikFunkenbusch To really tout EF over L2S (but again I don't care and it is not relevant to my existing Schema First design) one would bring up all the "OO" features it brings or how "rapid" it is to [re-]create trivial schemas or how everything can be done from unified code (including migrations and validations), if such is your cup of tea. L2S is a RA mapper. EF apparently maps .. EF "Code First". (And the question is how to get EF to correctly - even trivially - map my Schema.) – Singspiel

As I said a number of comments ago. Make it a 1:*, then add a unique index to the FK to prevent duplicates. Yes, you still have collection semantics for the many side, but your data will stay valid. Or just give in and make it a shared primary key. – Gonyea

By the way, the reason EF did not support this was not because it was a poor mapper, but because it did not have any mechanism to support unique constraints prior to EF6.1. Since there is no native SQL concept of a 1:1, it can only be simulated by a 1:M with a unique constraint, and EF didn't support that. EF 6.1 added unique indexes, but unfortunately, the internal logic is essentially unchanged and is validating that it can't do this because it hasn't been updated. Why didn't it support constraints? a feature that never made it to the top of the priority list. – Gonyea

@ErikFunkenbusch My real world involves starting with a schema and "give in" is not an option - I don't work with "to-do list" apps. I disagree on that point: UX FK (or CK) in RA is a native SQL concept that is 1:1 .. what is a PK, but a CK, but a UX set of columns? It's not RA limit that EF only considers a PK as a UX constraint. Anyway, if this is the "EF6 Way" - and the inclusion of unique indexes in EF 6.1 gave me a glimmer of hope it was not - then such would make a base for an answer. (I'll hold my tongue as to why EF doesn't support basic RA concepts given valid CK/FK constraints.) – Singspiel

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

Singspiel