Entity classes for multiple schemas not separated into different folders

[kiberkli]

Hello all.

I'm migrating an existing postgresql database to be used with .Net Core. There are a number of schemas in the database.
I'm reverse engineering the database and I'm getting issues with the results. If there is a table in one schema and a table with the same name in a different schema, the entity classes that are create are Class.cs and Class1.cs in the output folder a specified (Entities):

• client.User creates Entities/User.cs
• my_admin.User creates Entities/User1.cs

These classes are in the same namespace and not differentiated in any meaningful way.

I haven't seen an scaffolding option that creates a subdirectory for each schema and places the schema's generated classes into that subdirectory:

• client.User creates Entities/Client/User.cs
• my_admin.User creates Entities/MyAdmin/User.cs

Multiple dotnet commands for each schema would be nice, sort of like this:

dotnet ef \
dbcontext \
scaffold \
"Host=localhost;Database=my_database.my_admin;Username=dbuser;Password=dbpassword" Npgsql.EntityFrameworkCore.PostgreSQL \
--output-dir ./Entities/MyAdmin

dotnet ef \
dbcontext \
scaffold \
"Host=localhost;Database=my_database.client;Username=dbuser;Password=dbpassword" Npgsql.EntityFrameworkCore.PostgreSQL \
--output-dir ./Entities/Client

The classes would also have to be in a namespace for each schema.

(I'm sure I'm missing something, I can't be the only one who has multiple schemas.)

[kiberkli]

This issue can be addressed by adding --schema [schema] and -o [output folder]. One would issue a dotnet ef command line for each schema.
This works but one would get a context class in each schema's folder.

[roji]

Duplicate of dotnet/efcore#3988

[roji]

@kiberkli this would be an EF Core-level enhancement, and not specific to the PostgreSQL provider.

You should probably take a look at the EF Core Power Tools, which supports this.

[guidevnet]

@roji EF Core Power Tools does not support this. This is marked as enhancement in this issue.

Today, it can create several folders, one per schema, but you can't set the namespace (one per schema) to fix the User1 problem. Do you have any suggestion?

[roji]

@guidevnet not really, unfortunately...

[kiberkli]

I run the scaffolding command for each schema:
dotnet ef dbcontext scaffold <connection-string> Npgsql.EntityFrameworkCore.PostgreSQL --context <Schema>Context --output-dir ./Entities/<Schema> --schema <schema> --force

Side effect is that it builds your project for each command. I have 11 schemas and I get a context class for each. I suppose it allows me to run it for only for the schema that changed.
For me this is good enough.

[guidevnet]

I run the scaffolding command for each schema:
dotnet ef dbcontext scaffold <connection-string> Npgsql.EntityFrameworkCore.PostgreSQL --context <Schema>Context --output-dir ./Entities/<Schema> --schema <schema> --force

Side effect is that it builds your project for each command. I have 11 schemas and I get a context class for each. I suppose it allows me to run it for only for the schema that changed.
For me this is good enough.

This is also my current way of doing things. However, this has several downsides:

1. I have about 15 schemas, and I use the entire database in several WebApi controllers. In order to use dependency injection, my Asp.Net Core Startup class became this monstrosity:

services.AddScoped<Schema1.Entities>();
services.AddScoped<Schema2.Entities>();
services.AddScoped<Schema3.Entities>();
services.AddScoped<Schema4.Entities>();
services.AddScoped<Schema5.Entities>();
services.AddScoped<Schema6.Entities>();
services.AddScoped<Schema7.Entities>();
services.AddScoped<Schema8.Entities>();
services.AddScoped<Schema9.Entities>();
services.AddScoped<Schema10.Entities>();
services.AddScoped<Schema11.Entities>();
services.AddScoped<Schema12.Entities>();
services.AddScoped<Schema13.Entities>();
services.AddScoped<Schema14.Entities>();
services.AddScoped<Schema15.Entities>();

This is very ugly, and I don't know if there is any optimization magic under the hood, it seems that it instantiate 15 Entity classes in every API request (and we have the need to be extremely responsive).

2. I have some tables that have relationships between schemas, and the scaffold operation does not generate navigation properties for the auto join. There are several missing properties, and my code is full of manual joins and duplicated connections (select in one schema, read the results, select again in other schema), forcing me to remember where to join in every table and making my code way slower, instead of building a single query through property navigation.

3. Because of (2), I can't use LazyLoading Proxies between schemas.

4. I have to issue a lot of commands to scaffold the database, and they rebuild my giant project every single time.

5. And, of course, the other option is to generate everything in one go, and deal with User1, User2, User3, User4 classes.