.Net Core Async Saving

Question

I'm trying to save all the KPI values that have been passed from View, but I ran into a problem, where I used foreach loop and this operation took way too long to process, so I loooked for solution and found Parallel.ForEach, but it throws me this error:

An attempt was made to use the context while it is being configured. A DbContext instance cannot be used inside 'OnConfiguring' since it is still being configured at this point. This can happen if a second operation is started on this context before a previous operation completed.

Code:

 [HttpPost]
    [ValidateAntiForgeryToken]
    public async Task<IActionResult> Create([Bind("Date,Name,Value,ID,CriteriaID,Employee")] List<KPI> KPI, List<string> employeeGIDs)
    {
        if (KPI.Count == 0)
            return Create();

        int managerID = HttpContext.Session.GetInt32("ID").Value;

        var criterias = _context.Criterias.AsEnumerable().Where(x => KPI.Any(k => k.CriteriaID == x.ID));
        var users = _context.Users.AsEnumerable().Where(x => employeeGIDs.Contains(x.GID));
        var managerKPIs = _context.KPIs.AsEnumerable().Where(x => x.ManagersId == managerID);

        List<KPI> kpisToAdd = new List<KPI>(KPI);

        if (ModelState.IsValid)
        {
            ParallelLoopResult parallelLoopResult = Parallel.ForEach(kpisToAdd, kpi => 
            {
                kpi.ManagersId = managerID;
                kpi.Criteria = criterias.FirstOrDefault(x => x.ID == kpi.CriteriaID);
                var user = users.FirstOrDefault(x => employeeGIDs.Contains(x.GID));

                if (kpi.Criteria.IsIndividual)
                {
                    kpi.EmployeeUserCode = user.UserCode;
                    kpi.EmployeeFullName = user.FullName;
                }
            });

            if (parallelLoopResult.IsCompleted)
            {
                var criteriasToAdd = kpisToAdd
                .Where(x => !managerKPIs
                .Any(m =>
                m.Date.Month.Equals(x.Date.Date.Month) &&
                m.EmployeeUserCode.Equals(x.EmployeeUserCode) &&
                m.Criteria.Name.Equals(x.Criteria.Name)));

                _context.AddRange(criteriasToAdd);
                await _context.SaveChangesAsync();
            }
        }

        return Create();
    }

I tried wrapping it into Task, but it doesn't work. Are there any alternatives?

If you want more than one connection to database use a connection pool : https://learn.microsoft.com/en-us/ef/core/performance/advanced-performance-topics?force_isolation=true&tabs=with-di%2Cexpression-api-with-constant — jdweng, Mar 22 '23 at 11:23
If you're using EF you can't. See this: https://stackoverflow.com/questions/12827599/parallel-doesnt-work-with-entity-framework — GH DevOps, Mar 22 '23 at 11:23
Due to `AsEnumerable()` doesn't iterate your DbSet, you are using your DbContext concurrently - weird error mentioned is a side effect.. EF doesn't support its DbContext instances to work concurrently. Btw, why to create new list, if you got one through model binding? — Ryan, Mar 22 '23 at 11:28

score 1 · Answer 1 · answered Mar 24 '23 at 01:43

This can happen if a second operation is started on this context before a previous operation completed.

Yes. You can't use a single DbContext from within Parallel code.

I used foreach loop and this operation took way too long to process, so I loooked for solution and found Parallel.ForEach

Well, that's one solution, I guess. You would need one DbContext per parallel thread, though. So instead of using one database connection to do N operations, you could use M database connections to do N operations.

That's not a great solution, though, because all those updates are still hitting the same database - probably the same table and closely related rows.

A better solution would be to do some bulk loading into your database. Then you have one database connection and one (or just a handful) of operations to load all the data at once.

score 0 · Answer 2 · answered Mar 24 '23 at 02:01

Lets strip your example down to this;

_context.Criterias
    .AsEnumerable()
    .Where(x => something(x))
    .FirstOrDefault(x => somethingElse(x));

_context.Criterias is an IQueryable (and a DbSet but that's not relevant).

When you start enumerating an IQueryable, Entity Framework will start executing a database query and materialising the result set into dotnet objects.

When you call .AsEnumerable() you are preventing Entity Framework from building a more efficient query. In your case, you are forcing Entity Framework to query the entire table, loading every record into memory for client side evaluation, every time you enumerate the results.

But it's only when you are calling .FirstOrDefault, that this enumeration will actually occur. That's when the query will be executed.

And you are calling .FirstOrDefault in a loop. Each iteration loading the entire table. No wonder you are seeing performance issues.

.Net Core Async Saving

2 Answers2