TransactionScope vs Transaction in LINQ to SQL

by

It should be noted that when using the TransactionScope there is no need for the try/catch construct you have. You simply have to call Complete on the scope in order to commit the transaction when the scope is exited.

That being said, TransactionScope is usually a better choice because it allows you to nest calls to other methods that might require a transaction without you having to pass the transaction state around.

When calling BeginTransaction on the DbConnection object, you have to pass that transaction object around if you want to perform other operations in the same transaction, but in a different method.

With TransactionScope, as long as the scope exists, it will handle everything that registers with the current Transaction on the thread, making your code cleaner, and more maintainable.

On top of that, you have the added benefit of being able to use other resources that can participate in transactions, not just the connection to the database.

It should be noted that in situations where you need to squeeze the most out of your connections and database operations, you might not want to use TransactionScope; even against a single database, you run the possibility of the Distributed Transaction Coordinator being used and having the transaction being turned into a distributed transaction (even for a single database connection).

In these cases, while muddying up your design, you might want to consider passing a connection-specific transaction around.

Or, if you know you will use one resource consistently (and on the same thread), you might want to create a class that reference-counts your connection/transaction.

You would create a class that on construction, creates your resource/increments the count. It would also implement IDisposable (in which you would decrement/release/commit/abort when the count is zero), and store the count in a variable that has ThreadStaticAttribute applied to it.

This allows you to separate the transaction management from the logic code, and still hold onto a singular resource fairly efficiently (instead of escalating to a distributed transaction).

More Related Contents:

  1. Returning IEnumerable vs. IQueryable
  2. Difference Between Select and SelectMany
  3. Return anonymous type results?
  4. LINQ – Left Join, Group By, and Count
  5. When should I dispose of a data context
  6. How to do a subquery in LINQ?
  7. LINQ to SQL and a running total on ordered results
  8. Understanding .AsEnumerable() in LINQ to SQL
  9. How to return anonymous type from c# method that uses LINQ to SQL [duplicate]
  10. LINQ: combining join and group by
  11. How to write Asynchronous LINQ query?
  12. Convert Linq Query Result to Dictionary
  13. How do you perform a CROSS JOIN with LINQ to SQL?
  14. LINQ Where Ignore Accentuation and Case
  15. Operation could destabilize the runtime?
  16. How to select only the records with the highest date in LINQ
  17. Get table-data from table-name in LINQ DataContext
  18. DISTINCT() and ORDERBY issue
  19. Linq query with nullable sum
  20. LINQ to SQL using GROUP BY and COUNT(DISTINCT)
  21. LINQ Expression to return Property value?
  22. How to make LINQ execute a (SQL) LIKE range search
  23. Will using LINQ to SQL help prevent SQL injection
  24. Get sum of two columns in one LINQ query
  25. Last and LastOrDefault not supported
  26. Linq “Could not translate expression… into SQL and could not treat it as a local expression.”
  27. Accessing System Databases/Tables using LINQ to SQL?
  28. Linq where column == (null reference) not the same as column == null
  29. If Else in LINQ
  30. C# Linq to SQL: How to express “CONVERT([…] AS INT)”?

Leave a Comment