Entity Framework Core – validating data and catching SQL errors

Last Updated: July 31, 2020 | Created: July 15, 2018

This article is about how to catch SQL errors, like unique index violations, and turning them into user-friendly error messages. This capability adds to the general area of validation of data being written to the database, especially around EF Core.

This article comes from needing this capability in my GenericServices and GenericBizRunner libraries while working on a project for a client. I also extended the design to handle concurrency issues in both libraries, as one of the users of my GenericServices library was asking for it.

TL;DR; – summary

When EF Core writes data out to the database it doesn’t validate that data (see Introduction to validation section for more on validation). However, a relational database will apply its own validation, such as checking that a unique index constraint hasn’t been violated, and will throw an exception if any constraint is breached.

The problem with database exception messages is that they aren’t user-friendly, and can reveal something about your database structure (which is a possible security breach), so you can’t show them directly to the user. This article describes how to capture these database exceptions in EF Core and turn them into user-friendly error messages.

Introduction to validation in EF

Note: if you know about data validation and how EF Core does (or doesn’t) validate data on save then you can skip this section.

Data validation in .NET is about checking that the data in a class fits within certain rules. Typical validation rules are things like the attribute [MaxLength(100)] on a string. Other more complex validation rules can be applied via the IValidatableObject Interface. Some of these attributes, like [MaxLength(…)], [Required], etc. are also used by EF to set the size, nullability etc. of columns in the database.

Any validation rules are typically checked at the front end, say in ASP.NET Core, but can also be checked when that data is saved to the database. The default state for EF6.x is that the data written to the database is validated, but in EF Core the data isn’t validated – the reasoning is that the data  its most likely been validated earlier, so leaving out validation makes the save quicker.

Personally, when I create data in a business logic I always validate the data, as no front-end checks have been applied and the business logic might get things wrong. This is why my GenericBizRunner library defaults to validating the data on save, while my GenericServices library, which works with the front-end CRUD (Create/Read/Update/Delete) accesses, defaults to not validating the data.

But as I said at the beginning, whether you validate the data or not the relational database will have some constraints that it will always apply – you can’t bypass them for the good reason that the database wants to keep its state valid at all times. If you breach a database constraint in EF6.x or EF Core you will get an exception.

How to capture a database exception in EF Core

When you call SaveChanges/SaveChangesAsync then a range of exceptions can occur, from EF Core caught issues like concurrency issues (DbUpdateConcurrencyException, to database issues like the example of the unique index violation (DbUpdateException). Here is an example of catching a DbUpdateException taken from my book “Entity Framework Core in Action” (section 10.7.3).

try
{
   _context.SaveChanges();
}
catch (DbUpdateException e)
{
   //This either returns a error string, or null if it can’t handle that error
   var error = CheckHandleError(e);
   if (error != null)
   {
      return error; //return the error string
   }
   throw; //couldn’t handle that error, so rethrow
}

In the case of the DbUpdateException has an inner exception, which is the actual database exception containing the information about the constraint violation. The type and content of this inner exception differs for each database type.

The other thing to note is that the calling method has to return either success, or one or more error messages.

My generalized SaveChangesExceptionHandler

For my libraries I wanted to generalise the approach to cover both a) any type of exception, b) any type of databases. The signature for the SaveChangesExceptionHandler is

Func<Exception, DbContext, IStatusGeneric>

The method takes in the exception to decode and returns either a status, which can have errors or a success message, or null if it doesn’t handle the error type it was given. I also supply the current DbContext, which you would need if you were trying to handle a DbUpdateConcurrencyException.

Example exception handler targeting SQL Server

For my example I am going to show you a exception hander that works for SQL Server, but the approach works for any database – you just have to decode the database errors from the inner exception specific to your database.

The code below is written to capture the inner SqlException and only handles SQL unique index violations. It starts with the method that detects each type of error you want to handle, and then calling the appropriate method. It’s basically a sophisticated switch pattern, and you can add more error handling by adding extra tests.

public const int SqlServerViolationOfUniqueIndex = 2601;
public const int SqlServerViolationOfUniqueConstraint = 2627;

public static IStatusGeneric SaveChangesExceptionHandler
    (Exception e, DbContext context)
{
    var dbUpdateEx = e as DbUpdateException;
    var sqlEx = dbUpdateEx?.InnerException as SqlException;
    if (sqlEx != null)
    {
        //This is a DbUpdateException on a SQL database

       if (sqlEx.Number == SqlServerViolationOfUniqueIndex ||
           sqlEx.Number == SqlServerViolationOfUniqueConstraint)
       {
          //We have an error we can process
          var valError = UniqueErrorFormatter(sqlEx, dbUpdateEx.Entries);
          if (valError != null)
          {
              var status = new StatusGenericHandler();
              status.AddValidationResult(valError);
              return status;
          }
          //else check for other SQL errors
       }   
    }

    //add code to check for other types of exception you can handle
    …

    //otherwise exception wasn't handled, so return null
    return null;
} 

