7

I've been reading a document from Microsoft's patterns & practices group (Data Access for Highly-Scalable Solutions: Using SQL, NoSQL, and Polyglot Persistence).

In Chapter 3, in the section "Retrieving Data from the SQL Server Database", the authors discuss using Entity Framework to load entities from a database. Here's a bit of their sample code:

using (var context = new PersonContext()) { Person person = null; using (var transactionScope = this.GetTransactionScope()) { person = context.Persons .Include(p => p.Addresses) .Include(p => p.CreditCards) .Include(p => p.EmailAddresses) .Include(p => p.Password) .SingleOrDefault(p => p.BusinessEntityId == personId); transactionScope.Complete(); } // etc... }

Note the use of a custom transaction scope via the GetTransactionScope method, implemented in their base context class like so:

public abstract class BaseRepository { private static TransactionOptions transactionOptions = new TransactionOptions() { IsolationLevel = IsolationLevel.ReadCommitted }; protected virtual TransactionScope GetTransactionScope() { return new TransactionScope(TransactionScopeOption.Required, transactionOptions); } }

Working with Transactions (EF6 Onwards) on MSDN states:

In all versions of Entity Framework, whenever you execute SaveChanges() to insert, update or delete on the database the framework will wrap that operation in a transaction [...] the isolation level of the transaction is whatever isolation level the database provider considers its default setting. By default, for instance, on SQL Server this is READ COMMITTED. Entity Framework does not wrap queries in a transaction. This default functionality is suitable for a lot of users

The bold emphasis is mine.

My question: is the use of a TransactionScope as shown above overkill, particularly for data reads, when using Entity Framework?

(I realised after posting that the answer to this question might be somewhat opinion-based, sorry about that.)

Improve this question
3
  • With this isolationLevel, this code is overkill if the transaction contains only read operation. But if the isolationLevel is ReadUncommited, this code will allow you to read dirty row (not commited yet). Commented Mar 5, 2015 at 16:19
  • 2
    You should avoid transaction scope's if it doesn't serve a business purpose. this to avoid deadlocks and performance problems. Commented Mar 16, 2015 at 13:37
  • Transaction Scope doesnt create deadlocks. Inconsistent table access creates deadlocks. Commented Feb 14, 2024 at 20:40

5 Answers 5

Reset to default
6
+50

The question is a little open ended. But may prove useful for people learning about dirty reads. Isolation and EF. And you have read EF Transaction Scope ef6 and we have a clear question.

Generally i would say let EF manage the scope. Beginners dont consider uncommitted reads and it is a good default for EF.

You may have a valid reason to want to control scope. Or need to use an existing transaction. But remains for specialist use.

So now the real question.
Is it good practice to include such defensive programming around isolation.

My view:
Only if it doesnt make the code harder to maintain, and harder to reuse.

Oracle and SQL server have default Read Committed. I would expect so on other DBs.I would therefore conclude, most likely unecessary protection that adds complexity.
I wouldnt add it to my code.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

EF does not say that it has default transaction scope outside of SaveChanges, does it create TransactionScope when you create context?
2

It depends. If you want dirty reads, serializable reads,etc anything other than default isolation level, then you need to wrap you queries inside a transaction scope.

Improve this answer

Comments

2

To maintain data integrity, it is sometimes useful to use an explicitly defined multi-statement transaction so that if any part of the transaction fails, it all gets rolled back. The decision on whether or not to use a transaction should not be entered into lightly. Explicit transactions, especially distributed ones, come at a major cost to performance and open the system up to deadlocks and to the possibility that a transaction could be left open, locking the affected table and blocking all other transactions. In addition, the code is far more difficult to develop and maintain.

In my experience, I have found that using a transaction scope in C# code is more complicated than using an explicit transaction in SQL. So for operations that would benefit from a transaction, and really any sufficiently complicated query, I would recommend creating a stored procedure to be called from the ORM.

Improve this answer

Comments

2

Have you read the following link ? https://msdn.microsoft.com/en-us/library/vstudio/bb738523%28v=vs.100%29.aspx

If you were to use the normal behavior of saveChanges without it begin part of a transactionscope you would not be able to extend the transaction beyond the boundaries of the database. Using the transaction scope like in the reference above makes the message queue part of the transaction. So if for some reason the sending of the message fails, the transaction fails too.

Of course you might consider this a more complex scenario. In simple applications that don't require such complex logic, you are probably safer to use the plain saveChanges api.

Improve this answer

Comments

1

In the example code given that only retrieves data from one table/entity, using TransactionScope is unnecessary because there is nothing being changed. In general SQL databases will both get and change data using queries, but in this context a "query" is just a get operation, and as you emboldened in the question, EF does not transact queries.

If the code were retrieving data from one table/entity and using that information to update the same or another table/entity, that is where a transaction is generally going to be needed to maintain data integrity.

This second scenario is where EF generally has a problem. You can issue a query and make changes to the entities and call SaveChanges(), but a SQL trace will show that each call to sp_executesql is running under a different transaction ID. This is easy enough to fix by wrapping that code in a TransactionScope.

I would have thought this would have been better addressed by now, but I helped a team member today that was running into data issues when using the second scenario and found EF still behaving the same way in .net6 as it did way back in .net framework 4.x.

Improve this answer

6 Comments

I don't see how this could be "better addressed". How should EF guess that two SaveChanges calls should be in one transaction?
@GertArnold - Sorry, not following where you got "two SaveChanges()" from.
Then what exactly is "this" in "I would have thought this would have been better addressed by now"?
The two paragraphs that precede that statement describe what "this" is.
That doesn't make a whole lot of a difference. How would EF know that you have a db interaction that should start or finish a transaction? Users have ample tools to start and end transactions themselves that EF enlists in. I can imagine that they don't feel any urge to offer any features in that area.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.