• Register
0 votes
446 views

Problem :

I want to save on the DB the log of a multi threaded processor but I am facing the following error:

new transaction is not allowed because there are other threads running in the session.

In each tread I have following function :

internal bool WriteMyTrace(IResult myresult, string mymessage, byte mytype)
{
SPC_SENDING_TRACE mytrace = new SPC_SENDING_TRACE(mymessage,Parent.currentLine.CD_LINE,mytype,Parent.currentUser.FULLNAME,Parent.guid);
Context.SPC_SENDING_TRACE.AddObject(mytrace);
if (Context.SaveChanges(myresult) == false)
            return false;
        return true;
}

Here Context is different for each of the threads, but there connection with my DB is always in the same way.

Anyone have solution on my problem?

6 5 3
7,540 points

2 Answers

0 votes

Solution :

I guess you must create the context for each of the transaction and then just dispose it, you can do that in following way:

using(var myctx = new MyContext()) {
    //do your transaction here
}

After the closed bracket your context is disposed.

You should also try to add the .ToList() to the every LINQ query that you have. When you try to iterate over your LINQ result, you must not make any changes until the iteration is finished. Also check if you have something similar to that. Hope my solution helped you this time in resolving your issues.

 

9 7 4
38,600 points
0 votes

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.

10 6 4
31,120 points

Related questions

0 votes
1 answer 65 views
65 views
Problem : My Query is giving the strange error as follows there is already an open datareader associated with this command which must be closed first.
asked Nov 15, 2019 peterlaw 6.9k points
0 votes
1 answer 81 views
81 views
Problem : I am facing error on below step objUDMCountryStandards.Country = txtMySearchCountry.Text.Trim() != string.Empty ? txtMySearchCountry.Text : null; in the get grid data throws error as below : &ldquo;The calling thread cannot access this object because a different thread owns it.&rdquo; Is there anything wrong with my code?
asked Jan 7 alecxe 7.5k points
1 vote
1 answer 32 views
32 views
Problem : In the following step objUDMCountryStandards.Country = txtSearchCountry.Text.Trim() != string.Empty ? txtSearchCountry.Text : null; in get grid data throws the exception as below : The calling thread cannot access this object because a different thread owns it. What is wrong in my code?
asked Dec 28, 2019 alecxe 7.5k points
0 votes
1 answer 19 views
0 votes
1 answer 28 views
28 views
Problem: I am na&iuml;ve to C# programming. I am trying to use the auto-implemented properties. I think the best ever way to fix below error is to declare my own backing variable? Please find below my C# code: public Point Origin { get; set; } ... unchanged. To resolve this error, store the result of the expression in an intermediate value, or use a reference type for the intermediate expression.
asked Jul 23 Raphael Pacheco 4.9k points