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

• Download source code - 8.1 KB

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.

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.

Another Db Context

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

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.

Db

Connection String

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

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

Commands

Add-Migration MigrationName
Update-Database
Script-Migration

Drop-Database 
Remove-Migration MigrationName

Migrations History Table

The database now contains two different migration tables.

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.

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

• https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/history-table

History

• 8^th August, 2022: Initial version