0

I am using C# and EF to work with a SQL Server database.

I need to do several queries at a time. For now I'm doing all the operations together and in the end I'm calling the DbContext.SaveChanges function to apply all the changes at once.

My problem is that if one of the queries has an error, all the queries are cancelled.

Do I need to call the DbContext.SaveChanges for every query for this?

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
amichai
  • 718
  • 9
  • 19
  • 2
    Maybe it is better to use transaction? When error occurs everything in transaction is rolled back and you won't have corrupted data? – Roman Jun 06 '17 at 08:53
  • https://stackoverflow.com/questions/815586/using-transactions-or-savechangesfalse-and-acceptallchanges Check this link – Murat Can OĞUZHAN Jun 06 '17 at 08:55
  • Just as a note, in most cases, this is a good thing. You need to consider carefully the implications of having your data in an unexpected state. – Paddy Jun 06 '17 at 09:01
  • *It depends:* does it make **sense** (from a business / requirements perspective) if 3 out of 5 queries succeed and their updates are persisted? Or are those queries together a **single operation** that either works fully - or fails if any one step of it fails? – marc_s Jun 06 '17 at 09:01

3 Answers3

2

DBcontext works as UnitOfWork. It keeps track of all the changes in current DBContext and when you call SaveChanges it figures out what exactly should be done while writing those to database. In other words, this is similar to Database Transaction.

Do I need to call the DbContext.SaveChanges for every query for this?

It depends. You need to identify your UnitOfWork. In web applications, it is generally "DBConstext per Request". It may not be exactly for you. Identify group of DB actions those should be done in one group OR should fail as a group. Then call SaveChanges accordingly.

Calling SaveChanges for each DB call may not be efficient. You are ignoring other important features of ORM those will not be helpful in this case. Take a time to identify your UnitOfWork.

Amit Joshi
  • 15,448
  • 21
  • 77
  • 141
  • I'm getting a json contain array of objects need to be inserted to db. isn't it inefficient to access the db for every query? – amichai Jun 06 '17 at 09:06
  • IMO, all items in array should be inserted in DB in single group. If there are errors doing this, validate the data in advance. – Amit Joshi Jun 06 '17 at 09:08
  • To validate the data i need to access the db to check existing data, but the errors are very rare and i prefer to lose the defected objects. – amichai Jun 06 '17 at 09:14
  • Well then go on and call `SaveChanges` per array item. Just remember, you are loosing some good features from ORM here. – Amit Joshi Jun 06 '17 at 09:16
  • Thanks, I hope its not inefficient – amichai Jun 06 '17 at 09:18
1

if you don't need transaction then you need to call the DbContext.SaveChanges for every query.

jalil
  • 85
  • 10
0

Yes you can call the DBcontext.SaveChanges after every query to run the query successfully. If your one of the query having error so the remain work will be saved and please try to put your error query at the end of your execution if there are no dependency.

Aniket Sharma
  • 1,000
  • 10
  • 16