Having found the unique index errors I call a method to specifically handle that sort of error. I should say that I add a constraint/key name following the pattern ‘UniqueError_<EntityName>_<PropertyName> to any unique indexes or primary keys that I want to handle. This both gives me more info to show the user, and stops me trying to handle errors on properties I didn’t expect to have an error. Here is my UniqueErrorFormatter method.

private static readonly Regex UniqueConstraintRegex =
    new Regex("'UniqueError_([a-zA-Z0-9]*)_([a-zA-Z0-9]*)'", RegexOptions.Compiled);

public static ValidationResult UniqueErrorFormatter(SqlException ex, IReadOnlyList<EntityEntry> entitiesNotSaved)
{
    var message = ex.Errors[0].Message;
    var matches = UniqueConstraintRegex.Matches(message);

    if (matches.Count == 0)
        return null;

    //currently the entitiesNotSaved is empty for unique constraints - see https://github.com/aspnet/EntityFrameworkCore/issues/7829
    var entityDisplayName = entitiesNotSaved.Count == 1
        ? entitiesNotSaved.Single().Entity.GetType().GetNameForClass()
        : matches[0].Groups[1].Value;

    var returnError = "Cannot have a duplicate " +
                      matches[0].Groups[2].Value + " in " +
                      entityDisplayName + ".";

    var openingBadValue = message.IndexOf("(");
    if (openingBadValue > 0)
    {
        var dupPart = message.Substring(openingBadValue + 1,
            message.Length - openingBadValue - 3);
        returnError += $" Duplicate value was '{dupPart}'.";
    }

    return new ValidationResult(returnError, new[] {matches[0].Groups[2].Value});
}

There is some weird regex and decoding of the SQL error message, and that is because the error string isn’t that simple to decode. Here is an example error message:

Cannot insert duplicate key row in object ‘dbo.Books’ with unique index ‘UniqueError_Book_ISBN’. The duplicate key value is (9781617294563).

The UniqueErrorFormatter method reformats this into a message

Cannot have a duplicate ISBN in Book. Duplicate value was ‘ 9781617294563’.

Notice on lines 13 to 15 I try to find the entity class so that I can return a user friendly and localized name, but the name in the constraint/key name is pretty good (unless you have a TPH class).

The SaveChangesWithValidation code

The code above is called within my SaveChangedWithValidation method, which I show below. The method returns the IStatusGeneric interface I use in my libiraries. This contains either a success message if there were no errors, or a list of errors if problems were found.

public static IStatusGeneric SaveChangesWithValidation(this DbContext context, IGenericServicesConfig config)
{
    var status = context.ExecuteValidation();
    if (!status.IsValid) return status;

    context.ChangeTracker.AutoDetectChangesEnabled = false;
    try
    {
        context.SaveChanges();
    }
    catch (Exception e)
    {
        var exStatus = config?.SaveChangesExceptionHandler(e, context);
        if (exStatus == null) throw;       //error wasn't handled, so rethrow
        status.CombineStatuses(exStatus);
    }
    finally
    {
        context.ChangeTracker.AutoDetectChangesEnabled = true;
    }

    return status;
}

The ExecuteValidation code looks like this

private static IStatusGeneric ExecuteValidation(this DbContext context)
{
    var status = new StatusGenericHandler();
    foreach (var entry in
        context.ChangeTracker.Entries()
            .Where(e =>
                (e.State == EntityState.Added) ||
                (e.State == EntityState.Modified)))
    {
        var entity = entry.Entity;
        var valProvider = new ValidationDbContextServiceProvider(context);
        var valContext = new ValidationContext(entity, valProvider, null);
        var entityErrors = new List<ValidationResult>();
        if (!Validator.TryValidateObject(
            entity, valContext, entityErrors, true))
        {
            status.AddValidationResults(entityErrors);
        }
    }

    return status;
}

NOTE: You can find all of this code in the class SaveChangesExtensions in the GenericServices library.

Enabling this feature in my GenericServices and GenericBizRunner

You need to configure the libraries to take your error handler. GenericBizRunner is the simplest as it already defaults to validation on writes to the database. Here is the configuration code.

services.RegisterGenericBizRunnerBasic<MyDbContext>(
   new GenericBizRunnerConfig
{
    SaveChangesExceptionHandler = 
        GenericBizRunnerErrorHandler.SaveChangesExceptionHandler
});

The GenericServices is a bit more complex, as by default it doesn’t validate on saving to the database, so your error handler won’t be called. You can turn on validation on a case-by-case via the PerDtoConfig<TDto, TEntity> class, or turn on validation for all writes to the database via the global configuration, as shown below.

