I use Entity Framework to access my SQL data. I have some constraints in the database schema and I wonder how to handle exceptions that are caused by these constraints.
As example, I get the following exception in a case where two users try to add an (almost) identical entity to the DB concurrently.
System.Data.UpdateException
"An error occurred while updating the entries. See the InnerException for details."
(inner exception) System.Data.SqlClient.SqlException
"Violation of UNIQUE KEY constraint 'Unique_GiftId'. Cannot insert duplicate key in object 'dbo.Donations'.\r\nThe statement has been terminated."
How do I properly catch this specific exception?
Dirty solution:
catch (UpdateException ex)
{
SqlException innerException = ex.InnerException as SqlException;
if (innerException != null && innerException.Message.StartsWith("Violation of UNIQUE KEY constraint 'Unique_GiftId'"))
{
// handle exception here..
}
else
{
throw;
}
}
Now while this approach works, it has some downsides:
Do you know a better solution for this? Thanks for all feedback..
Note: I do not want to code the constraints manually within the application layer, I want to have them in the DB.
You should do validation on the UI first then handle specific errors related to Entity Framework. 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. An error occurred sending updates to the database.
To handle exception in Sql Server we have TRY.. CATCH blocks. We put T-SQL statements in TRY block and to handle exception we write code in CATCH block. If there is an error in code within TRY block then the control will automatically jump to the corresponding CATCH blocks.
Entity Framework Core allows you to drop down to raw SQL queries when working with a relational database. Raw SQL queries are useful if the query you want can't be expressed using LINQ. Raw SQL queries are also used if using a LINQ query is resulting in an inefficient SQL query.
You should be able to trap the SQL error number (which is SqlException.Number)
In this case it's 2627 which has been the same forever for SQL Server.
If you want abstraction, then you'll always have some dependency on the database engine because each one will throw different exception numbers and messages.
One way is to inspect the Errors property of the inner SqlException. The SqlError class has a Number property that identifies the exact error. See the master.dbo.sysmessages table for a list of all error codes.
Of course this still ties you to Sql Server. I'm not aware of a way to abstract this away other than roll your own 'EF exception analyzer'.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With