Unique Constraint (i.e. Candidate Key) Support
SQL Server and other databases support Unique Constraints on tables. Foreign key constraints are generally based on unique constrains on the principal side, with the Primary Key being only a special case of a unique constraint.
The Entity Framework currently only supports basing referential constraints on primary keys and does not have a notion of a unique constraint. The idea is to have:
• Support for specifying a unique constraint on an Entity
• Support for specifying a foreign key associations that on the principal end specify columns(s) that comprise a unique constraint but are not the primary key,
Several DataBases give you the possibility to create UNIQUE INDEXES, both to support Unique Constraint on it or just to ensure the uniqueness of some kind of data.
In both cases, this kind of information doesn't have a proper place into the model and this makes the DbContext unable to determine the proper order of the Insert/Update/Delete commands within the commands tree.
The reason is pretty clear to me, if within the same SaveChanges() you delete a row or you update a index column(s) value of a row and you add a brand new row with the same original index column(s) value of the updated/delete row, the order of this two commands is VERY IMPORTANT 'cause the wrong order will end with an exception from the DB!
In my previous experiences I wrote a tons of code executed during the SaveChanges() to inspect the ObjectStateManager, to detect all the changes impacting the columns covered by a Unique Index, to remove these changes from the Context, to prepare a list of ad-hoc DbCommand in order to replicate the operation just removed and, finally, after the execution of the parent SaveChanges() but within the same DbTransaction, to execute the DbCommands.
A lot of very risky code, it took several days just only for the Unit Testing!
The Feature Request is:
Give us the opportunity to put into the Model this vital information about the uniqueness of the columns (in form of metadata) and use them while the CommandTree builder decides the statement execution order.
This will be supported in EF7.
Please also add support for nullable unique key constraints.
Currently, when attempting to add a second row with a null field, the following error is raised:
Cannot insert duplicate key row in object 'dbo.People' with unique index 'IX_Passport'. The duplicate key value is (<NULL>).
The statement has been terminated.
Roger Hendriks commented
Mmmm these additions are Migrations only, how about generic error handling when violating a UC? Is that in too?
Now lets ask MVC guys here (https://github.com/aspnet/mvc/), if they are going to add [Unique] decorator for their vNext.
Mike Bantegui commented
If you add this feature, can you also allow Entity Framework to be aware of and create unique constraints when you programmatically create a database instance via Database.Create?
We have a use case where we have our database versioned and deployed through a secondary mechanism (DbUp) and create the entity model from the deployed database.
However, some of our projects have functional tests that need to exercise our data access tier. In one specific case, a test written verifying that a particular method correctly handled unique data was erroneously passing because the database created via Database.Create() does not contain any of the unique constraints present in the original DB model.
Steve Berwick commented
can't even imagine the nightmare this would be to implement. but thanks for looking into it Diego.
OMG! Yes! Yeehaa!!! Good luck with this! This is soooo long avaited feature.
Neville Perkins commented
Yes please!!! We all dream of a world where all Microsoft technologies actually support the same features across the platform. Really bad that SQL has had this for years yet EF which is punted as the main db connection path for app development, does not.
that are great news!
Alexandru Matei commented
It was about time!
Now please add support for unique indexes definition in .edmx Model First..?
Need it ASAP
Linq to SQL can do this, why in the hell cant EF.
Add Unique Constraint feature
Matt Artz commented
Yeah, I have just come across this issue after the first part of our modernisation project which was to add surrogate keys as primary keys to our tables (so EF could rely on an unchanging primary key) and set what were previously the primary keys as unique keys then re-added foreign keys from other tables to the unique key. This meant no data or (breaking) schema changes and our legacy applications could continue to work and use joins as they always have and not require modification...
A large percentage of our entities should have navigational properties through these foreign keys on unique keys but of course we can't do that which means manually retrieving and joining entities until this feature is added.
Please make it soon!
John-Edward Mc Laughlin commented
If SQL Server supports this then so should EF. I am using a code first approach and thinking of just moving to a SQL Server database project for my model creation. This has been a request for some time now. Please add this.
David Bullock commented
A key is a key, whether it's a primary key or not. If I sometimes choose to link based on a natural key instead of a surrogate, what's my Object-Relational binding tool got to object to?
From my point of view this is indeed needed. A table may have candidate keys based on a single field that must be unique. While it is possible to define this in code when the DB is created in a Code First approach (or in the database it self once it has been created), it would be nice if EF can define this in a straightforward way
Stijn Herreman commented
Can we get a technical explanation as to why this is so hard to implement?
Martin Costello commented
I'd like this supported in the EDMX from database first so that the database can be recreated in an SQL LocalDB instance using DbContext.Database.Initialize() with DropAndCreateDatabaseAlways<T> via Database.SetInitializer<T>() without using SQL scripts just from the model metadata (and similarly with default constraints as well).
Please Support is!