EF Core handling index violation best practices

Question

I need to handle different kinds of exceptions that comes from SQL. In most cases these exceptions are coming from index violations.

It includes at least:

DeleteBehavior.Restrict violation
Unique constraint violation
Duplicated key

I haven't found anything related to proper way of handling this. So, I came up with this solution:

try
{
    context.SaveChanges();
}
catch (Exception exception)
{
    if (exception.GetBaseException() is not SqlException sqlException) 
       throw;

    switch (sqlException.Number)
    {
        // Error codes we want to consider. The codes are for SQL Server provider.
        case 2627: // Unique constraint error
        case 547: // Constraint check violation
        case 2601: // Duplicated key row error
            // Checking if error message informing that an index was violated.
            // If so, this message will contain the name of index.
            if (sqlException.Message.Contains(IndexNames.MyIndexName))
            {
                // An example of error message:
                // Microsoft.Data.SqlClient.SqlException (0x80131904):
                // The INSERT statement conflicted with the FOREIGN KEY constraint "MyIndexName".
                // The conflict occurred in database "MyDatabase", table "dbo.Foo", column 'Id'.
                // The statement has been terminated.


                // Here I can do whatever I want knowing violated index name.
            }
        break;
    }
}

This is working code (tested on SQL Server), but it is not too reliable. For example, different databases may display errors differently. They will have different codes, so you have to write an error handler for each database separately.

And in general, the substring search doesn't look very good to me in this situation. It does not look like a very solid and maintainable solution. Especially when we are working with EF Core, a quite powerful framework.

The purpose of this question is to find more elegant ways to handle errors of this kind, if they exist.

There is DbUpdateException class. | Look at [EntityFramework.Exceptions](https://github.com/Giorgi/EntityFramework.Exceptions) library. — Alexander Petrov, Apr 15 '22 at 17:58
See also "C# 6.0 Exception Filters try catch when": https://stackoverflow.com/questions/38497774/catching-exceptions-with-catch-when — Stefan Wuebbe, Apr 15 '22 at 18:31

score 3 · Answer 1 · answered Jul 03 '22 at 21:09

3

This is exactly why I created EntityFramework.Exceptions library. With this library you don't have to deal with different error numbers and simply do:

try
{
    context.SaveChanges();
}
catch (UniqueConstraintException e)
{
    //Handle unique exception here
}
catch (ReferenceConstraintException e)
{
    //Handle reference exception here
}

answered Jul 03 '22 at 21:09

Giorgi

30,270
13
89
125

Great work! But it's not clear to me how to determine the index or constraint that was violated? Is it the same approach that I used in my code snippet using `string.Contains`? – Artem Balianytsia Jul 04 '22 at 11:49
1

@ArtemBalianytsia There is no easy way to determine that. You will need to use string manipulation to get that info. – Giorgi Jul 04 '22 at 13:01

EF Core handling index violation best practices

1 Answers1

Linked