You should do validation on the UI first then handle specific errors related to Entity Framework.
Create a Model and use data annotations :
using System.ComponentModel.DataAnnotations;
public class YourViewModel
{
[Required]
[Range(0, 15, ErrorMessage = "Can only be between 0 .. 15")]
public int stNumber { get; set; }
}
In your controller return the model to the view:
var model = new YourViewModel();
return View(model);
Bind your textbox to the model by adding the model to your view and using some tag helpers:
@using YourProject.WebUI.Models
@model YourViewModel
@Html.TextBoxFor(m => m.stNumber )
@Html.ValidationMessageFor(m => m.stNumber )
Now when someone tries to enter a non numeric or a number that is out of range an error will be displayed to the user before bad data is ever sent back to the controller.
To handle Entity Framework exceptions use a try catch:
try
{
var entity = context.yourEntity.FirstOrDefault(o => o.Id == custId);
if (entity == null) return false;
entity.value= stNumber;
entity.ModifiedBy = userId;
entity.ModifiedDate = DateTime.Now;
Db.SaveChanges();
return true;
}
catch (DbUpdateException Ex)
{
Console.WriteLine(Ex.InnerException.Message);
return false;
}
Other Exception types include:
DbUpdateException
An error occurred sending updates to the database.
DbUpdateConcurrencyException
A database command did not affect the expected number of rows. This usually indicates an optimistic concurrency violation; that is, a row has been changed in the database since it was queried.
DbEntityValidationException
The save was aborted because validation of entity property values failed.
NotSupportedException
An attempt was made to use unsupported behavior such as executing multiple asynchronous commands concurrently on the same context instance.
ObjectDisposedException
The context or connection have been disposed.
InvalidOperationException
Some error occurred attempting to process entities in the context either before or after sending commands to the database.
Here we learn how to handle error in Entity Framework, details exception handling techniques example in C#.
Error handling is very important aspect in application development life cycle, there are many different ways error handling is implemented, while developing any application.
In this tutorial you will learn how to implement error handling in entity framework, so we can catch the right error at data access layer and make the required changes to fix the error.
One simple step to implement error handling could be using try catch block like example below.
try
{
using (var context = new Entities())
{
var query = from c in context.vwProduct
join o in context.tbOfferProduct on c.ProductId equals o.ProductId
where (o.OfferId == offerId)
select c;
products = query.ToList<vwProduct>();
}
}
catch (Exception ex)
{
ErrorDTO.TrackError(ex);
}
But above example may not be sufficient to know what the error is!
It will throw very common message or error in inner exception property, which may not reveal the actual error.
So, we will implement DbEntityValidationException, which will catch some entity specify error details.
catch (DbEntityValidationException e)
{
ErrorDTO.TrackError(e.InnerException);
}
Above same example can be written differently like example below, this will help us to know exactly which field is causing error, and what type of error is that.
entity framework exception handling example
In following example, I am looping through the entity collection can catching exception details in a string builder object.
catch (DbEntityValidationException e)
{
StringBuilder strErr = new StringBuilder();
foreach (var eve in e.EntityValidationErrors)
{
strErr.Append($"Entity of type {eve.Entry.Entity.GetType().Name}" +
$"in the state {eve.Entry.State} "+
$"has the following validation errors:");
foreach (var ve in eve.ValidationErrors)
{
strErr.Append($"Property: {ve.PropertyName}," +
$" Error: {ve.ErrorMessage}");
}
foreach (var ve in eve.ValidationErrors)
{
strErr.Append($"Property: {ve.PropertyName}, " +
$"Value: {eve.Entry.CurrentValues.GetValue<object>(ve.PropertyName)}," +
$" Error: {ve.ErrorMessage}");
}
}
ErrorDTO.TrackError("AddProduct", strErr.ToString());
}
Apart from above DbEntityValidationException, there are few other type of exception, which are also helpful in some scenario, like EntityCommandExecutionException, DbUpdateException etc.
Here is the complete exception handling implementation example,
Notice, how to catch Entity Validation Errors in following code
using System.Data.Entity.Validation;
using System.Data.Entity.Infrastructure;
using System.Data.Entity;
using System.Data.Entity.Core;
public List<vwProduct> GetProductsByOffer(long offerId)
{
List<vwProduct> products = null;
try
{
using (var context = new Entities())
{
var query = from c in context.vwProduct
join o in context.tbOfferProduct on c.ProductId equals o.ProductId
where (o.OfferId == offerId)
select c;
products = query.ToList<vwProduct>();
}
}
catch (DbEntityValidationException e)
{
StringBuilder strErr = new StringBuilder();
foreach (var eve in e.EntityValidationErrors)
{
strErr.Append($"Entity of type {eve.Entry.Entity.GetType().Name}" +
$"in the state {eve.Entry.State} "+
$"has the following validation errors:");
foreach (var ve in eve.ValidationErrors)
{
strErr.Append($"Property: {ve.PropertyName}," +
$" Error: {ve.ErrorMessage}");
}
foreach (var ve in eve.ValidationErrors)
{
strErr.Append($"Property: {ve.PropertyName}, " +
$"Value: {eve.Entry.CurrentValues.GetValue<object>(ve.PropertyName)}," +
$" Error: {ve.ErrorMessage}");
}
}
ErrorDTO.TrackError("AddProduct", strErr.ToString());
}
catch (DbUpdateException ex)
{
ErrorDTO.TrackError(ex.Source, ex.InnerException);
}
catch (EntityCommandExecutionException cex)
{
ErrorDTO.TrackError(cex.InnerException);
}
catch (Exception ex)
{
ErrorDTO.TrackError(ex);
}
return products;
}
ex {«An error occurred while executing the command definition. See the inner exception for details.»}
System.Exception {System.Data.Entity.Core.EntityCommandExecutionException}
While dealing with complex entity, we should think of implementing all possible exception type, so the code can catch the exact error message and provide the log details to developers, it will be easy to fix the error quickly.
I cannot tell you how many times I’ve had the following conversation
“Hey I’m getting an error”
“What’s the error?”
“DBUpdateException”
“OK, what’s the message though, that could be anything”
“ahhh.. I didn’t see…..”
Frustratingly, When doing almost anything with Entity Framework including updates, deletes and inserts, if something goes wrong you’ll be left with the generic exception of :
Microsoft.EntityFrameworkCore.DbUpdateException: ‘An error occurred while saving the entity changes. See the inner exception for details.’
It can be extremely annoying if you’re wanting to catch a particular database exception (e.g. It’s to be expected that duplicates might be inserted), and handle them in a different way than something like being unable to connect to the database full stop. Let’s work up a quick example to illustrate what I mean.
Let’s assume I have a simple database model like so :
class BlogPost
{
public int Id { get; set; }
public string PostName { get; set; }
}
And I have configured my entity to have a unique constaint meaning that every BlogPost must have a unique name :
modelBuilder.Entity<BlogPost>()
.HasIndex(x => x.PostName)
.IsUnique();
If I do something as simple as :
context.Add(new BlogPost
{
PostName = "Post 1"
});
context.Add(new BlogPost
{
PostName = "Post 1"
});
context.SaveChanges();
The *full* exception would be along the lines of :
Microsoft.EntityFrameworkCore.DbUpdateException: ‘An error occurred while saving the entity changes. See the inner exception for details.’
Inner Exception
SqlException: Cannot insert duplicate key row in object ‘dbo.BlogPosts’ with unique index ‘IX_BlogPosts_PostName’. The duplicate key value is (Post 1).
Let’s say that we want to handle this exception in a very specific way, for us to do this we would have to have a bit of a messy try/catch statement :
try
{
context.SaveChanges();
}catch(DbUpdateException exception) when (exception?.InnerException?.Message.Contains("Cannot insert duplicate key row in object") ?? false)
{
//We know that the actual exception was a duplicate key row
}
Very ugly and there isn’t much reusability here. If we want to catch a similar exception elsewhere in our code, we’re going to be copy and pasting this long catch statement everywhere.
And that’s where I came across the EntityFrameworkCore.Exceptions library!
Using EntityFrameworkCore.Exceptions
The EntityFrameworkCore.Exceptions library is extremely easy to use and I’m actually somewhat surprised that it hasn’t made it’s way into the core EntityFramework libraries already.
To use it, all we have to do is run the following on our Package Manager Console :
Install-Package EntityFrameworkCore.Exceptions.SqlServer
And note that there are packages for things like Postgres and MySQL if that’s your thing!
Then with a single line for our DB Context we can set up better error handling :
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseExceptionProcessor();
}
If we run our example code from above, instead of our generic DbUpdateException we get :
EntityFramework.Exceptions.Common.UniqueConstraintException: ‘Unique constraint violation’
Meaning we can change our Try/Catch to be :
try
{
context.SaveChanges();
}catch(UniqueConstraintException ex)
{
//We know that the actual exception was a duplicate key row
}
Much cleaner, much tidier, and far more reusable!
When using Entity Framework Core for data access all database exceptions are wrapped in DbUpdateException. If you need to know whether the exception was caused by a unique constraint, value being too long or value missing for a required column you need to dig into the concrete DbException subclass instance and check the error number to determine the exact cause.
EntityFramework.Exceptions simplifies this by handling all the database specific details and throwing different exceptions for different cases. All you have to do is
inherit your DbContext from ExceptionProcessorContext and handle the exception(s) (such as UniqueConstraintException, CannotInsertNullException, MaxLengthExceededException, NumericOverflowException) you need.
The Problem with Entity Framework Exceptions
Let’s say we have a Product table which has Name column with a unique index and Price column. Entity Framework context will look like this:
class DemoContext : DbContext
{
public DbSet<Product> Products { get; set; }
protected override void OnModelCreating(ModelBuilder builder)
{
builder.Entity<Product>().Property(b => b.Price).HasColumnType("decimal(5,2)").IsRequired();
builder.Entity<Product>().Property(b => b.Name).IsRequired().HasMaxLength(15);
builder.Entity<Product>().HasIndex(u => u.Name).IsUnique();
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(@"Data Source=localhost;Initial Catalog=Test;Integrated Security=True;Connect Timeout=30;");
}
}
If we try to insert two records with the same name we will get DbUpdateException. As we have already mentioned DbUpdateException is thrown every time when saving changes to the database fails. In order to check that the exception was caused by the unique index we need to get the specific database DbException subclass instance and check the error number. In case of SQL Server we can do it like this:
using (var demoContext = new DemoContext())
{
demoContext.Products.Add(new Product
{
Name = "Moon Lamp",
Price = 1
});
demoContext.Products.Add(new Product
{
Name = "Moon Lamp",
Price = 10
});
try
{
demoContext.SaveChanges();
}
catch (DbUpdateException e)
{
var sqlException = e.GetBaseException() as SqlException;
//2601 is error number of unique index violation
if (sqlException != null && sqlException.Number == 2601)
{
//Unique index was violated. Show corresponding error message to user.
}
}
}
While this works it has several disadvantages. First of all it is repetitive to write this code every time we try to save changes to database. Secondly, there are other database errors to handle such as trying to insert null in non-null column or trying to insert longer value than the column allows. Finally, the error numbers are different for different database servers.
To avoid these issues I have created a library
EntityFramework.Exceptions which handles database specific errors and throws different exceptions for different database errors.
EntityFramework.Exceptions – Easy Way to Handle Exceptions
With EntityFramework.Exceptions we can rewrite the above code like this:
using (var demoContext = new DemoContext())
{
demoContext.Products.Add(new Product
{
Name = "Moon Lamp",
Price = 1
});
demoContext.Products.Add(new Product
{
Name = "Moon Lamp",
Price = 10
});
try
{
demoContext.SaveChanges();
}
catch (UniqueConstraintException e)
{
//Unique index was violated. Show corresponding error message to user.
}
}
As you can see we no longer have to deal with database specific exception and error numbers. Instead UniqueConstraintException is thrown when either a unique index or unique constraint is violated. As a result our code is cleaner, shorter and easier to understand. What’s more the library provides other exceptions such as CannotInsertNullException, MaxLengthExceededException, NumericOverflowException. For example if we try insert a product with Name longer than 15 characters we will get MaxLengthExceededException exception:
using (var demoContext = new DemoContext())
{
demoContext.Products.Add(new Product
{
Name = "Moon Lamp Change 3 Colors",
Price = 1
});
try
{
demoContext.SaveChanges();
}
catch (MaxLengthExceededException e)
{
//Max length of Name column exceeded. Show corresponding error message to user.
}
}
Apart from this EntityFrameworkCore.Exceptions supports other database servers such as PostgreSQL and MySQL so if you ever switch to different database server your exception handling code will stay the same.
To get started with EntityFramework.Exceptions all you need to do is install either
SQL Server,
PostgreSQL or
MySQL nuget package and inherit your DbContext from ExceptionProcessorContext:
class DemoContext : ExceptionProcessorContext
{
public DbSet<Product> Products { get; set; }
}
How Does EntityFramework.Exceptions Work ?
The implementation is pretty straightforward. There is an ExceptionProcessorContextBase class in EntityFramework.Exceptions.Common project which inherits from DbContext, overrides SaveChanges and handles any exception that occurs. It gets the database specific exception instance and asks derived classes to tell which exception it should throw for the DbException that occurred. For more details please check the
GitHub repository. If you find the library useful don’t forget to Star the repository. If you have any questions or suggestions you are welcome to submit an issue or send a PR.
EntityFramework.Exceptions
Handle database errors easily when working with Entity Framework Core. Supports SQLServer, PostgreSQL, SQLite, Oracle and MySql
Entity Framework Community Standup Live Show
What does EntityFramework.Exceptions do?
When using Entity Framework Core for data access all database exceptions are wrapped in DbUpdateException. If you need to find
whether the exception was caused by a unique constraint, value being too long or value missing for a required column you need to dig into
the concrete DbException subclass instance and check the error code to determine the exact cause.
EntityFramework.Exceptions simplifies this by handling all the database specific details and throwing different exceptions. All you have
to do is to configure DbContext by calling UseExceptionProcessor and handle the exception(s) such as UniqueConstraintException,
CannotInsertNullException, MaxLengthExceededException, NumericOverflowException, ReferenceConstraintException you need.
How do I get started?
First, install the package corresponding to your database:
PM> Install-Package EntityFrameworkCore.Exceptions.SqlServer
PM> Install-Package EntityFrameworkCore.Exceptions.MySql
PM> Install-Package EntityFrameworkCore.Exceptions.MySql.Pomelo
PM> Install-Package EntityFrameworkCore.Exceptions.PostgreSQL
PM> Install-Package EntityFrameworkCore.Exceptions.Sqlite
PM> Install-Package EntityFrameworkCore.Exceptions.Oracle
Or:
dotnet add package EntityFrameworkCore.Exceptions.SqlServer
dotnet add package EntityFrameworkCore.Exceptions.MySql
dotnet add package EntityFrameworkCore.Exceptions.MySql.Pomelo
dotnet add package EntityFrameworkCore.Exceptions.PostgreSQL
dotnet add package EntityFrameworkCore.Exceptions.Sqlite
dotnet add package EntityFrameworkCore.Exceptions.Oracle
Next, in your DbContext OnConfiguring method call UseExceptionProcessor extension method:
class DemoContext : DbContext { public DbSet<Product> Products { get; set; } public DbSet<ProductSale> ProductSale { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseExceptionProcessor(); } }
You will now start getting different exception for different database error. For example, when a unique constraints fails you will get UniqueConstraintException exception:
using (var demoContext = new DemoContext()) { demoContext.Products.Add(new Product { Name = "a", Price = 1 }); demoContext.Products.Add(new Product { Name = "a", Price = 1 }); try { demoContext.SaveChanges(); } catch (UniqueConstraintException e) { //Handle exception here } }
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!
When starting to work with Entity Framework and SQL Server, you often run into the same errors. Sometimes these errors are caused by missing permissions for the database connection, and sometimes it’s caused the way that the database is accessed. In this article, we will go through some of the most common errors and some of the possible ways these errors could be fixed.
Prerequisites for debugging
To debug the following errors and find the root course of each problem, there is a set of required tools we need to install first. The first one is, of course, a proper logging framework so that the full error, including any inner exceptions, is revealed. We have an example of how to log to elmah.io from Entity Framework. The next is Microsoft SQL Server Management Studio, which can be used to inspect the setup of the SQL Server and the permissions of the users of the databases. The most common mistake is a malformed connection string. The correct format is as the following: Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;
System.Data.SqlClient.SqlException: Login failed for user ‘myUsername’
At first glance, this seems like it would cover a quite simple error: That the user credentials passed were not valid. This would seem like it could only be a problem with the connection string like a misspelling of the username or password, and that’s one of the possible causes of the problem. It could also be a problem in the SQL Server like that your user does not have permission to access the database or that the server does not allow SQL Server authentication. The local version of SQL Server has the Server authentication set to Windows Authentication mode by default, which does not enable the possibility to connect without Integrated Security.
The measures one can take to fix the problem are the following:
- Check that the User Id and Password in the connection string are correct.
- Ensure that
Integrated Securityis set toFalsein the connection string. - Check that the user is present in
Security>Loginsin the SQL Server connection in Microsoft SQL Server Management Studio. - Try resetting the password of the user.
- Ensure that SQL Server authentication is enabled for the server in
Server Properties>Security>Server authenticationand then selectSQL Server and Windows Authentication mode.
System.Data.SqlClient.SqlException: Cannot open database «myDB» requested by the login. The login failed.
The error states that there could not be an established connection to a specific database on the server. This could mean that the specified database in the connection string does not exist or that there was a typo in the connection string.
Possible solutions to the problem could be:
- Check that there are no typos in the
databasefield in the connection string. - If your project is code-first, ensure that you’ve called
myContext.Database.EnsureCreated()before using your context. - If your project is database-first, check the server if the database is present on the server using Microsoft SQL Server Management Studio.
This error states that it was not possible to connect to the specified server. The error often comes together with a long wait time because it wants to make sure that it does not just have a slow connection. A typo could cause the error in the connection string or that the server is configured wrong.
These are some of the ways to troubleshoot the problem:
- Check that the
serverfield in the connection string is written correctly. - Ensure that remote connection is allowed in Microsoft SQL Server Management Studio in
Server Properties>Connection>Remote server connections. - When installing Microsoft SQL Server Management Studio, you also get a program called
SQL Server Configuration Manager. This can only be used on the computer that the server is running. InSQL Network Configuration>Protocols for SERVERNAMETCP/IPneeds to be enabled for it to be accessed.
Microsoft.EntityFrameworkCore.DbUpdateException
This error covers a lot of different errors. What they all have in common is that Entity Framework failed to save the changes to the database. It is essential to inspect the inner exceptions. It is often just a wrapper around all the System.Data.SqlClient.SqlException‘s that occur when the connection is established.
Since the ways to fix these errors are unique to each case, we will list some common causes and how to avoid and fix these errors in general terms.
There have been changes to the models that Entity Framework uses in your project
Entity Framework creates all the tables in a database by itself, but when the models in a project start to change, there is a clash between what Entity Framework expects and what’s actually on the SQL Server. A way to solve this is by using the Migration feature from Entity Framework when you make changes to your models.
The same SQL Server is used for many projects
When a lot of projects use the same database by accident or intentionally, there can be clashes in the naming of tables. This would mean that one project’s Entity Framework connection would think that its table was made, while the scheme is the one of another project. This can be avoided by using unique database names for different projects.
Duplicate of entries with the same primary key
If multiple entries with the same primary key are inserted into a table, then there is a conflict in the database. This can sometimes be solved by doing context.SaveChanges() only once per update and sometimes be avoided by explicitly making foreign keys in one-to-many relations instead of letting Entity Framework handle it.
elmah.io: Error logging and Uptime Monitoring for your web apps
This blog post is brought to you by elmah.io. elmah.io is error logging, uptime monitoring, deployment tracking, and service heartbeats for your .NET and JavaScript applications. Stop relying on your users to notify you when something is wrong or dig through hundreds of megabytes of log files spread across servers. With elmah.io, we store all of your log messages, notify you through popular channels like email, Slack, and Microsoft Teams, and help you fix errors fast.
See how we can help you monitor your website for crashes
Monitor your website
- Download source (ZIP) — 7.4 MB
- Download source (RAR) — 6.2 MB
Introduction
If you are using the EF6 and want to log the database operations, analyze them, then, this is the right place for you.
Background
When I was developing one WebApi project, what I was searching for is an output each query performed by Entity framework should be logged with time. Also, exception if there is any. So, in this section, you will learn how to log commands and queries to the database generated by Entity framework.
Using the Code
There are two ways:
1) Simple Method
using (MyDatabaseEntities context = new MyDatabaseEntities()) { context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s); }
This will log the database operations in the output window. What it does is it writes the operations performed by EntityFramework to the output window. It gives awesome traces.
Have a look:
)
2) IDbCommandInterceptor
This uses the IDbCommandInterceptor Interface. This is in-built in Entity framework 6.
Note: This Interface is available only in Entityframework 6 and later.
Have a look at Interface:
namespace System.Data.Entity.Infrastructure.Interception { public interface IDbCommandInterceptor : IDbInterceptor { void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext); void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext); void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext); void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext); void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext); void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext); } }
Let’s derive this interface to the DatabaseLogger class.
FYI, I have added my log information into database. You may insert into file, Excel, anything you want.
You need not worry about the methods Interface itself is very self explanatory. It has 6 methods.
You can see that I have derived and check comments to understand each methods.
public class DatabaseLogger : IDbCommandInterceptor { static readonly ConcurrentDictionary<DbCommand, DateTime> MStartTime = new ConcurrentDictionary<DbCommand, DateTime>(); public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) { Log(command, interceptionContext); } public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) { OnStart(command); } public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { Log(command,interceptionContext); } public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { OnStart(command); } private static void Log<T>(DbCommand command, DbCommandInterceptionContext<T> interceptionContext) { DateTime startTime; TimeSpan duration; MStartTime.TryRemove(command, out startTime); if (startTime != default(DateTime)) { duration = DateTime.Now - startTime; } else duration = TimeSpan.Zero; const int requestId = -1; var parameters = new StringBuilder(); foreach (DbParameter param in command.Parameters) { parameters.AppendLine(param.ParameterName + " " + param.DbType + " = " + param.Value); } var message = interceptionContext.Exception == null ? $"Database call took {duration.TotalSeconds.ToString("N3")} sec. RequestId {requestId} rnCommand:rn{parameters + command.CommandText}" : $"EF Database call failed after {duration.TotalSeconds.ToString("N3")} sec. RequestId {requestId} rnCommand:rn{parameters.ToString() + command.CommandText}rnError:{interceptionContext.Exception} "; if (duration.TotalSeconds>1 || message.Contains("EF Database call failed after ")) { using (DbContext dbContext = new DbContext()) { Error error = new Error { TotalSeconds = (decimal)duration.TotalSeconds, Active = true, CommandType = Convert.ToString(command.CommandType), CreateDate = DateTime.Now, Exception = Convert.ToString(interceptionContext.Exception), FileName = "", InnerException = interceptionContext.Exception == null ? "" : Convert.ToString(interceptionContext.Exception.InnerException), Parameters = parameters.ToString(), Query = command.CommandText, RequestId = 0 }; dbContext.Errors.Add(error); dbContext.SaveChanges(); } } } public void ScalarExecuted (DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { Log(command, interceptionContext); } public void ScalarExecuting (DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { OnStart(command); } private static void OnStart(DbCommand command) { MStartTime.TryAdd(command, DateTime.Now); } }
Now, I am registering this class to dbcontext.
Explanation: It tells entity framework to use this class for Logging database operations.
public DbContext(): base("name=connectionstring") { DbInterception.Add(new DatabaseLogger()); }
Everything is set up now. You can make an Error model class like this. (This is the code-first model class.)
public class Error { [Key] [Required] public int ErrorId { get; set; } public string Query { get; set; } public string Parameters { get; set; } public string CommandType { get; set; } public decimal TotalSeconds { get; set; } public string Exception { get; set; } public string InnerException { get; set; } public int RequestId { get; set; } public string FileName { get; set; } public DateTime CreateDate { get; set; } public bool Active { get; set; } }
Have a look at Error logs here:
Technically, there are many approaches to log your Database operations. This is the easiest way I saw.
Credits
- Idea of using time intervals: http://stackoverflow.com/a/27365855/2630817
- Resharper for decompiling Interface: https://www.jetbrains.com/resharper/
- Have a look at https://msdn.microsoft.com/en-us/library/dd287191(v=vs.110).aspx for
ConcurrentDictionary
Bonus
I just used this so thought about sharing it here too 🙂
Let’s have a quick look at logging the time taken by each controller. Same table’s structure, same queries.
- Add a new class named
ExecutionTimeFilterlike I have added:public class ExecutionTimeFilter : ActionFilterAttribute { public override void OnActionExecuting(HttpActionContext actionContext) { base.OnActionExecuting(actionContext); actionContext.Request.Properties.Add("Time", Stopwatch.StartNew()); } public override void OnActionExecuted(HttpActionExecutedContext actionContext) { base.OnActionExecuted(actionContext); try { var stopwatch = (Stopwatch)actionContext.Request.Properties["Time"]; actionContext.Request.Properties.Remove("Time"); var elapsedTime = stopwatch.Elapsed; if (!(elapsedTime.TotalSeconds > 10)) return; using (DbContext dbContext = new DbContext()) { Error error = new Error { TotalSeconds = (decimal)elapsedTime.TotalSeconds, Active = true, CommandType = "Action Context", CreateDate = DateTime.Now, Exception = Convert.ToString(actionContext.Request), FileName = "", InnerException = actionContext.Response.ToString(), Parameters = "", Query = "", RequestId = 0 }; dbContext.Errors.Add(error); dbContext.SaveChanges(); } } catch { } } }
- Register it to the
configin my case I am using it in web API so inApp_Start/WebApiConfig.cs:public static void Register(HttpConfiguration config) { config.Filters.Add(new ExecutionTimeFilter()); }
Done! now, every action executed and took more than 10 seconds will log that entry to the database.
This member has not yet provided a Biography. Assume it’s interesting and varied, and probably something to do with programming.
- Download source (ZIP) — 7.4 MB
- Download source (RAR) — 6.2 MB
Introduction
If you are using the EF6 and want to log the database operations, analyze them, then, this is the right place for you.
Background
When I was developing one WebApi project, what I was searching for is an output each query performed by Entity framework should be logged with time. Also, exception if there is any. So, in this section, you will learn how to log commands and queries to the database generated by Entity framework.
Using the Code
There are two ways:
1) Simple Method
using (MyDatabaseEntities context = new MyDatabaseEntities()) { context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s); }
This will log the database operations in the output window. What it does is it writes the operations performed by EntityFramework to the output window. It gives awesome traces.
Have a look:
2) IDbCommandInterceptor
This uses the IDbCommandInterceptor Interface. This is in-built in Entity framework 6.
Note: This Interface is available only in Entityframework 6 and later.
Have a look at Interface:
namespace System.Data.Entity.Infrastructure.Interception { public interface IDbCommandInterceptor : IDbInterceptor { void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext); void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext); void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext); void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext); void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext); void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext); } }
Let’s derive this interface to the DatabaseLogger class.
FYI, I have added my log information into database. You may insert into file, Excel, anything you want.
You need not worry about the methods Interface itself is very self explanatory. It has 6 methods.
You can see that I have derived and check comments to understand each methods.
public class DatabaseLogger : IDbCommandInterceptor { static readonly ConcurrentDictionary<DbCommand, DateTime> MStartTime = new ConcurrentDictionary<DbCommand, DateTime>(); public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) { Log(command, interceptionContext); } public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) { OnStart(command); } public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { Log(command,interceptionContext); } public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { OnStart(command); } private static void Log<T>(DbCommand command, DbCommandInterceptionContext<T> interceptionContext) { DateTime startTime; TimeSpan duration; MStartTime.TryRemove(command, out startTime); if (startTime != default(DateTime)) { duration = DateTime.Now - startTime; } else duration = TimeSpan.Zero; const int requestId = -1; var parameters = new StringBuilder(); foreach (DbParameter param in command.Parameters) { parameters.AppendLine(param.ParameterName + " " + param.DbType + " = " + param.Value); } var message = interceptionContext.Exception == null ? $"Database call took {duration.TotalSeconds.ToString("N3")} sec. RequestId {requestId} rnCommand:rn{parameters + command.CommandText}" : $"EF Database call failed after {duration.TotalSeconds.ToString("N3")} sec. RequestId {requestId} rnCommand:rn{parameters.ToString() + command.CommandText}rnError:{interceptionContext.Exception} "; if (duration.TotalSeconds>1 || message.Contains("EF Database call failed after ")) { using (DbContext dbContext = new DbContext()) { Error error = new Error { TotalSeconds = (decimal)duration.TotalSeconds, Active = true, CommandType = Convert.ToString(command.CommandType), CreateDate = DateTime.Now, Exception = Convert.ToString(interceptionContext.Exception), FileName = "", InnerException = interceptionContext.Exception == null ? "" : Convert.ToString(interceptionContext.Exception.InnerException), Parameters = parameters.ToString(), Query = command.CommandText, RequestId = 0 }; dbContext.Errors.Add(error); dbContext.SaveChanges(); } } } public void ScalarExecuted (DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { Log(command, interceptionContext); } public void ScalarExecuting (DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { OnStart(command); } private static void OnStart(DbCommand command) { MStartTime.TryAdd(command, DateTime.Now); } }
Now, I am registering this class to dbcontext.
Explanation: It tells entity framework to use this class for Logging database operations.
public DbContext(): base("name=connectionstring") { DbInterception.Add(new DatabaseLogger()); }
Everything is set up now. You can make an Error model class like this. (This is the code-first model class.)
public class Error { [Key] [Required] public int ErrorId { get; set; } public string Query { get; set; } public string Parameters { get; set; } public string CommandType { get; set; } public decimal TotalSeconds { get; set; } public string Exception { get; set; } public string InnerException { get; set; } public int RequestId { get; set; } public string FileName { get; set; } public DateTime CreateDate { get; set; } public bool Active { get; set; } }
Have a look at Error logs here:
Technically, there are many approaches to log your Database operations. This is the easiest way I saw.
Credits
- Idea of using time intervals: http://stackoverflow.com/a/27365855/2630817
- Resharper for decompiling Interface: https://www.jetbrains.com/resharper/
- Have a look at https://msdn.microsoft.com/en-us/library/dd287191(v=vs.110).aspx for
ConcurrentDictionary
Bonus
I just used this so thought about sharing it here too 🙂
Let’s have a quick look at logging the time taken by each controller. Same table’s structure, same queries.
- Add a new class named
ExecutionTimeFilterlike I have added:public class ExecutionTimeFilter : ActionFilterAttribute { public override void OnActionExecuting(HttpActionContext actionContext) { base.OnActionExecuting(actionContext); actionContext.Request.Properties.Add("Time", Stopwatch.StartNew()); } public override void OnActionExecuted(HttpActionExecutedContext actionContext) { base.OnActionExecuted(actionContext); try { var stopwatch = (Stopwatch)actionContext.Request.Properties["Time"]; actionContext.Request.Properties.Remove("Time"); var elapsedTime = stopwatch.Elapsed; if (!(elapsedTime.TotalSeconds > 10)) return; using (DbContext dbContext = new DbContext()) { Error error = new Error { TotalSeconds = (decimal)elapsedTime.TotalSeconds, Active = true, CommandType = "Action Context", CreateDate = DateTime.Now, Exception = Convert.ToString(actionContext.Request), FileName = "", InnerException = actionContext.Response.ToString(), Parameters = "", Query = "", RequestId = 0 }; dbContext.Errors.Add(error); dbContext.SaveChanges(); } } catch { } } }
- Register it to the
configin my case I am using it in web API so inApp_Start/WebApiConfig.cs:public static void Register(HttpConfiguration config) { config.Filters.Add(new ExecutionTimeFilter()); }
Done! now, every action executed and took more than 10 seconds will log that entry to the database.
This member has not yet provided a Biography. Assume it’s interesting and varied, and probably something to do with programming.