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

Question

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?

Solution 1

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.

Solution 2

In my case, I'm using EF 6 and decorated one of the properties in my model with:

[Index(IsUnique = true)]

To catch the violation I do the following, using C# 7, this becomes much easier:

protected async Task<IActionResult> PostItem(Item item)
{
  _DbContext.Items.Add(item);
  try
  {
    await _DbContext.SaveChangesAsync();
  }
  catch (DbUpdateException e)
  when (e.InnerException?.InnerException is SqlException sqlEx && 
    (sqlEx.Number == 2601 || sqlEx.Number == 2627))
  {
    return StatusCode(StatusCodes.Status409Conflict);
  }

  return Ok();
}

Note, that this will only catch unique index constraint violation.

Solution 3

// put this block in your loop
try
{
   // do your insert
}
catch(SqlException ex)
{
   // the exception alone won't tell you why it failed...
   if(ex.Number == 2627) // <-- but this will
   {
      //Violation of primary key. Handle Exception
   }
}

EDIT:

You could also just inspect the message component of the exception. Something like this:

if (ex.Message.Contains("UniqueConstraint")) // do stuff

Solution 4

try
{
   // do your insert
}
catch(Exception ex)
{
   if (ex.GetBaseException().GetType() == typeof(SqlException))
   {
       Int32 ErrorCode = ((SqlException)ex.InnerException).Number;
       switch(ErrorCode)
       {
          case 2627:  // Unique constraint error
              break;
          case 547:   // Constraint check violation
              break;
          case 2601:  // Duplicated key row error
              break;
          default:
              break;
        }
    }
    else
    {
       // handle normal exception
    }
}

Solution 5

I thought it might be useful to show some code not only handling the duplicate row exception but also extracting some useful information that could be used for programmatic purposes. E.g. composing a custom message.

This Exception subclass uses regex to extract the db table name, index name, and key values.

public class DuplicateKeyRowException : Exception
{
    public string TableName { get; }
    public string IndexName { get; }
    public string KeyValues { get; }

    public DuplicateKeyRowException(SqlException e) : base(e.Message, e)
    {
        if (e.Number != 2601) 
            throw new ArgumentException("SqlException is not a duplicate key row exception", e);

        var regex = @"\ACannot insert duplicate key row in object \'(?<TableName>.+?)\' with unique index \'(?<IndexName>.+?)\'\. The duplicate key value is \((?<KeyValues>.+?)\)";
        var match = new System.Text.RegularExpressions.Regex(regex, System.Text.RegularExpressions.RegexOptions.Compiled).Match(e.Message);

        Data["TableName"] = TableName = match?.Groups["TableName"].Value;
        Data["IndexName"] = IndexName = match?.Groups["IndexName"].Value;
        Data["KeyValues"] = KeyValues = match?.Groups["KeyValues"].Value;
    }
}

The DuplicateKeyRowException class is easy enough to use... just create some error handling code like in previous answers...

public void SomeDbWork() {
    // ... code to create/edit/update/delete entities goes here ...
    try { Context.SaveChanges(); }
    catch (DbUpdateException e) { throw HandleDbUpdateException(e); }
}

public Exception HandleDbUpdateException(DbUpdateException e)
{
    // handle specific inner exceptions...
    if (e.InnerException is System.Data.SqlClient.SqlException ie)
        return HandleSqlException(ie);

    return e; // or, return the generic error
}

public Exception HandleSqlException(System.Data.SqlClient.SqlException e)
{
    // handle specific error codes...
    if (e.Number == 2601) return new DuplicateKeyRowException(e);

    return e; // or, return the generic error
}

View more solutions

Comments

[Sinan ILYAS]

After checking out the code you shared, I can now see that the problem with my code is so obvious. I should write "ex.InnerException.InnerException as SqlException" instead of "ex.InnerException as SqlException".

[Sinan ILYAS]

Unfortunately, catch(SqlException ex) doesn't catch the Unique Key violation exception and throws this error: An exception of type 'System.Data.Entity.Infrastructure.DbUpdateException' occurred in EntityFramework.dll but was not handled in user code

[Sinan ILYAS]

Inspecting "UniqueConstraint" in the error message should work, but it doesn't seem to be the best approach.

[Learner]

Is there a way to detect also on which column violation occurred? There may be multiple unique keys in one table...

[ken2k]

@Learner The only way I can think of would be to parse the error message (which states the name of the constraint / column), but it wouldn't be a very good solution (error messages might be updated in the future, and more important, are translated in multiple languages)

[Learner]

Yeah, I thought so... Hopefully they won't translate my unique key name :)... I was thinking to try searching the unique key names inside error message. But is true that it might disappear completely with the new versions... Thanks... Maybe I should stick to "check before operation"

[GDS]

Along the same lines as this answer is this interesting blog

[Daniel Lobo]

Doesn't this break the pattern of the ORM, creating a direct dependency to the database? Does this means that every time I use other databases I have to reprogram the Exception handling to recognize the specific codes?

[Steve Haselschwerdt]

I like the fact that this adds the extra error code and the switch makes the code look cleaner, but I think there's a small error where you cast the InnerException to ErrorCode. I think you want to call GetBaseException() instead of using InnerException.

[Steve Haselschwerdt]

Also, this swallows all SqlException types instead of just the unique key violations. I think your default case should probably rethrow.

[Vasil Popov]

Instead of all "if (dbUpdateEx.InnerException != null) and after that if (dbUpdateEx.InnerException.InnerException is SqlException ...)" could be used "var sqlException = ex.GetBaseException() as SqlException; if (sqlException != null) { .... }" to clear up the code.

[Tomas]

@ken2k This code depends on SQL Server implementation and will not work for other databases. As correctly Daniel Lobo mentioned it breaks ORM idea.

[LarryBud]

I'm just wondering how many times someone has moved their apps to a completely different database system? I've been programming 30 years. Never once did we migrate an application to a different database system

[Gert Arnold]

Well, ex.InnerException.InnerException can be anything, or throw an NRE. This isn't safe.