Entity Framework: Can DB Contexts Have Foreign Keys Across Different Schemas?

Question

We have a single database with multiple schemas. Company architect wants to have Entity Framework DbContext for each schema. Additionally, foreign key relationships exist between different schemas.

In scaffolding a schema today, received this error:

Error:

For foreign key FK_Customer_TransactionId on table CustomerTransaction, Unable to model the end of the foreign key on principal table inv.Product. This is usually because the principal table was not included in the selection set."

I need to have one schema and 1 table from another schema. It cannot locate the Parent Table on the additional schema to create foreign key off.

Looking for solutions:

(1) Is it bad practice in general to have multiple db contexts for every schema, with foreign keys between different schemas? Do Microsoft and Entity Framework support this?

(2) In Entity Framework Core Scaffold, Is there anyway to scaffold a schema And an additional parent table from another schema?

(3) Or is there method to ignore foreign key property on outside schemas?

dotnet ef dbcontext scaffold 
   "Server=localhost;Database=DatabasetestTest;Trusted_Connection=True;" 
   Microsoft.EntityFrameworkCore.SqlServer -c DatabaseContext 
   -o Entities\Scaffold 
   -f 
   --project C:\Project 
   --schema CustomerService

Currently using EF Core 2.2

Entity Framework: One Database, Multiple DbContexts. Is this a bad idea?

Using Entity Framework 6 with Multiple DB Schemas but using One DBContext

Why he wants to have separate context to for each schema? DDD and bounded context? In my opinion messing with DbContext right from the start is not a good idea. — dropoutcoder, Aug 14 '19 at 17:37
Okay. I will try to put something together and give you an answer. Gimme day or two. — dropoutcoder, Aug 14 '19 at 17:41
hi @dropoutcoder sometimes I think its choice 3 above in question, Microsoft may not have solution, thanks — , Aug 14 '19 at 21:48

score 1 · Accepted Answer · answered Aug 17 '19 at 16:48

(1) Is it bad practice in general to have multiple db contexts for every schema, with foreign keys between different schemas? Do Microsoft and Entity Framework support this?

I don't think there is a bad practice in having multiple schemas or/and DbContext or relationships between schemes. Entity Framework supports it. Anyway, I'd suggest to think about doing it as in the future you can end up having too many database contexts and it may become a big mess.

(2) In Entity Framework Core Scaffold, Is there anyway to scaffold a schema And an additional parent table from another schema?

It is. You can scaffold multiple schemes and tables. The only thing is you have to specify concrete schemes and tables to scaffold.

dotnet ef dbcontext scaffold 
   "Server=localhost;Database=DatabasetestTest;Trusted_Connection=True;" 
   Microsoft.EntityFrameworkCore.SqlServer -c DatabaseContext 
   -o Entities\Scaffold 
   -f 
   --project C:\Project 
   --schema CustomerService
   --schema AnotherSchema
   --schema YetAnotherSchema
   --table Table1
   --table Table2
   --table Table3
   --table TableX

(3) Or is there method to ignore foreign key property on outside schemas?

I don't think so. Whenever two tables have relation between each other you have to conform it. If you want to scaffold table CustomerService.CustomerTransaction that is referenced by FK in table inv.Product you have to include schema 'inv' and table 'Product' to the scaffolding. You might get another error as there might be other relations with tables you going to include. You may end up scaffolding whole database.

Entity Framework: Can DB Contexts Have Foreign Keys Across Different Schemas?

1 Answers1

Linked