.NET 6 Web Api Entity Framework Core with multiple databases

Question

Hi all.

I need to create a Web Api for reading/writing some databases.
The thing is that this Api will be used on different environments, and each environment has its own database (with different structures each). So, I use database first.
The idea is using this Api as a proxy to make other apps database independent.

So, apps will make GET request to the Api for getting, for example, a user object array (IdUser, Name, Email). The Api needs to connect to the database and transform the user table into that DTO object (IdUser, Name, Email).

For example:

• Computer 1. Database A. Table UsersActive (Fields: UserCode, UserName, Email)
• Computer 2. Database B. Table User (Fields: Id, Username, EmailAddress)
• And so on...

In the beginning, I will have 3 different database schemas, but the idea is to add more in the future.

When using the Api, I want to call /api/users and get a Json with the structure: IdUser, Name, Email. The Api will have in config "some value" for knowing the database schema (A or B).

The thing is: I'm used to classic framework, and this is my first core app. I don't know the best way for solving these requirements. My doubts:

• Can I have multiple Entity Framework Core models on the same Api? Any problem if two models have some objects with the same name? (For example, it's possible that database A and B have a table called User, with the same name).
• Which is the best Api design? I want to avoid that each method (for example UsersGet) to have a switch to determine the model to use, etc. Of course, I need to add the logic to transform the database table to the global DTO object that will be returned.
• Maybe is better to have one dll per schema, and use on each api only the dll of its schema?

Any help will be appreciated.

Answer 1

Based on your question, I believe the best thing is to implement multiple DbContext in your application. Each datacontext can have its models. You can initiate multiple database context in the startup.

Hope this helps

Answer 2

As sreejukg says the cleanest solution would be to just use multiple DbContext instances, then define an interface declaring common operations that each schema can implement that has the job of converting from the data model format to your common API format. That's probably the solution I'd go with, just in case the schemas diverge too far and can no longer fit into a common data model.

The second approach you could look into would be to use a single DbContext & data model format and use the fluent EF mapping methods to tell EF which columns to map to. So you would have an appsetting to dictate which schema instance you're using for the running app, define an abstract DbContext class that contains your entity properties, then creating a derived class per schema that inherits from that base class where you configure the mapped columns using the fluent API like in this example:
https://www.learnentityframeworkcore.com/configuration/fluent-api/hascolumnname-method

I haven't tried this second method myself so you'd have to have a play around and see if it suits your needs.

Answer 3

Hi. Thank both for your answers.

@Sreeju Nair very clear.
@P a u l very well explained.