5

Since EF doesn't support unique key contraints, it seems that we need to catch exception during the Save method, and display error message to user.

The problems with this approach are:

  • how do we know which record threw an exception
  • how do we know what kind of problem threw an exception (ex I could have two unique constraints on same record, so I need to tell the user which one is broken)

DBMS is SqlServer 2008.

How to resolve these problems?

Improve this question

1 Answer 1

Reset to default
2

If you allow that a user can enter values that must be unique in the database you should validate this input before you save the changes:

if (context.Customers.Any(c => c.SomeUniqueProperty == userInput))
    // return to user with a message to change the input value
else
    context.SaveChanges();

This isn't only the case for values with unique constraints in the database but also for entering foreign key values that must refer to existing target records or primary key values if the primary key isn't autogenerated in the database. EF doesn't help you in the latter situation either because a context doesn't know the content of the whole database table but only about the entities that are currently attached to the context. It is true that EF will forbid to attach two objects with the same primary key but allows two objects with the same unique key constraint. But this doesn't guard you completely against primary key constraint violations when you save changes to the database.

In the unlikely case that between the Any check and SaveChanges another user has entered a record with the same value, I would consider the occuring exception as not possible to handle and just tell the user "An expected error occurred, please try again...". If the user tries again the Any check happens again and he will get the more useful message to change the input value from the code above.

The exception returned for such unique key constraint or primary key constraint violations is a general DbUpdateException and one of the inner exceptions will be a SqlException that contains as one of its properties a SQL Server error code. Any other details can be found only in the exception message "Violation of UNIQUE KEY constraint IX_SomeUniqueProperty_Index..." or similar. If you expect that the user can understand and react accordingly to this information you could display it. Otherwise you could log this message for an administrator or developer to check for possible bugs or other problems.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

So, since I am doing batch updates, this would require to find updated/added records in Customers, and loop through each record to check if there is an existing record with same value. I am not quite sure how will it impact on performance when there are 10+ records that were changed. Or in this case it is better to just catch an exception? The message must be localized, so it seems that I need to create a procedure that will do a message search for IX_SomeUniqueProperty, since the message can be on french also, if OS is french (if I am not mistaken), and I cannot depend on "Violation of ...."
@Goran: I see, but I would still do a query to check for existence. If you also need to update as you say, not only insert, don't you need to query for the entity in the database anyway? You cannot decide between updating and inserting without knowing if the entity is already in the DB or not.
Decision between updating and inserting is made by EF itself. Any entity that I have added to Customers collection is considered as new record, records with already assigned PK will be updated. What I need to cover is the case where a user on the network added a record (client's data are in most cases not up to date with database data, since that would require constant resynchronizing), so there could be 15 minutes elapsed between the last data load, and save changes call. For this reason I cannot only depend on the data that is cached on client to check if unique (or any other) constraints.
I am not quite sure that I understand what are you having doubts on? Basically you load list of customers from database (DbContext Customers set). On Add new item method, you create a new Customer, assign to it default values, and add it to Context's Customers Set. EF will mark it as new item, and will perform INSERT INTO statement when SubmitChanges is called.
@Goran: Yes, I agree with this. But that's only an INSERT, not an UPDATE. If the new customer has a value in the unique key property that another record in the DB already has you'll get an exception ("cannot insert duplicate key..."). If you want to UPDATE that existing record, you must query for it, and then either load it, change the properties and save changes, or set the state to Modified and save changes. For me that would be the straightforward way. You seem to have the idea to try to insert and if you get an exception you know you must repeat with an update.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.