Duplicate key exception from Entity Framework?

Question

I'm trying to catch the exception thrown when I insert a already existing user with the given username into my database. As the title says then I'm using EF. The only exception that's thrown when I try to insert the user into to db is a "UpdateException" - How can I extract this exception to identify whether its a duplicate exception or something else?

Answer 1

catch (UpdateException ex)
{
    SqlException innerException = ex.InnerException as SqlException;
    if (innerException != null && innerException.Number == ??????)
    {
        // handle exception here..
    }
    else
    {
        throw;
    }
}

Put the correct number at ?????? that corresponds to unique constraint violation (I don't know it from the top of my head).

Answer 2

Because I'm using EntityFramework with C#, I had to make a minor change to this - hope it helps anyone...

try
{
    await db.SaveChangesAsync();
}
catch (DbUpdateException ex)
{
    SqlException innerException = ex.InnerException.InnerException as SqlException;
    if (innerException != null && (innerException.Number == 2627 || innerException.Number == 2601))
    {
        //your handling stuff
    }
    else
    {
        throw;
    }
}

My issue came about because I needed DbUpdateException instead of UpdateException, and my InnerException object had an additional InnerException object that contained the Number I needed...

Answer 3

Now in C# 6.0 you should be able to do something like this:

catch (UpdateException ex) when ((ex.InnerException as SqlException)?.Number == ??????)
{
    // Handle exception here
}

Answer 4

Now in C# 7 you can use the is operator

// 2627 is unique constraint (includes primary key), 2601 is unique index
catch (UpdateException ex) when (ex.InnerException is SqlException sqlException && (sqlException.Number == 2627 || sqlException.Number == 2601))
{

}

Answer 5

If you need to do the same in Entity Framework Core you can use the library that I built which provides strongly typed exceptions including UniqueConstraintException: EntityFramework.Exceptions

It allows you to catch types exceptions like this:

using (var demoContext = new DemoContext())
{
    demoContext.Products.Add(new Product
    {
        Name = "a",
        Price = 1
    });

    demoContext.Products.Add(new Product
    {
        Name = "a",
        Price = 10
    });

    try
    {
        demoContext.SaveChanges();
    }
    catch (UniqueConstraintException e)
    {
        //Handle exception here
    }
}

All you have to do is install it from Nuget and call it in your OnConfiguring method:

class DemoContext : DbContext
{
    public DbSet<Product> Products { get; set; }
    public DbSet<ProductSale> ProductSale { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseExceptionProcessor();
    }
}

Answer 6

Following @peteski and @Sam

or keyword available since C# 9:

catch (UpdateException ex) when (ex.InnerException is SqlException { Number: 2627 or 2601 })
{

}

Answer 7

I think its better if you prevent the exception from happening. If its possible with your code, I would do the following:

When using entity framework, the best thing to do is to first try and get the entry that will cause you the trouble, with LINQ's SingleOrDefault. Now you can update the gotten entity with the instance you wanted to insert, safes you an ID number with auto-increment if you use it. If SingleOrDefault is null you can safely add your entity.

example of code:

    public override void AddOrUpdate(CustomCaseSearchCriteria entity)
    {
        var duplicateEntityCheck = GetSingleByUniqueConstraint(entity.UserCode, entity.FilterName);
        if (duplicateEntityCheck != null)
        {
            duplicateEntityCheck.Overwrite(entity);
            base.Update(duplicateEntityCheck);
        }
        else
            base.Add(entity);
    }

    public virtual CustomCaseSearchCriteria GetSingleByUniqueConstraint(string userCode, string filterName)
    {
        return GetAllInternal().SingleOrDefault(sc => sc.UserCode == userCode && sc.FilterName == filterName);
    }

Answer 8

For any DB type you can use reflection and the correspondent error number (for MySql 1062):

try
{
    var stateEntries = base.SaveChanges();
}
catch (Exception e)
{
    if(e is DbUpdateException)
    {
        var number = (int)e.InnerException.GetType().GetProperty("Number").GetValue(e.InnerException);
        if (e.InnerException!= null && (number == 1062))
        {
            //your handling stuff
        }
        else
        {
            messages.Add(e.InnerException.Source, e.InnerException.Message);
        }
    }
    else if (e is NotSupportedException || e is ObjectDisposedException || e is InvalidOperationException)
    {
        messages.Add(e.InnerException.Source, e.InnerException.Message);
    }
}