Working with multiple databases using .Net 5.0 and Entity Framework Core 5.0
The first question definitely comes in mind, why use multiple databases ? Well it is good to have database vendor independent backend so that you can swap it out whenever it is required. Or you may have a client who wants their data in a particular database vendor. That’s where ORM comes into picture. ORM like Entity-Framework allows you to talk to multiple database with ease.
This article is about using Entity Framework to achieve this and I can say it is very very simple. Shall we start ? Here we taking SQL Server and Postgres DB as an example.
First of all we need a base DbContext, which contains DbSets which are common between databases.
Now we need separate DbContext for each of the database types. Here we have SQLServerDbContext and PostgresDbContext. Both can have their own DbSets as well or they can share DbSets which is defined in parent AppDbContext
As you can see both DbContext overrides OnConfiguring method as it requires separate database drivers for each as well as separate connection strings. Also UseSqlServer and UseNpgsql is not natively available. To use these you have to install these nuget packages. There are nuget packages of Oracle and In-Memory Db as well if you want to explore.
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="5.0.4"/><PackageReference Include="Microsoft.EntityFrameworkCore.InMemory" Version="5.0.4"/><PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="5.0.2"/><PackageReference Include="Oracle.EntityFrameworkCore" Version="5.21.1"/>
At Startup of the Application, In ConfigureServices method, we can register the particular DbContext during startup of the application by adding a key in appsettings.json, a kind of flag to choose DbContext. PS-This is an incomplete startup.cs file.
You appsettings.json might look like this-
{"Logging": { "LogLevel": { "Default": "Information", "Microsoft": "Warning", "Microsoft.Hosting.Lifetime": "Information" }},"AllowedHosts": "*","DatabaseType": "Postgres","ConnectionStrings": {"SQLServerConnectionString": "Server=SUMANH-WIN-LP\\SQLEXPRESS;Database=HospitalDB;User Id=sa;Password=passtheword","PostgresConnectionString": "host=localhost;port=5432;database=HospitalDB;user id=postgres;password=passtheword" }
}
Now here we can use, built-in dependency injection feature of .Net Core/.Net 5.0. Your particular DbContext is injected into the services and we can use it anywhere in our project.
So suppose now we have a UserService and we want to use the DbContext. All we can simply inject AppDbContext in the constructor and in the runtime AppDbContext will be either of SQLServerDbContext/PostgresDbContext type and you can use this context to query the database. That’s it and you are done.
Here are the screenshots of context at Runtime-
Thanks for your time to read. If you have any questions, you can reach me out by mail at suman.hansada@gmail.com and on twitter at Suman Hansada (@SumanHansada) / Twitter
