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!
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
HI Giorgi,
Yes, they look really useful.
Replacing the IStateManager is clever but are there any down sides?
Hi Jon,
I’m not aware of any downside apart from the fact that the interface is marked as:
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
Hi Giorgi,
Yep, I saw the comment which is why I asked. I too use classes that have that comment on (SqlServerDesignTimeServices etc., which contains the code to reverse engineer the database), so I’m not against it.
I’m quite interested why you used that – was it to override the SaveChanges / SaveChangesAsync methods? I haven’t seen that done before – it looks like an interesting way to do things. I’d like to know more.
Great that your on EF Community Standup – I will certainly watch that!
Yes, I wanted to override it without having the user inherit from a custom DbContext base class. That was the only way I found
Thanks – nice idea. I have a library that needs to intercept SaveChanges / SaveChangesAsync and I went for a custom DbContext base class. Maybe your way is better – I need to think about that.
In EF 5 you can probably achieve it with interceptors: https://docs.microsoft.com/en-us/ef/core/logging-events-diagnostics/interceptors
Yep, saw that discussion on the EF Core standup. I need to look into that sometime.
PS. People liked what you had done!
Hi Anon,
The name “CheckHandleError” represents the exception handler you write and where you would place it to catch the exceptions.
I then show an example method called SaveChangesExceptionHandler which looks for SQL Server unique errors and then calls the UniqueErrorFormatter method to turn that into a user-friendly error message.
I hope that helps.
Please can you help me with that?
Where is the checkHandleError function?
Excuse me, does it work on .net framework 4.x?
There is some example in .net framework.
Thanks.
Hi Rick,
This will work with EF Core on .NET framework 4.x.
If you are using EF6.x then the approach will work, but the code inside the exception handler will be slightly different.
Awesome article, but I hope this will eventually turn up in the EF Core repo.
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.
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.
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.
Check out https://github.com/Giorgi/EntityFramework.Exceptions
Excellent article. But surely there must be a typo in the section My generalized SaveChangesExceptionHandler
if (sqlEx == null)
?Hi f00,
Well spotted! Yep, that wasn’t correct – I have fixed it.
Cheers 🙂