What is the difference between Entity Framework (6) transactions with one or more SaveChanges () calls

Question

I want to know what are the practical differences in performing a transaction in the same database context between these three ways:

1) Multiple operations with one single SaveChanges() without explicit use of sql transaction

 using (TestDbContext db = new TestDbContext()) { // first operation // second operation db.SaveChanges(); } 

2) Multiple operations with one single SaveChanges() using a sql transaction

 using (TestDbContext db = new TestDbContext()) using (DbContextTransaction trans = db.Database.BeginTransaction()) { // operation 1 // operation 2 db.SaveChanges(); trans.commit(); } 

3) Multiple operations with multiple SaveChanges() using sql transaction

 using (TestDbContext db = new TestDbContext()) using (DbContextTransaction trans = db.BeginTransaction()) { // operation 1 db.SaveChanges(); // operation 2 db.SaveChanges(); trans.commit(); } 

In (2) and (3), if commit() should actually execute the requested sql queries to the database, is it really different, say, to save changes for each operation or to save changes for the whole operation at once?

And if (1) also allows you to safely perform multiple operations in the same database context, what is the main use of manually starting a transaction? I would say that we can manually provide a try / catch block to roll back the transaction if something bad happens, but AFAIK, SaveChanges () also automatically covers it, at least with SQLServer.

** UPDATED: Another thing: Should I use the db context and transaction variables at the class level, or should they be local only for the contained methods?

Answer 1

If you do not start a transaction, it is implicit. Value, all SaveChanges() that you execute will be available in the database right after the call.

If you start a transaction, SaveChanges() is still updating, but the data is not available to other connections until commit is called.

You can verify this yourself by setting breakpoints, creating new objects, adding them to the context, and doing SaveChanges() . You will see that the ID property will have a value after this call, but there will be no corresponding row in the database until you commit the transaction.

As for your second question, it really depends on the needs of concurrency, what your class is doing, and the amount of data you work with. This is not a lot of problems, because the problem is with the execution of the code.

Contexts are not thread safe, so as long as you have only one thread in your application, access to the context, you can make it in a wider area. But then, if other instances of the application access the data, you will need to make sure that you update the data to the latest model. You should also consider that the more models you load into memory, the slower they are saved over time.

I try to create my contexts as close as possible to the operations that should be performed whenever possible, and delete them shortly after.

Answer 2

Your question does not seem to relate at all to entity structure, and more about sql transactions. The sql operation is one atomic change. In other words, either all changes are committed, or no one is committed.

You really don't have an example that describes the script, but if you added another example, for example:

 using (TestDbContext db = new TestDbContext()) { // operation 1 db.SaveChanges(); // operation 2 db.SaveChanges(); } 

... in this example, if your first operation was successfully saved, but the second operation failed, you could have a situation where the data captured in the first step is potentially invalid.

This is why you should use the sql transaction to wrap both SaveChanges in one operation, which means that either all the data or none is committed.