Question
I am working on ASP.NET core application where I am using Entity Framework to interact with SQL server database. so far, I was using Scaffold-DbContext command to create new model from SQL server database. Even if , we wanted to create DbContext based on multiple tables, I could do that using -t flag in above command. Everything happening on single SQL database.
Scaffold-DbContext "Server=XXXXXXXXX;Database=XXXXXXXX;User Id=XXXXX;Password:XXXXXX" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -t Table1 Table2
From Scaffold-DbContext parameters commands, I couldn't find option to specify different databases.
In new scenario, I have to join 2 tables from different SQL databases. Is there a way to create model which is consist of 2 tables from different databases. Both tables are using one-to-one relationship between them.
For example- DB1 has table1 and DB2 has table2. Is there a way to create a DbContext which is consist of these 2 tables (table1 and table2)?
Is there any other way to achieve join between 2 tables from 2 different database?
Accepted Answer
No, you can't do that with EntityFramework (neither 6.x nor Core). A DbContext is per database and you can only do joins within the same DbContext.
You could create a view and map the view to the models you needs, but iirc. mapping of views is still on the roadmap for EntityFramework Core. May work with EntityFramework 6.x though.
Edit:
At least you can't doing it with Linq/Fluent api. You can execute raw queries though. The catch: The project must match the model exactly, there can't be any missing fields of the model. The Ad-hoc mapping to non-entities is on the roadmap for future versions of EntityFramework Core
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow