Solution:
After much pulling out of hair I discovered that the foreach loops were the culprits. What requires to occur is to call EF however return it into an IList<T> of that target type then loop on the IList<T>.
Example:
IList<Client> clientList = from a in _dbFeed.Client.Include("Auto") select a;
foreach (RivWorks.Model.NegotiationAutos.Client client in clientList)
{
var companyFeedDetailList = from a in _dbRiv.AutoNegotiationDetails where a.ClientID == client.ClientID select a;
// ...
}
As you've meanwhile defined, you cannot save from within a foreach that is still drawing from the database through an active reader.
Calling ToList() or ToArray() is fine for small data sets, however at the time you have thousands of rows, you will be consuming a large amount of memory.
It's better to load the rows in chunks.
public static class EntityFrameworkUtil
{
public static IEnumerable<T> QueryInChunksOf<T>(this IQueryable<T> queryable, int chunkSize)
{
return queryable.QueryChunksOfSize(chunkSize).SelectMany(chunk => chunk);
}
public static IEnumerable<T[]> QueryChunksOfSize<T>(this IQueryable<T> queryable, int chunkSize)
{
int chunkNumber = 0;
while (true)
{
var query = (chunkNumber == 0)
? queryable
: queryable.Skip(chunkNumber * chunkSize);
var chunk = query.Take(chunkSize).ToArray();
if (chunk.Length == 0)
yield break;
yield return chunk;
chunkNumber++;
}
}
}
Given the above extension process, you can write your query like this:
foreach (var client in clientList.OrderBy(c => c.Id).QueryInChunksOf(100))
{
// do stuff
context.SaveChanges();
}
The queryable object you call this method on should be ordered. This is since Entity Framework just supports IQueryable<T>.Skip(int) on ordered queries, which creates sense at the time you consider that multiple queries for diverse ranges need the ordering to be stable. In case the ordering isn't important to you, only 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 named for each entity.
In case you want to improve your throughput dramatically, you must call SaveChanges() less frequently. Employ code like this instead:
foreach (var chunk in clientList.OrderBy(c => c.Id).QueryChunksOfSize(100))
{
foreach (var client in chunk)
{
// do stuff
}
context.SaveChanges();
}
This results in 100 times less database update calls. Naturally every of those calls takes longer to complete, however you still come out way ahead in the end. Your mileage may vary, however this was worlds faster for me.
And it gets around the exception you were viewing.
EDIT I revisited this question after running SQL Profiler and updated a some things to enhance performance. For anyone who is devoted, here is some sample SQL that displays what is made by the DB.
The first loop doesn't require to skip anything, hence is easier.
SELECT TOP (100) -- the chunk size
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
FROM [dbo].[Clients] AS [Extent1]
ORDER BY [Extent1].[Id] ASC
Subsequent calls require to skip previous chunks of results, so introduces usage of row_number:
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
We have present posted an official response to the bug opened on link. The workarounds we recommend are as pursues:
This error is due to Entity Framework making an implicit transaction at the time the SaveChanges() call. The best method to perform around the error is to use a different pattern (for example not saving while in the midst of reading) or by bluntly declaring a transaction. Here are three possible solutions:
// 1: Save after iteration (recommended approach in most cases)
using (var context = new MyContext())
{
foreach (var person in context.People)
{
// Change to person
}
context.SaveChanges();
}
// 2: Declare an explicit transaction
using (var transaction = new TransactionScope())
{
using (var context = new MyContext())
{
foreach (var person in context.People)
{
// Change to person
context.SaveChanges();
}
}
transaction.Complete();
}
// 3: Read rows ahead (Dangerous!)
using (var context = new MyContext())
{
var people = context.People.ToList(); // Note that this forces the database
// to evaluate the query immediately
// and could be very bad for large tables.
foreach (var person in people)
{
// Change to person
context.SaveChanges();
}
}
Actually you cannot save changes within a foreach loop in C# using Entity Framework.
context.SaveChanges() process acts like a commit on a regular database system (RDMS).
Only make all changes (which Entity Framework will cache) and then save all of them right away calling SaveChanges() after the loop (outside of it), like a database commit command.
