实体框架：如何正确处理由于SQL约束而发生的异常
我使用实体框架来访问我的 SQL 数据。我在数据库模式中有一些约束，我想知道如何处理由这些约束引起的异常。
例如，在两个用户尝试同时向数据库添加（几乎）相同的实体的情况下，我收到以下异常。
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."
我如何正确捕获这个特定的异常？
肮脏的解决方案：
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;
}
}
虽然这种方法有效，但它有一些缺点：
- 没有类型安全：代码取决于包含唯一列名称的异常消息。
- 对 SqlCLient 类的依赖（破坏的抽象）
您知道更好的解决方案吗？ 感谢您的所有反馈。
注意：我不想在应用程序层中手动编码约束，我希望将它们放在数据库中。
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:
- No type safety: The code depends on the exception message which contains the name of the unique column.
- Dependency on the SqlCLient classes (broken abstraction)
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您应该能够捕获 SQL 错误号（即 SqlException.Number）
在本例中，它是 2627，对于 SQL Server 来说永远是相同的。
如果您想要抽象，那么您将始终对数据库引擎有一定的依赖性，因为每个引擎都会抛出不同的异常号和消息。
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.
一种方法是检查 Errors 属性。 SqlError 类具有 Number 属性 确定确切的错误。有关所有错误代码的列表，请参阅 master.dbo.sysmessages 表。
当然，这仍然将您与 Sql Server 联系在一起。除了推出您自己的“EF 异常分析器”之外，我不知道有什么方法可以将其抽象出来。
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'.
这种情况不应该发生，因为在使用 EF 时不应该显式分配键；而是让上下文分配一个合适的。如果是并发问题，那么您应该在事务范围内进行更新。
然后，如果出现 UpdateException，您可以再次重试更新。您可以在事务范围内安全地执行此操作，并且仅在更新彻底时才完成范围。在这种情况下，下一次更新的机会比第一次更大。
This scenario should not happen as the key should never be assign explicitly when using EF; rather allowing the context to assign an appropriate one. If its a concurrency issue then you should do the update in a transaction scope.
Then if you have an UpdateException you could retry the update again. You can safely do that in a transaction scope and only complete the scope when the update goes thorough. In this scenario the chances of the update going through the next time is greater than the first one.