I have a .Net Core 2.1 Web API which talks to two MySQL databases. Therefore I have two DbContexts, each with a connection string pointing to the relevant database.
In one of my controller actions, I need to return data which requires a join between two tables, one from each database. Is it possible to do this?
As an example, a simple controller action to retrieve data might look something like this:
[HttpGet]
public IEnumerable<Employee> GetEmployees()
{
return _context.Employees
.Include(e => e.Departments);
}
That example uses one controller only, because in that example both the employee and department tables are in the same database, and therefore both their DbSets would be in the same DbContext.
But what if the employee table was in one database and department table was in another? Then the DbSets for employee and department would be defined in different DbContexts. How could I handle the join in that case? (So that in the example above, the "Include" works properly?
I would imagine that I would have to inject both DbContexts into this controller. But I'm not sure where to go from there...
In my case, both datbases are MySQL databases, and both are on the same server, so that is the only scenario I'm interested in.
After more research, it looks like I could maybe use raw SQL to achieve this. However, what I ended up doing is creating a view on the server which does all the necessary joins, and then I simply call this view...