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