Transaction exception while using ADO.NET entities

in .NET Development, Uncategorized

The following ADO.NET code will throw following exception “New transaction is not allowed because there are other threads running in the session.”:


var customer = ... ;
using (var entities = new ApplicationEntities())
{
var myQuery = from o in entities.Orders
where
o.Customers.ID == 3
select o;

foreach (var order in orders)
{
var newOrder = (Orders)order.Clone();
newOrder.Customers = customer;

entities.AddToOrders(newOrder);
entities.SaveChanges();

}
}

The exception occurs because the foreach loop opens an transaction to query the order entities. The transaction stays open until the end of the loop is reached. To avoid this exception, just use the saveChanges()-method outside the foreach loop, see example below:


var customer = ... ;
using (var entities = new ApplicationEntities())
{
var myQuery = from o in entities.Orders
where
o.Customers.ID == 3
select o;

foreach (var order in orders)
{
var newOrder = (Orders)order.Clone();
newOrder.Customers = customer;

entities.AddToOrders(newOrder);

}

//This is the right place to call the saveChanges method

entities.SaveChanges();

}

Another solution would be, to store the query result in an object, for example a list or an array and iterate trough the array instead of the IQueryable list.

0 Comments

Leave a Reply

Using Gravatars in the comments - get your own and be recognized!

XHTML: These are some of the tags you can use: <a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>