3

I just noticed that when a SqlException occurs while updating data using Entity Framework, all subsequent calls to SaveChanges will fail because the Entity that caused the exception is still in the "queue" to be saved.

This just stung us on a live system as many users were not able save their data due to some failed update with error 'string or binary data would be truncated'. We just had to resort to recycling the application pool.

How can I remove/reset the offending object and let other updates go through?

EDIT: Better yet, what is the best way of handling Sql Exeptions that occur during insert/update?

Ladislav Mrnka
  • 349,807
  • 56
  • 643
  • 654
Alfero Chingono
  • 2,663
  • 3
  • 32
  • 53

2 Answers2

4

First essential rule of EF - ObjectContext is unit of work and because of that you have to deal it with this way. If you share context among users you did so big mistake that you should immediately turn off the application and don't let your users use it unless you make a fix. I wrote the answer where I describing two patterns the context is implementing - unit of work and identity map. If you share the context among concurrent users you make the result of each operation undeterministic. You can't say if user has a fresh / commited data and you can't say if you saved everything the user was modifying in the single transaction.

Now the context uses transaction internally. Each SaveChanges saves every modified record (from all concurrent users if you share the context) in the transaction. Single error and whole transaction is rolled back. Once you start to use context per logical operation (request or action in the case of web application) you can show user the data and let him do modification or you can simply try it again (in case of dealing with locking issues). This of course doesn't solve problems where you do not validate user input because that is the bug in application which must be fixed. If you don't have a validation you can't show the user what is wrong with data.

Community
  • 1
  • 1
Ladislav Mrnka
  • 349,807
  • 56
  • 643
  • 654
  • So basically, create a new `ObjectContext` for every CRUD operation? – Alfero Chingono Apr 20 '11 at 19:45
  • If you use web application you can create single context for each request and you can make multiple CRUDs in that request if they are part of single logical transaction - you can also call SaveChanges multiple times so you can use multiple database transactions in single logical transaction. – Ladislav Mrnka Apr 20 '11 at 19:51
  • Wow! I was seriously mistaken... I read your answer and it makes everything clearer. Thank you! – Alfero Chingono Apr 20 '11 at 19:56
1

You can protect yourself from that situation by following the Unit of Work pattern with your context.

In that situation, each update from a different user would use its own context...so if an error was present in one it wouldn't affect the others.

Take a look at:

Using Repository and Unit of Work patterns with Entity Framework 4.0

The Unit of Work will also help you understand how to recover from an error.

Since a single Unit of Work is, in essence, a single transaction you can simply roll back the transaction and notify the user (and allow them to modify their data and try again).

Justin Niessner
  • 229,755
  • 35
  • 391
  • 521
  • Thanks Justin... I guess the question then becomes, "How do I roll back"? – Alfero Chingono Apr 20 '11 at 19:33
  • @deverop - If you're working with a single context, SaveChanges() will automatically rollback the transaction for you. If you're using multiple contexts in a single unit of work, you need to take a look at TransactionScope. – Justin Niessner Apr 20 '11 at 19:35
  • Thanks for your patience... I am using a single context (cuz I thought/read that it improves performance) and unfortunately it does roll back the transaction (in the db), but the entity is still in "pending" state so any other call to `SaveChanges` is blocked. – Alfero Chingono Apr 20 '11 at 19:42