Problem
This is the error I’m getting right now:
while running this code:
public class ProductManager : IProductManager
{
#region Declare Models
private RivWorks.Model.Negotiation.RIV_Entities _dbRiv = RivWorks.Model.Stores.RivEntities(AppSettings.RivWorkEntities_connString);
private RivWorks.Model.NegotiationAutos.RivFeedsEntities _dbFeed = RivWorks.Model.Stores.FeedEntities(AppSettings.FeedAutosEntities_connString);
#endregion
public IProduct GetProductById(Guid productId)
{
// Do a quick sync of the feeds...
SyncFeeds();
...
// get a product...
...
return product;
}
private void SyncFeeds()
{
bool found = false;
string feedSource = "AUTO";
switch (feedSource) // companyFeedDetail.FeedSourceTable.ToUpper())
{
case "AUTO":
var 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;
foreach (RivWorks.Model.Negotiation.AutoNegotiationDetails companyFeedDetail in companyFeedDetailList)
{
if (companyFeedDetail.FeedSourceTable.ToUpper() == "AUTO")
{
var company = (from a in _dbRiv.Company.Include("Product") where a.CompanyId == companyFeedDetail.CompanyId select a).First();
foreach (RivWorks.Model.NegotiationAutos.Auto sourceProduct in client.Auto)
{
foreach (RivWorks.Model.Negotiation.Product targetProduct in company.Product)
{
if (targetProduct.alternateProductID == sourceProduct.AutoID)
{
found = true;
break;
}
}
if (!found)
{
var newProduct = new RivWorks.Model.Negotiation.Product();
newProduct.alternateProductID = sourceProduct.AutoID;
newProduct.isFromFeed = true;
newProduct.isDeleted = false;
newProduct.SKU = sourceProduct.StockNumber;
company.Product.Add(newProduct);
}
}
_dbRiv.SaveChanges(); // ### THIS BREAKS ### //
}
}
}
break;
}
}
}
Model #1 – On our Dev Server, this model is stored in a database. Model #1 http://content.screencast.com/users/Keith.Barrows/folders/Jing/media/bdb2b000-6e60-4af0-a7a1-2bb6b05d8bc1/Model1.png Model #2 http://content.screencast.com/users/Keith.Barrows/folders/Jing/media/bdb2b000-6e60-4af0-a7a1-2bb6
Model #2 – This model is stored in a database on our Prod Server and is automatically updated every day. http://content.screencast.com/users/Keith.Barrows/folders/Jing/media/4260259f-bce6-43d5-9d2a-017bd9a980d4/Model2.png alt text http://content.screencast.com/users/Keith.Barrows/folders/Jing/media/4260259f-bce6-43d5-9d2a-017bd9a980
Note: In Model #1, the red circled items are the fields I utilize to “map” to Model #2. Please disregard the red circles in Model #2; these are from a previous query that has since been answered.
Note: I still need to add an isDeleted check so that I may soft delete it from DB1 if our client’s inventory has changed.
With this code, all I want to do is link a firm in DB1 to a client in DB2, get their product list from DB2, then INSERT it into DB1 if it isn’t already there. A comprehensive inventory pull should be done the first time through. If new merchandise came in on the stream over night, nothing should happen the next time it’s run.
Asked by Keith Barrows
Solution #1
After much hair pulling, I determined that the perpetrators were the foreach loops. Call EF but return it as an IListT> of that target type, then loop on the IListT>.
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;
// ...
}
Answered by Keith Barrows
Solution #2
You can’t save from within a foreach that is currently drawing from the database via an active reader, as you’ve already discovered.
For modest data sets, calling ToList() or ToArray() is OK, but when you have thousands of rows, you’ll be using a lot of RAM.
It’s preferable to load the rows in batches.
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++;
}
}
}
You can write your query like this using the above extension methods:
foreach (var client in clientList.OrderBy(c => c.Id).QueryInChunksOf(100))
{
// do stuff
context.SaveChanges();
}
This method must be called on an ordered queryable object. Because Entity Framework only supports IQueryableT>, this is the case. 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.
The database will be queried in batches of 100 in this version. It’s worth noting that SaveChanges() is called for each entity separately.
SaveChanges() should be called less frequently if you wish to drastically increase your throughput. Instead, use code like this:
foreach (var chunk in clientList.OrderBy(c => c.Id).QueryChunksOfSize(100))
{
foreach (var client in chunk)
{
// do stuff
}
context.SaveChanges();
}
There are 100 times fewer database update calls as a result of his work. Of course, each of those calls takes longer to complete, but you still end up with a significant advantage. Your mileage may vary, but I found this to be much faster.
It also avoids the exception you were encountering.
EDIT After running SQL Profiler, I went back to this question and tweaked a few things to increase performance. For those who are curious, here is some sample SQL that demonstrates what the database generates.
The first loop is simpler because it does not need to skip anything.
SELECT TOP (100) -- the chunk size
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
FROM [dbo].[Clients] AS [Extent1]
ORDER BY [Extent1].[Id] ASC
Because subsequent calls must skip prior portions of results, row number: is introduced.
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
Answered by Drew Noakes
Solution #3
We’ve now published an official answer to the Connect problem. The following are the workarounds we recommend:
Entity Framework creates an implicit transaction during the SaveChanges() call, which causes this problem. The simplest method to avoid the problem is to adopt a different pattern (for example, not saving while reading) or to declare a transaction explicitly. Here are three options for you to consider:
// 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();
}
}
Answered by Mark Stafford – MSFT
Solution #4
In C#, you can’t use Entity Framework to store changes inside a foreach loop.
The context.SaveChanges() method behaves similarly to a commit in a traditional database system (RDMS).
Simply make all of the changes (which Entity Framework will cache) and then call SaveChanges() after the loop (outside of it) to save them all at once, similar to a database commit command.
This works if you can save all of your modifications at the same time.
Answered by Edgardo Pichardo C.
Solution #5
Simply put, context is everything. After you’ve finished your foreach loop, call SaveChanges() (loop).
Answered by Majid
Post is based on https://stackoverflow.com/questions/2113498/sqlexception-from-entity-framework-new-transaction-is-not-allowed-because-ther