dbContext issue in C#

Question

error message : System.InvalidOperationException: 'A second operation was started on this context instance before a previous operation completed. This is usually caused by different threads concurrently using the same instance of DbContext. For more information on how to avoid threading issues with DbContext

It occur error when I get data from db at second time.

_context.UserPTO.Where(up => up.UserId == user.Id).FirstOrDefault() in below source

How can I do for this?

private DbContextOptions<DataContext> GetAllOptions()
{
    var optionBuilder = new DbContextOptionsBuilder<DataContext>();

    optionBuilder.UseSqlServer(AppSettings.ConnectionString);

    return optionBuilder.Options;
}

public void UpdatePTO()
{

    try
    {
        using (_context = new DataContext(GetAllOptions()))
        {
            var users =  _context.Users.ToList();

            if (users != null && users.Count > 0)
            {
                foreach (var user in users)
                {
                    var plusPTO = 0.00M;
                    var workedYear = DateTime.Now.Date.Year - user.StartWorkDate.Date.Year;

                    switch (workedYear)
                    {
                        case int n when (3 < n):
                            plusPTO = 1.25M;
                            break;
                        case int n when (3 < n):
                            plusPTO = 1.50M;
                            break;
                    };

                    var userPTO = _context.UserPTO.Where(up => up.UserId == user.Id).FirstOrDefault();

                    if (userPTO != null)
                    {
                        userPTO.Pto = userPTO.Pto + plusPTO;
                    }

                    var userPTOHistory = new UserPTOHistory
                    {
                        UserId = user.Id,
                        PTOType = "",
                        UserPTOId = 0,
                        CurrentPTO = userPTO.Pto,
                        NeedPTO = 0,
                        VerifiedType = "Accepted",
                        CreatedDate = DateTime.Now,
                        VerifiedDate = DateTime.Now,
                        CalculatedPTO = userPTO.Pto
                    };

                    _context.Add(userPTOHistory);

                    _context.SaveChangesAsync();
                }
            }



        }
    }
    catch (Exception)
    {
        throw;
    }

}

// program.cs ( cf) this is a worker service project. )

using Microsoft.EntityFrameworkCore;
using WorkerService;
using WorkerService.Services;

IHost host = Host.CreateDefaultBuilder(args)
    .ConfigureServices((hostContext, services) =>
    {
        IConfiguration configuration = hostContext.Configuration;
        AppSettings.Configuration = configuration;
        AppSettings.ConnectionString = configuration.GetConnectionString("DefaultConnection");

        var optionBuilder = new DbContextOptionsBuilder<DataContext>();
        optionBuilder.UseSqlServer(AppSettings.ConnectionString);

        services.AddScoped<DataContext>(d => new DataContext(optionBuilder.Options));

        services.AddHostedService<Worker>();
    })
    .Build();


await host.RunAsync();

Try rebuilding and restarting the app. Provided code does not show anything which could result in such issue. Can you provide a [mre]? — Guru Stron, Mar 15 '23 at 13:18

score 0 · Answer 1 · answered Mar 15 '23 at 23:12

You are in the middle of the foreach while you are trying to do another query. You need to enable MARS for this to work. See the documentation.

Add the following to your connection string

MultipleActiveResultSets=True

Incidentally this is a terrible way to get a result, it has the famous N+1 problem. You can do it much better using joins, navigation properties and inline calculations

_context.Users.Select(user =>
    new {
        user,  // user should have PTO navigation property
        workedYear = (DateTime.Now.Date.Year - user.StartWorkDate.Date.Year) =>
            switch (workedYear)
            {
                > 3 => 1.25M,
                _   => 1.50M,
            },
    }).ToList();

It's unclear what you actually do with the result, but the point is to let LINQ translate the whole thing to SQL.

This method does not require MARS, which has a performance penalty.

dbContext issue in C#

1 Answers1