Last post Jul 27, 2018 01:14 PM by f.a.rodriguez
Jul 26, 2018 01:42 PM|f.a.rodriguez|LINK
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:
public IEnumerable<Employee> GetEmployees()
.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.
Jul 26, 2018 01:57 PM|mgebhard|LINK
I generally write and invoke a raw T-SQL script. This works fine as long as the user executing the script has access to both database. I've never tried MySQL. Give it a try by running a query form one DB that accesses the other DB.
Jul 26, 2018 02:15 PM|bruce (sqlwork.com)|LINK
Jul 26, 2018 02:47 PM|f.a.rodriguez|LINK
Ok thanks guys. I will try with the raw SQL. I guess as long as the user specified in the connection string has access to both databases, then it should work using just the one context. I will try it and let you know....
Jul 27, 2018 01:14 PM|f.a.rodriguez|LINK
Thanks guys. I tried the raw SQL approach but kept getting
InvalidOperationException: Sequence contains more than one matching element
errors. And I'm not even doing a SingleOrDefault()..... And anyway, the query is returning a single row (hell, I even added a `LIMIT 1` to the SQL query to be certain).
Anyway, I ended up creating a view on the database which does the join to the other database, which I simply call from my program.