EF CodeFirst Validation of Unique composite key

Question

I have a table that contains an Id column as a primary key. There is also a requirement to enforce uniqueness on two other columns, which are actually FKs. In Sql Server I can create a UniqueKey on this two columns.

What can I do on the EF side so that it can validate uniqueness over these two fields?

score 2 · Accepted Answer · edited May 23 '17 at 12:20

2

EF doesn't support unique keys so your best workaround is to use unique key in the database and catch exception during saving. If you need EF to create the unique key during database creation you can use custom initializer.

If you want to have some validation in the application you will need to execute some query and check that these data don't exist yet but you will still need to use the former advice with catching exception because another thread / process / user can create such data between your query and saving changes.

edited May 23 '17 at 12:20

Community

1
1

answered Jun 29 '12 at 14:17

Ladislav Mrnka

360,892
59
660
670

Hi, I have already looked at your post you linked to, before I posted this question. WHat do you mean by executing some query? I see two possible problems here: 1) I need to compare new value to existing values in databasem, and 2) I am having duplicate values in memory data, so insert/update of the second record will fail. Is your opinion that DataAnnotations are not applicable in this scenario? – Goran Jun 29 '12 at 14:39
Data annotations are not applicable because you can have same value in memory in different context instance. In such case validation by data annotation will pass but the real validation will happen only in the database. – Ladislav Mrnka Jun 29 '12 at 17:25

score -1 · Answer 2 · answered Jun 29 '12 at 14:17

-1

In your POCO class mark each column that needs to be unique as Key. As in:

public MyPocoClass {
   [Key, Column(Order = 0)]
   public int Id { get; set; }

   [Key, Column(Order = 1)]
   public int Col2name { get; set; }


   [Key, Column(Order = 2)]
   public int Col3name { get; set; }
}

Setting the Column order is useful so that your primary key columns are all displayed together in the sql db.

If the columns are Foreign keys to other Entity Framework code first classes then simply name the property as tablename_tableId and EF will do the rest. I.e

public anotherClass {
       public int Id { get; set; }

       public int MyPocoClass_Id { get; set; } //this is a foreign key

    }

answered Jun 29 '12 at 14:17

marvc1

3,641
3
25
34

What do you mean by: EF will do the rest? I think that you have misunderstood my question. How can I check on the client side if there a two records with same Col2Name and Col3Name values? – Goran Jun 29 '12 at 14:31
Entity Framework will cover the validation for the foreign keys, the Context will throw an exception if you try to insert a row that is not unique or if the foreign key does not exist as a key in the other table. Does that help? – marvc1 Jun 29 '12 at 14:36
Nope. If I come to the point that a QUERY is executed to the database, that means that EF is not handling the validation at all. My question was: what can I do on the EF/client side to validate data. – Goran Jun 29 '12 at 14:42
I'm not sure what you are trying to achieve. You will have to make contact with the database which ever way you choose. To check if the primary/foreign key is valid you need to know what the database table contains. If you want to load the table as a collection, then iterate through it to check the validation of the keys, you can, but you still need to access the data. – marvc1 Jun 29 '12 at 14:46
I am not trying to validate if FK is valid, I am trying to validate uniqueness of value combined with values of two properties. In my case, these two properties are FKs, but that doesn't have to be the case always. If you come to the point that exception is raised, validation never hapened. This link http://blogs.microsoft.co.il/blogs/shimmy/archive/2012/01/23/validationattribute-that-validates-a-unique-field-against-its-fellow-rows-in-the-database.aspx speaks of property based validation, I am interested in uniqueness validation on entity level. – Goran Jun 29 '12 at 14:54
So you want to check for yourself if the row you are inserting does not have 2 columns the same as any existing rows? When you add an entity to the class you will have to check the uniqueness for yourself. But I suspect you have further underlying problems if you cannot insure the integrity of the data with a relational database. – marvc1 Jun 29 '12 at 15:04
No, I don't have further underlying problems, and the integrity of the databse is secured through the UniqueKey (read my question). Imagine that there is a string column in database of length 5. If I dont have any validation on model side (EF), then an exception will be raised if I try to insert string of length>5. However, in order to avoid exception, we place Validation attribute, so we do not need to execute a query to database and wait for the exception to occur. Similar thing I want to achieve with unique key, if possible. – Goran Jun 29 '12 at 17:15
ok, but that is not possible, as the only way to validate the data to be inserted is to know what is in the database. – marvc1 Jun 29 '12 at 20:44
I never said that there should be no query sent to the database, during validation, I said I don't like the idea of catching exceptions. Catching exceptions creates more problems, for example, if I have batch update, how do I know which record is breaking the unique constraint? Also, how do I know which constraint rule is broken on particular record, if I have several? I cannot just show the exception to user, since message must be localized. – Goran Jun 29 '12 at 23:00
Commit each record individually, and log/wrap the exception. – marvc1 Jun 30 '12 at 06:21

EF CodeFirst Validation of Unique composite key

2 Answers2