Entity Framework: How to properly handle exceptions that occur due to SQL constraints

Question

I use Entity Framework to access my SQL data. I have some constraints in the database schema and I wonder how to handle exceptions that are caused by these constraints.

As example, I get the following exception in a case where two users try to add an (almost) identical entity to the DB concurrently.

System.Data.UpdateException
"An error occurred while updating the entries. See the InnerException for details."

(inner exception) System.Data.SqlClient.SqlException
"Violation of UNIQUE KEY constraint 'Unique_GiftId'. Cannot insert duplicate key in object 'dbo.Donations'.\r\nThe statement has been terminated."

How do I properly catch this specific exception?

Dirty solution:

    catch (UpdateException ex)
    {
        SqlException innerException = ex.InnerException as SqlException;
        if (innerException != null && innerException.Message.StartsWith("Violation of UNIQUE KEY constraint 'Unique_GiftId'"))
        {
            // handle exception here..
        }
        else
        {
            throw;
        }
    }

Now while this approach works, it has some downsides:

No type safety: The code depends on the exception message which contains the name of the unique column.
Dependency on the SqlCLient classes (broken abstraction)

Do you know a better solution for this? Thanks for all feedback..

Note: I do not want to code the constraints manually within the application layer, I want to have them in the DB.

gbn · Accepted Answer · 2011-08-27T12:18:59.460

17

You should be able to trap the SQL error number (which is SqlException.Number)

In this case it's 2627 which has been the same forever for SQL Server.

If you want abstraction, then you'll always have some dependency on the database engine because each one will throw different exception numbers and messages.

edited Aug 27 '11 at 12:18

answered Sep 12 '10 at 10:02

gbn

422,506
82
585
676

Okay, but even if I use this ID, i have to parse the exception message for the column name. – driAn Sep 12 '10 at 11:08
@driAn: correct. SQL also has no native mechanism to drill to which constraint or column, which means there is no API or such to read it either. – gbn Sep 12 '10 at 11:23

score 2 · Answer 2 · answered Sep 12 '10 at 10:00

One way is to inspect the Errors property of the inner SqlException. The SqlError class has a Number property that identifies the exact error. See the master.dbo.sysmessages table for a list of all error codes.

Of course this still ties you to Sql Server. I'm not aware of a way to abstract this away other than roll your own 'EF exception analyzer'.

score 0 · Answer 3 · answered May 10 '12 at 19:26

This scenario should not happen as the key should never be assign explicitly when using EF; rather allowing the context to assign an appropriate one. If its a concurrency issue then you should do the update in a transaction scope.

Then if you have an UpdateException you could retry the update again. You can safely do that in a transaction scope and only complete the scope when the update goes thorough. In this scenario the chances of the update going through the next time is greater than the first one.

Entity Framework: How to properly handle exceptions that occur due to SQL constraints

3 Answers3

Linked