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.
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.