Note - The red circled items in Model #1 are the fields I use to "map" to Model #2. Please ignore the red circles in Model #2: that is from another question I had which is now answered.

Note: I still need to put in an isDeleted check so I can soft delete it from DB1 if it has gone out of our client's inventory.

All I want to do, with this particular code, is connect a company in DB1 with a client in DB2, get their product list from DB2 and INSERT it in DB1 if it is not already there. First time through should be a full pull of inventory. Each time it is run there after nothing should happen unless new inventory came in on the feed over night.

So the big question - how to I solve the transaction error I am getting? Do I need to drop and recreate my context each time through the loops (does not make sense to me)?

Yeah, this caused me a headache too. I almost fell off my chair when I found the problem! I understand the technical reasons behind the problem, but this isn't intuitive and it isn't helping the developer to fall into the "pit of success" blogs.msdn.com/brada/archive/2003/10/02/50420.aspx
– Doctor JonesMay 21 '10 at 13:20

8

Isn't that bad for performance for large datasets? If you have a millions records in the table. ToList() will suck them all into memory. I'm running into this very problem and was wondering whether the following would be feasible a)Detach the entity b)Create a new ObjectContext and attach the detached entity to it. c)Call SaveChanges() on the new ObjectContext d)Detach the entity from the new ObjectContext e)Attach it back to the old ObjectContext
– Abhijeet PatelJun 4 '10 at 3:19

134

The issue is that you can't call SaveChanges while you're still pulling results from the DB. Therefore another solution is just to save changes once the loop has completed.
– Drew NoakesOct 3 '10 at 20:04

The queryable object you call this method on must be ordered. This is because Entity Framework only supports IQueryable<T>.Skip(int) on ordered queries, which makes sense when you consider that multiple queries for different ranges require the ordering to be stable. If the ordering isn't important to you, just order by primary key as that's likely to have a clustered index.

This version will query the database in batches of 100. Note that SaveChanges() is called for each entity.

If you want to improve your throughput dramatically, you should call SaveChanges() less frequently. Use code like this instead:

This results in 100 times fewer database update calls. Of course each of those calls takes longer to complete, but you still come out way ahead in the end. Your mileage may vary, but this was worlds faster for me.

And it gets around the exception you were seeing.

EDIT I revisited this question after running SQL Profiler and updated a few things to improve performance. For anyone who is interested, here is some sample SQL that shows what is created by the DB.

The first loop doesn't need to skip anything, so is simpler.

SELECT TOP (100) -- the chunk size
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
FROM [dbo].[Clients] AS [Extent1]
ORDER BY [Extent1].[Id] ASC

SELECT TOP (100) -- the chunk size
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
FROM (
SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], row_number()
OVER (ORDER BY [Extent1].[Id] ASC) AS [row_number]
FROM [dbo].[Clients] AS [Extent1]
) AS [Extent1]
WHERE [Extent1].[row_number] > 100 -- the number of rows to skip
ORDER BY [Extent1].[Id] ASC

Thanks. Your explanation was much more useful than the one marked as "Answered".
– Wagner da SilvaDec 2 '10 at 23:52

1

This is great. just one thing: If you're querying on a column and updating the value of that column, you need to be ware of the chunkNumber++; . Let's say you have a column "ModifiedDate" and you're querying .Where(x=> x.ModifiedDate != null), and at the end of foreach you set a value for ModifiedDate. In this way you're not iterating half of the records because half of the records are getting skipped.
– ArvandOct 8 '15 at 8:55

I think this should work. var skip = 0; const int take = 100; List<Employee> emps ; while ((emps = db.Employees.Skip(skip).Take(take).ToList()).Count > 0) { skip += take; foreach (var emp in emps) { // Do stuff here } } I would formulate this an answer but it would be buried below the piles of answers below and it relates to this question.
– jwizeMar 26 '17 at 1:11

This error is due to Entity Framework creating an implicit transaction during the SaveChanges() call. The best way to work around the error is to use a different pattern (i.e., not saving while in the midst of reading) or by explicitly declaring a transaction. Here are three possible solutions:

If you take the Transaction route, just throwing in a TransactionScope might not fix it - don't forget to extend the Timeout if what you're doing could take a long time - for example if you'll be interactively debugging the code making the DB call. Here's code extending the transaction timeout to an hour: using (var transaction = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(1, 0, 0)))
– Chris MoschiniJun 6 '12 at 23:18

I've bumped into this error the very first time I have digressed from the "tutorial path" into a real example on my own! For me, however, the simpler solution, SAVE AFTER ITERATION, the better! (I think 99% of the times this is the case, and only 1% really MUST perform a database save INSIDE the loop)
– spidermanDec 17 '12 at 15:45

Gross. I just bumped into this error. Very nasty. The 2nd suggestion worked like a charm for me along with moving my SaveChanges into the loop. I thought having save changes outside of the loop was better for batching changes. But alright. I guess not?! :(
– Mr. YoungJul 11 '13 at 16:08

Did not work for me .NET 4.5. When used the TransactionScope I got the following error "The underlying provider failed on EnlistTransaction.{"The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025)"}". I end up doing the job outside the iteration.
– Diganta KumarOct 18 '13 at 4:16

Invoking SaveChanges() method begins a transaction which automatically rolls back all changes persisted to the database if an exception occurs before iteration completes; otherwise the transaction commits. You might be tempted to apply the method after each entity update or deletion rather than after iteration completes, especially when you're updating or deleting massive numbers of entities.

If you try to invoke SaveChanges() before all data has been processed, you incur a "New transaction is not allowed because there are other threads running in the session" exception. The exception occurs because SQL Server doesn't permit starting a new transaction on a connection that has a SqlDataReader open, even with Multiple Active Record Sets (MARS) enabled by the connection string (EF's default connection string enables MARS)

