1

I want to find a way to control EF's underlying db connection & transaction to make sure my application is using only one connection at a time during one transaction (I will need to support both Oracle and SQL Server).

I found this good article which comes with a lot of recommendations, but brings up (like all the other articles I have read) the TransactionScope. Well, I would like to stay away of TransactionScope, if possible...

Could I have a solution for this playing with only pure DbConnection & DbTransaction or this is simply impossible or wrong?

Even more, I found this article here, stating at section :

"Specifying Your Own Transaction"

Just as you can override the default behavior with connections, you can also control transaction functionality. If you explicitly create your own transaction, SaveChanges will not create a DbTransaction. You won't create a System.Common.DbTransaction, though. Instead, when creating your own transactions, you need to use a System.Transaction.TransactionScope object.

But there is no explaination...

I am using Entity Framework 5.0. Could you please help me understand in order to choose correct for my application? It would be ideal to show me some good patterns of usage.

Thanks in advance!

Note: I am planning this because of the transactions escalating to DTC for Oracle Data Provider.

Learner
  • 3,297
  • 4
  • 37
  • 62

2 Answers2

2

Entity Framework 6 has two features which may help with this:

If you did want to use EF5, you'd need to use a TransactionScope:

var context = new MyContext();

using (var transaction = new TransactionScope())
{
    MyItem item = new MyItem();
    context.Items.Add(item);
    context.SaveChanges();

    item.Name = "Edited name";
    context.SaveChanges();

    transaction.Complete();
}

As mentioned in the linked article, you will need to reference System.Transactions to get TransactionScope.

Richard
  • 29,854
  • 11
  • 77
  • 120
  • Yes, thank you... I know the solution with TransactionScope, but as said I'd prefer to avoid it, if possible. I am more interested if I could use the ADO.Net DbConnection and DbTransaction here. If not possible or wrong, then why? – Learner Aug 30 '13 at 10:01
  • 1
    In that case I would suggest looking at Entity Framework 6, which has support for explicit transactions. It's not released yet, but it's at Release Candidate stage so should be reasonably stable. – Richard Aug 30 '13 at 10:02
  • EF6 also allows you to create a `DbContext` using an existing `DbConnection`, which also sounds like it might be what you need. – Richard Aug 30 '13 at 10:05
  • Thanks a lot Richard... Please edit your answer adding this useful link: https://entityframework.codeplex.com/wikipage?title=Improved%20Transaction%20Support so that I could accept your answer... – Learner Aug 30 '13 at 10:21
0

Entity Framework maintains its own transaction which is sufficient. However it gives you flexibility of committing or discarding the changes in transaction. If you do not call SaveChanges() method then it will discard the transaction. Also if you are using the same DbContext for many transactions, then it would be using same connection. If you use two or more DbContext at the same time, then it will use separate connections which is ideal situation. Here is a very important point I would like to make is that it would be a waste of Entity Framework technology to implement own transaction. If you want to do so, then I would suggest to use your own DB implementation in traditional way.

Adarsh Kumar
  • 1,134
  • 7
  • 21
  • I am planning this because of the transactions escalating to DTC. Please see: http://stackoverflow.com/questions/18489291/ef-and-transactionscope-for-both-sql-server-and-oracle-without-escalating-spanni & https://forums.oracle.com/thread/2356912 – Learner Aug 30 '13 at 09:36
  • 1
    EF's transactions are NOT always sufficient. For example, you have no control over Insertion order in EF, and there are certain relationship types which EF just can't handle inserting in one ago. In these situations, multiple `.SaveChanges()` calls are needed, and an explicit transaction is good to prevent any possibility of a partial insert. – Richard Aug 30 '13 at 10:00