How can I catch UniqueKey Violation exceptions with EF6 and SQL Server?

c#, entity-framework-6, exception, sql-server, unique-key

Solution

With EF6 and the `DbContext` API (for SQL Server), I'm currently using this piece of code:

try
{
  // Some DB access
}
catch (Exception ex)
{
  HandleException(ex);
}

public virtual void HandleException(Exception exception)
{
  if (exception is DbUpdateConcurrencyException concurrencyEx)
  {
    // A custom exception of yours for concurrency issues
    throw new ConcurrencyException();
  }
  else if (exception is DbUpdateException dbUpdateEx)
  {
    if (dbUpdateEx.InnerException != null
            && dbUpdateEx.InnerException.InnerException != null)
    {
      if (dbUpdateEx.InnerException.InnerException is SqlException sqlException)
      {
        switch (sqlException.Number)
        {
          case 2627:  // Unique constraint error
          case 547:   // Constraint check violation
          case 2601:  // Duplicated key row error
                      // Constraint violation exception
            // A custom exception of yours for concurrency issues
            throw new ConcurrencyException();
          default:
            // A custom exception of yours for other DB issues
            throw new DatabaseAccessException(
              dbUpdateEx.Message, dbUpdateEx.InnerException);
        }
      }

      throw new DatabaseAccessException(dbUpdateEx.Message, dbUpdateEx.InnerException);
    }
  }

  // If we're here then no exception has been thrown
  // So add another piece of code below for other exceptions not yet handled...
}

As you mentioned `UpdateException`, I'm assuming you're using the `ObjectContext` API, but it should be similar.

Problem

One of my tables have a unique key and when I try to insert a duplicate record it throws an exception as expected. But I need to distinguish unique key exceptions from others, so that I can customize the error message for unique key constraint violations. All the solutions I've found online suggests to cast `ex.InnerException` to `System.Data.SqlClient.SqlException` and check the if `Number` property is equal to 2601 or 2627 as follows: ``` try { _context.SaveChanges(); } catch (Exception ex) { var sqlException = ex.InnerException as System.Data.SqlClient.SqlException; if (sqlException.Number == 2601 || sqlException.Number == 2627) { ErrorMessage = "Cannot insert duplicate values."; } else { ErrorMessage = "Error while saving data."; } } ``` But the problem is, casting `ex.InnerException` to `System.Data.SqlClient.SqlException` causes invalid cast error since `ex.InnerException` is actually type of `System.Data.Entity.Core.UpdateException`, not `System.Data.SqlClient.SqlException`. What is the problem with the code above? How can I catch Unique Key Constraint violations?

Original source