A good way to avoid this is when you have a reader open to open a second one and put those operations in the second reader. This is something that you can need when you are updating master/details in the entity framework. You open the first connection for the master record and the second for the detail records. if you are only reading there should be no problems. the problems occur during updating.
– Herman Van Der BlomApr 6 '14 at 18:27

I was getting this same issue but in a different situation. I had a list of items in a list box. The user can click an item and select delete but I am using a stored proc to delete the item because there is a lot of logic involved in deleting the item. When I call the stored proc the delete works fine but any future call to SaveChanges will cause the error. My solution was to call the stored proc outside of EF and this worked fine. For some reason when I call the stored proc using the EF way of doing things it leaves something open.

Had similar issue recently: the reason in my case was SELECT statement in stored procedure that produced empty result set and if that result set was not read, SaveChanges threw that exception.
– n0rdMar 3 '14 at 17:43

Same thing with unread result from SP, thanks a lot for hint)
– Pavel KSep 16 '14 at 11:25

This is not good practice at all. You shouldn't execute SaveChanges that often if you don't need to, and you definitely shouldn't "Always use your selection as List"
– DinerdoMar 25 at 20:27

@Dinerdo it really depends on the scenario. In my case, I have 2 foreach loops. Outer one had the db query as the list. For instance, this foreach traverses hardware devices. Inner foreach retrieves several data from each device. As per requirement, I need to save to database the data after it is retrieved from each device one by one. It is not an option to save all data at the end of the process. I encountered the same error but the mzonerz's solution worked.
– jstuardoyesterday

Here are another 2 options that allow you to invoke SaveChanges() in a for each loop.

The first option is use one DBContext to generate your list objects to iterate through, and then create a 2nd DBContext to call SaveChanges() on. Here is an example:

//Get your IQueryable list of objects from your main DBContext(db)
IQueryable<Object> objects = db.Object.Where(whatever where clause you desire);
//Create a new DBContext outside of the foreach loop
using (DBContext dbMod = new DBContext())
{
//Loop through the IQueryable
foreach (Object object in objects)
{
//Get the same object you are operating on in the foreach loop from the new DBContext(dbMod) using the objects id
Object objectMod = dbMod.Object.Find(object.id);
//Make whatever changes you need on objectMod
objectMod.RightNow = DateTime.Now;
//Invoke SaveChanges() on the dbMod context
dbMod.SaveChanges()
}
}

The 2nd option is to get a list of database objects from the DBContext, but to select only the id's. And then iterate through the list of id's (presumably an int) and get the object corresponding to each int, and invoke SaveChanges() that way. The idea behind this method is grabbing a large list of integers, is a lot more efficient then getting a large list of db objects and calling .ToList() on the entire object. Here is an example of this method:

This is a great alternative that I thought of and did, but this needs to be upvoted. Note: i) you can iterate as enumerable which is good for very large sets; ii) You can use the NoTracking command to avoid problems with loading so many records (if that's your scenario); iii) I really like the primary-key only option as well - that's very smart because you're loading a lot less data into memory, but you're not dealing with Take/Skip on a potentially dynamic underlying dataset.
– ToddAug 30 '18 at 6:54

So in the project were I had this exact same issue the problem wasn't in the foreach or the .toList() it was actually in the AutoFac configuration we used.
This created some weird situations were the above error was thrown but also a bunch of other equivalent errors were thrown.

EF creates second copy of data which uses for change detection (so
that it can persist changes to the database). EF holds this second set
for the lifetime of the context and its this set thats running you out
of memory.

The recommendation is to renew your context each batch.

So I've retrieved Minimal and Maximum values of the primary key- the tables have primary keys as auto incremental integers.Then I retrieved from the database chunks of records by opening context for each chunk. After processing the chunk context closes and releases the memory. It insures that memory usage is not growing.

What does "close a reader" mean in Entity Framework? There is no visible reader in a query like var result = from customer in myDb.Customers where customer.Id == customerId select customer; return result.FirstOrDefault();
– AnthonySep 5 '11 at 16:17

@Anthony As other answers say, if you use EF to enumerate over a LINQ query (IQueryable), the underlying DataReader will remain open until the last row is iterated over. But although MARS is an important feature to enable in a connection-string, the problem in the OP is still not solved with MARS alone. The problem is trying to SaveChanges while an underlying DataReader is still open.
– ToddAug 30 '18 at 6:58

In my case, the problem appeared when I called Stored Procedure via EF and then later SaveChanges throw this exception. The problem was in calling the procedure, the enumerator was not disposed. I fixed the code following way:

If you get this error due to foreach and you really need to save one entity first inside loop and use generated identity further in loop, as was in my case, the easiest solution is to use another DBContext to insert entity which will return Id and use this Id in outer context

I am much late to the party but today I faced the same error and how I resolved was simple. My scenario was similar to this given code I was making DB transactions inside of nested for-each loops.

The problem is as a Single DB transaction takes a little bit time longer than for-each loop so once the earlier transaction is not complete then the new traction throws an exception, so the solution is to create a new object in the for-each loop where you are making a db transaction.