Oracle - EF - too many local sessions in Global Transaction

[rjonna]

I have to insert 2 records in to one db and around 40 records in to another db in a single transaction. I am using TransactionScope and getting this error.

I did read this article but that does not help because there are 2 db's are involved and it is a Distributed Transaction.

http://www.devart.com/blogs/dotconnect/ ... art-3.html

My code is somewhat like below.

Code: Select all

using (var ts  = new TransactionScope())
{
    using (var context = new MyContext())
    {
        for (int i = 0; i  a.name = 'xxx').Single();
           context.ArchiveOrders.Add(order);
         }
    }
}

Even though I am creating the "Context" outside my loop(unlike the example in the link above), the code still fails with the "too many local sessions" error.

Also, I noticed that every query "context.Orders.Where(...)" is enlisted as a new Transaction. I am using Entity Framework and not Linq to Sql. So the above code breaks at 20 iterations.

Can anyone help with this? What are my options now?

[AndreyR]

This is default EF behaviour. Each query/SaveChanges call opens a new connection, and this connection is enlisted into an opened distributed transaction as a branch.
Here is a simple solution:

Code: Select all

using (var ts  = new TransactionScope()) 
{ 
    using (var context = new MyContext()) 
    { 
        context.Connection.Open();
        for (int i = 0; i  a.name = 'xxx').Single(); 
           context.ArchiveOrders.Add(order); 
         } 
    } 
}

The difference is the explicit opening of the connection. After this call all queries will be executed using this connection.
Hope this helps.

[rjonna]

Thanks for the reply. What would be the ramifications of opening the connection in the beginning? If there are none, why is it not the default way? i.e. why does EF open a new connection for each request?

[AndreyR]

This is the default EF behaviour. Anyway, there are no side effects in this case.