CODEWITHSUNDEEP
c#entity-frameworkasp.net-core
Aleks Vujic
Aleks Vujic

Reputation: 2251

EF Core Linq Where nested database call

I want to perform a database query using EF Core. GetAvailableUsers method calls another method in .Where(...) condition that accesses DbContext as well. When I do that, I get the following error:

A second operation started on this context before a previous operation completed. This is usually caused by different threads using the same instance of DbContext, however instance members are not guaranteed to be thread safe. This could also be caused by a nested query being evaluated on the client, if this is the case rewrite the query avoiding nested invocations.

FirstService.cs

private MyDbContext _dbContext;
private ISecondService _secondService;

public class FirstService(MyDbContext dbContext, ISecondService secondService)
{
    _dbContext = dbContext;
    _secondService = secondService;
}

public async Task<List<AppUser>> GetAvailableUsers()
{
    return await _dbContext.AppUser
        .Where(x => x.IsActive)
        .Where(x => _secondService.HasRole(x.Id, 3))
        .ToListAsync();
}

SecondService.cs

private MyDbContext _dbContext;

public class SecondService(MyDbContext dbContext)
{
    _dbContext = dbContext;
}

public bool HasRole(int userId, int roleId)
{
    // ... some complex logic

    return _dbContext.AppUserRoles
        .Any(x => x.UserId == userId && x.RoleId == roleId);
}

I registered DbContext in Startup.cs with:

services.AddEntityFrameworkNpgsql().AddDbContext<DbContext>(options => options.UseNpgsql(Configuration.GetConnectionString("abc")));

How can I get rid of the error? Is there any workaround to achieve the same result? What consequences will it have if I register DbContext as transient?

Upvotes: 0

Views: 494

Answers (4)

Aleks Vujic
Aleks Vujic

Reputation: 2251

I solved the problem by creating new DbContext in SecondService.cs.

public bool HasRole(int userId, int roleId)
{
    MyDbContext newContext = new MyDbContext(...);

    using (newContext)
    {
        return newContext.AppUserRoles
            .Any(x => x.UserId == userId && x.RoleId == roleId);
    }
}

Upvotes: 1

Rena
Rena

Reputation: 36585

Is there any workaround to achieve the same result?

You could get all the UserId in AppUser, then foreach the UserId and judge whether HasRole return true or not.Here is a smiple demo:

Model:

public class Role
{
    public int RoleId { get; set; }
    public string RoleName { get; set; }
    public List<AppUserRole> AppUserRole { get; set; }
}
public class AppUser
{
    [Key]
    public int UserId { get; set; }
    public bool IsActive { get; set; }
    public List<AppUserRole> AppUserRole { get; set; }
}
public class AppUserRole
{
    [Key]
    public int Id { get; set; }
    public int UserId { get; set; }
    public AppUser AppUser { get; set; }
    public int RoleId { get; set; }
    public Role Role { get; set; }
}

Action:

public async Task<List<AppUser>> GetAvailableUsers()
{
        var userlist =new List<AppUser>();
        var userIds =await _dbContext.AppUser.Select(u => u.UserId).ToListAsync();
        foreach (var userid in userIds)
        {
            var flag = _secondService.HasRole(userid, 3);
            if (flag)
            {
                var user = await _dbContext.AppUser.Where(x => x.IsActive).Where(x => x.UserId == userid).FirstOrDefaultAsync();
                userlist.Add(user);
            }  
        }           
        return userlist;
}

Upvotes: 1

mjwills
mjwills

Reputation: 23819

Is there any workaround to achieve the same result?

I'd suggest one of three solutions:

  1. Switch your registration of MyDbContext from Scoped to Transient. This will ensure that the two methods use two different DB connections.
  2. Execute .Where(x => _secondService.HasRole(x.Id, 3)) after ToListAsync.
  3. Restructure your code such that a single SQL query is executed (see Anton's answer for a starting point).

Option 1 and 2 effectively both rewrite the query avoiding nested invocations. The downside of 1 and 2 is that they will both execute many database queries and potentially bring more data over the wire than required.

Option 3 is your best long term solution (since it will scale much better), but it is hard to give concrete advice here without seeing the entirety of HasRole.

What are the pros and cons of registering DbContext as transient?

They are largely the same as for any other type. If the type is not thread-safe (and you plan to interact with it from multiple threads in the context of a single web request), or you plan to use it in a way that it doesn't support (e.g. multiple DB requests at the same time on the same DB Context like you are doing now) then transient will work and scoped won't.

As a general rule, Scoped is more appropriate for a DB Context.

What consequences will it have if I register DbContext as transient?

Try it and see. I strongly suspect the exception will stop occurring. It is impossible to give more specific guidance without seeing the entirety of your application alas.

Upvotes: 0

Anton Toshik
Anton Toshik

Reputation: 2909

Rewrite this 

return await _dbContext.AppUser
    .Where(x => x.IsActive)
    .Where(x => _secondService.HasRole(x.Id, 3))
    .ToListAsync();

To Include the Role and then you can access it directly, also return task not to create state machine. This will also create 1 query instead of N+1.

public Task<List<AppUser>> GetAvailableUsers()
{
    return _dbContext.AppUser
            .Include(x => x.Roles)
            .Where(x => x.IsActive && x.Roles.Select(y => y.RoleId).Contains(3))
            .ToListAsync();
}

Why AddDbContext is Scoped

Upvotes: 2

Related Questions