Click here to Skip to main content
15,441,149 members
Articles / Programming Languages / C#
Tip/Trick
Posted 7 Aug 2022

Stats

2.3K views
13 downloads
4 bookmarked

Entity Framework Core Code First Publishing Multiple Db Contexts in the Same Database

Rate me:
Please Sign up or sign in to vote.
3.40/5 (2 votes)
7 Aug 2022CPOL2 min read
EF Core first publishing multiple DB contexts in same DB
In this article, you will learn about the Entity Framework core first publishing multiple DB context in the same database.

Background

I once encountered a scenario, where we had to deploy two code first DB contexts (EF core 3 and 5) of two different projects in the same SQL Server database. To make things more manageable, I was thinking of using different migration tables. This migration table customize option is actually available in Entity Framework.

Db Context

We will find the DB context inside Db.App project.

C#
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using System.ComponentModel.DataAnnotations.Schema;

namespace Db.App
{
    [Table("Teams")]
    public class Team
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class AppDb : DbContext
    {
        public DbSet<Team> Teams { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var config = new ConfigurationBuilder()
                .AddJsonFile(Path.Combine(AppContext.BaseDirectory, 
                 "appsettings.json"), optional: false, reloadOnChange: true)
                .Build();
            optionsBuilder.UseSqlServer
            (config.GetConnectionString("DatabaseConnection"));
        }
    }
}

This is a basic Entity Framework DB context. Let's add migration and update the database in the project. This process will add a new table Teams and a migration log table __EFMigrationsHistory to the database.

Image 1

Another Db Context

We will find the DB context inside Db.ExportLog project.

C#
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using System.ComponentModel.DataAnnotations.Schema;

namespace Db.App
{
    [Table("FtpFiles", Schema = "log")]
    public class FtpFile
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class AppDb : DbContext
    {
        public DbSet<FtpFile> FtpFiles { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var config = new ConfigurationBuilder()
                .AddJsonFile(Path.Combine(AppContext.BaseDirectory, "appsettings.json"),
                              optional: false, reloadOnChange: true)
                .Build();
            optionsBuilder.UseSqlServer(config.GetConnectionString("DatabaseConnection"),
                 d => { d.MigrationsHistoryTable("__EFMigrationsHistory_FtpLog"); } );
        }
    }
}

Let's add migration and update the database in the project. This process will add a new table FtpFiles with log schema. And a migration log table __EFMigrationsHistory_FtpLog.

Image 2

Db

Connection String

In appsettings.json, we will find the target DB connections:

C#
{
  "ConnectionStrings": {
    "DatabaseConnection": "Data Source=.\\SQLEXPRESS;
     Initial Catalog=Cup;Integrated Security=True"
  }
}

Commands

C#
Add-Migration MigrationName
Update-Database
Script-Migration

Drop-Database 
Remove-Migration MigrationName
Migrations History Table

The database now contains two different migration tables.

Image 3

Limitations

  • It is better not to use the same table names in both DB contexts. If necessary, use the same name table but the schema should be different.
  • A relationship between two tables of two different DB contexts is not possible using code first process.
  • If we run Drop-Database at any project, will drop the entire database.

Multiple Db Context in Same Project

By default, the entity framework populates migration files inside the Migrations folder. If multiple Db contexts are present in the same project, they will use the same migration folder. To populate migrations of different Db contexts in different folders, we can use listed commands.

C#
Add-Migration MigrationName -c DbContextName -o Migrations\FolderName
Update-Database -Context DbContextName
Script-Migration -Context DbContextName -o ProjectFolderName\Migrations\DbContextName.sql

Remove-Migration MigrationName -Context DbContextName

About Code Sample

  • Visual Studio 2022 Solution
  • ASP.NET 6
  • EF Core 6
  • This example is also tested in core 5.

Reference

History

  • 8th August, 2022: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Bangladesh Bangladesh
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --