Using multiple DbContext getting error Cannot use multiple DbContext instances within a single query execution

asp.net-core-mvc c# entity-framework-core

Question

I started a new MVC app using .NET Core 3. I have three DbContext files that use three different databases: ComplaintDbContext for my main application, IdentityCoreDbContext for Identity users, and EmployeeDbContext for my employee database.

In my app I have a repository class called ComplaintRepository and the constructor looks like this:

public ComplaintRepository(ComplaintDbContext context, 
        EmployeeDbContext employeeContext)
    {
        _context = context;
        _employeeContext = employeeContext;
    }

In my ComplaintController I need to get data from both databases. I can get my data from my Complaint database, but once I call my Action that gets data from my Employee database I get the error:

Cannot use multiple DbContext instances within a single query execution. Ensure the query uses a single context instance.

I tried something like this:

public class FrameworkContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }
}

public class ExtendedContext : FrameworkContext
{
    public DbSet<Order> Orders { get; set; }
}

I cannot get it working. Any help would be appreciated. Thanks!

Edit:

I started a new repository called EmployeeRepository to separate concerns. Here is my Action that is giving me problems:

    public IEnumerable<ApplicationUser> GetWorkerList()
    {
        var employees = _employeeRepository.GetEmployeesByUnit(22);

        //Get ApplicationUsers where user exists in Employees list
        IEnumerable<ApplicationUser> userList = _userManager.Users
            .Where(emp => employees.Any(e => emp.EmployeeID == e.EmployeeId)).OrderBy(e => e.LastName);

        return userList;            
    }

My Employee database and my Identity database both share a column called EmployeeId.

When I tried changing it to use ToList() I started getting a different error:

InvalidOperationException: The LINQ expression 'Where( source: DbSet, predicate: (a) => Any( source: (Unhandled parameter: __employees_0), predicate: (e) => a.EmployeeID == e.EmployeeId))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().

Edit:

I used Tao Zhou's recommendation of using ToList() and I was able to get it working. I replaced IEnumerable in my repository to use this:

    public List<TblEmployee> GetEmployeesByUnit(int unitId)
    {
        var emp = _context.TblEmployee.Where(e => e.UnitId == unitId &&
                e.TermDate == null)
            .OrderBy(e => e.LastName).ToList();

        return emp;
    }

In my controller I basically did the same and I now have this:

    public List<ApplicationUser> GetWorkerList()
    {
        var employees = _employeeRepository.GetEmployeesByUnit(22);

        List<ApplicationUser> userList = new List<ApplicationUser>();

        //Get ApplicationUsers where user exists in Employees list
        foreach (TblEmployee emp in employees)
        {
            ApplicationUser user = _userManager.Users
                .Where(e => e.EmployeeID == emp.EmployeeId).FirstOrDefault();

            userList.Add(user);
        }

        return userList;            
    }

I would like to use LINQ instead of the foreach loop.

1
0
10/24/2019 2:19:53 PM

Popular Answer

I met with same the problem and i solved using async-await structure. I have 2 queries and it gives me the error.

var result = await _context.YourTable.ToListAsync();

Maybe this can help you.

0
1/24/2020 12:59:29 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow