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

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
}