services.GenericServicesSimpleSetup<MyDbContext>(
new GenericServicesConfig
    {
        DtoAccessValidateOnSave = true,     //we use  Dto access for Create/Update
        DirectAccessValidateOnSave = true,  //And direct access for Delete
        SaveChangesExceptionHandler = GenericServiceErrorHandler.SaveChangesExceptionHandler
}, Assembly.GetAssembly(typeof(MyDto)));

Other things you could do with this

As I said earlier one of my users of the GenericServices library wanted to know if GenericServices could handle concurrency issues, and I had to say no. But, now with my generalised SaveChangesExceptionHandler both libraries can handle concurrency issues. I haven’t done this yet, but the steps would be:

  • In the ‘switch’ method you would detect that the exception was a DbUpdateConcurrencyException and the type of the entity it happened on. Then you would call a method designed to handle concurrency issues on that entity type.
  • In that method you would (try) to fix the issue. If you have then you need to call SaveChanges (within another try/catch in case the error happened again) to save the corrected update.
  • If successful you return a status of success, which the system would return to the user. But if there were errors it couldn’t handle it can report to the user that the process failed.

NOTE: that handling concurrency issues is quite complex and hard to test, so I’m not saying this is simple. But you can now do this in in my GenericServices and GenericBizRunner libraries.

NOTE: I cover this concurrency issues in detail in section 8.7 in my book – it takes 12 pages!

Conclusion

Catching SQL errors and turning them into user-friendly error messages is hard work, but in real applications its often the only way to ensure the system works. Otherwise the user will be presented with the rather unhelpful “There was an error” page. Certainly not acceptable in real applications.

I spent bit of my own time to generalised the error handler in my GenericServices and GenericBizRunner libraries to cover more possible exceptions and work with any database. Now you have access to this feature in both of my libraries, or you can copy my approach in your own applications.

I hope this helps you.

Happy coding!

5 3 votes
Article Rating
Subscribe
Notify of
guest
21 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments
Giorgi
2 years ago

Have you seen https://github.com/Giorgi/EntityFramework.Exceptions? It has typed exceptions for unique constraint violation, length violation, required violation and so on. It also supports other database apart from Sql Server

Giorgi
2 years ago
Reply to  Jon P Smith

Hi Jon,

I’m not aware of any downside apart from the fact that the interface is marked as:

This is an internal API that supports the Entity Framework Core infrastructure and not subject to the same compatibility standards as public APIs. It may be changed or removed without notice in any release.

By the way, I will be talking about the library tomorrow on EF Community Standup: https://www.youtube.com/watch?v=aUl5QfswNU4&list=PLdo4fOcmZ0oX-DBuRG4u58ZTAJgBAeQ-t&index=0

Giorgi
2 years ago
Reply to  Jon P Smith

Yes, I wanted to override it without having the user inherit from a custom DbContext base class. That was the only way I found

Giorgi
2 years ago
Reply to  Jon P Smith

In EF 5 you can probably achieve it with interceptors: https://docs.microsoft.com/en-us/ef/core/logging-events-diagnostics/interceptors

Anon
Anon
2 years ago

Please can you help me with that?

Anon
Anon
2 years ago

Where is the checkHandleError function?

Rick
Rick
3 years ago

Excuse me, does it work on .net framework 4.x?

There is some example in .net framework.

Thanks.

Benjamin Vertonghen
Benjamin Vertonghen
3 years ago

Awesome article, but I hope this will eventually turn up in the EF Core repo.

Jon P Smith
3 years ago

Hi Benjamin,

Glad you liked the article. I don’t think the EF Core team will add these features. They decided to not add validation to the SaveChanges call (EF6 did validate, but their view is the front end normally does validation). Also catching database errors is unlikely, as there are literally thousands of possible database errors.

But as my article shows, its not hard to add these features. My libraries contain validation features, which can be turned on/off. I also have a place where you can insert a database error handler.

nightrush
nightrush
3 years ago

But isn’t this making EF Core coupled to SQL server? If you wanted to switch to Postgres or something else you’d need to add support for those inner exceptions as well, or replace them. Because error codes are different.

Jon P Smith
3 years ago
Reply to  nightrush

Hi nightrush,

No, my generalized SaveChangesExceptionHandler captures any exception. The exception for a database error is EF Core’s DbUpdateException, with the inner exception coming from the database provider. You would need to write a exception handler that works with your database’s exceptions for the reason you state – the error feedback is unique to each database type.

I see that my article didn’t make that clear that it is designed to work with any database. Thanks for pointing that out and I have updated the article to make that clearer.

f00
f00
3 years ago

Excellent article. But surely there must be a typo in the section My generalized SaveChangesExceptionHandler
if (sqlEx == null) ?

Jon P Smith
3 years ago
Reply to  f00

Hi f00,

Well spotted! Yep, that wasn’t correct – I have fixed it.

f00
f00
4 years ago
Reply to  Jon P Smith

Cheers 🙂