Entity Framework Core with multiple DB Contexts, Schemas and Projects

• Download source - 14.7 KB

Introduction

Sometimes database schema can get complex and large, in such cases, database schema is usually separated in multiple schemas where one team can be responsible for subset of the DB schemas and back-end code accessing the schemas. Entity framework core (v2.0) allows separating DB schema into multiple projects where one project holds one or more DB contexts.

Sample solution will use the following tables in two schemas:

• Users.User
• Users.Role
• Products.Product

MVC Web API Project

First step is to create a MVC Web API Core project that will later use our DB contexts. I named the entry project and solution MultipleDbContexts. A number of nuget packages needs to be referenced. After referencing all packages, MultipleDbContexts.csproj file looks like this:

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>netcoreapp2.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <Folder Include="wwwroot\" />
  </ItemGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.6" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer.Design" 
     Version="2.0.0-preview1-final" />
  </ItemGroup>

  <ItemGroup>
    <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="2.0.3" />
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.0.2" />
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.2" />
  </ItemGroup>

</Project>

ValuesController is added by the template and we will leave it as it is for now. Same for Startup.cs, we will change it later.

Now we need a new database, for that purpose, we can use free SQL Server Express or (also free) SQL Server for developers. Create a new empty database and name it multipleDbContexts.

In order to use the database, we need to add connection string to appsettings.json file, which should look like this:

{
  "connectionString": "Server=host\\instance;Database=multipleDbContexts;Trusted_Connection=True;",
  "Logging": {
    // ...
  }
}

DB Context Projects

MultipleDbContexts.Users project will have UsersDbContext which will reference tables Users.User and Users.Role.

Add new class library to the solution and reference it in entry project. MultipleDbContexts.Users class library needs to reference EF nugets, so after adding all needed packages, MultipleDbContexts.Users.csproj looks like this:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>netcoreapp2.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.6" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer.Design" 
                      Version="2.0.0-preview1-final" />
  </ItemGroup>

  <ItemGroup>
    <DotNetCliToolReference 
       Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="2.0.3" />
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.0.2" />
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.2" />
  </ItemGroup>

</Project>

Now we need to create MultipleDbContexts.Products class library and reference same nuget packages, and set reference from the entry project to the MultipleDbContexts.Products.

It's time to add table models to MultipleDbContexts.Products and MultipleDbContexts.Users.

MultipleDbContexts.Products have the following files:

Products.cs

[Table("Product", Schema = "Products")]
public class Products
{
   public int Id { get; set; }
   [MaxLength(60)]
   public string Name { get; set; }
   [MaxLength(600)]
   public string ImageUrl { get; set; }
}

ProductsDbContext.cs

public class ProductsDbContext : DbContext
{
    private readonly IConfiguration _config;

    public ProductsDbContext(IConfiguration config, DbContextOptions<ProductsDbContext> options)
        : base (options)
    {
        _config = config ?? throw new System.ArgumentNullException(nameof(config));
    }

    public DbSet<Products> Products { get; set; }
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(_config["connectionString"], options =>
        {
            options.MigrationsHistoryTable("__UsersMigrationsHistory", "Products");
        });
    }
}

A few things you can notice from the files above:

• Products class has TableAttribute which defines name of the table and name of the schema.
• ProductsDbContext constructor accepts IConfiguration, that is needed for connection string.
• Second argument of ProductsDbContext constructor is DbContextOptions<ProductsDbContext>, instead of using DbContextOptions we need to specify generic DbContextOptions<> argument when using multiple db context.
• OnConfiguring method sets migration history table to use, that is because:
  1. we want migration history table to be in same schema as tables and
  2. we want to have separate migrations for each DB context

Now, we need to add the following files to MultipleDbContexts.Users project:

RoleModel.cs

[Table("Role", Schema = "Users")]
public class RoleModel
{
    [Key, MaxLength(60)]
    public string Key { get; set; }

    [Required, MaxLength(250)]
    public string Description { get; set; }
}

UserModel.cs

[Table("User", Schema = "Users")]
public class UserModel
{
    [Key]
    public int Id { get; set; }

    [Required, MaxLength(50)]
    public string Email { get; set; }
    
    [StringLength(500)]
    public string AboutUser { get; set; }
}

UsersDbContext.cs

public class UsersDbContext : DbContext
{
    private readonly IConfiguration _config;

    public UsersDbContext(IConfiguration config, DbContextOptions<UsersDbContext> options)
        : base(options)
    {
        _config = config ?? throw new System.ArgumentNullException(nameof(config));
    }

    public DbSet<UserModel> Users { get; set; }
    public DbSet<RoleModel> Roles { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(_config["connectionString"], options =>
        {
            options.MigrationsHistoryTable("__UsersMigrationsHistory", "Users");
        });
    }
}

Following screenshot shows solution structure.

Now we need to add our DB contexts to DI container in Startup.cs:

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<UsersDbContext>(options =>
    {
        options.UseSqlServer(Configuration["connectionString"],
            sqlServerOptions =>
            {
                sqlServerOptions.MigrationsAssembly("MultipleDbContexts.Users");
            });
    });

    services.AddDbContext<ProductsDbContext>(options =>
    {
        options.UseSqlServer(Configuration["connectionString"],
            sqlServerOptions =>
            {
                sqlServerOptions.MigrationsAssembly("MultipleDbContexts.Products");
            });
    });

    services.AddMvc();
}

Migrations

For migrations to work, we need to specify entry project when using dotnet ef command. That is done with -s argument. We also need to specify for which context we are applying migration, which is done with --context argument.

Open command prompt in directory where MultipleDbContexts.Products is located and enter the following commands:

dotnet ef migrations add products -s ../MultipleDbContexts/ --context ProductsDbContext
dotnet ef database update -s ../MultipleDbContexts/ --context ProductsDbContext

Similar commands are needed for MultipleDbContexts.Users in the directory of the project.

dotnet ef migrations add users -s ../MultipleDbContexts/ --context UsersDbContext
dotnet ef database update -s ../MultipleDbContexts/ --context UsersDbContext

Now our DB should have tables. Let's add some data for testing, execute the following SQL on the multipleDbContexts database:

INSERT INTO [Users].[User] (AboutUser, Email) VALUES ('About user', 'me@example.com')
INSERT INTO [Users].[Role] ([Key], [Description]) VALUES ('Admin', 'Super Admin')
INSERT INTO Products.Product (ImageUrl, Name) VALUES ('/products/teapot.png', 'Teapot')

Making Sure Everything Works

Everything should be in place, edit ValuesController to use DB contexts:

[Route("api/[controller]")]
public class ValuesController : Controller
{
    // GET api/values
    [HttpGet]
    public object Get(
        [FromServices] UsersDbContext usersDb, 
        [FromServices] ProductsDbContext productsDb
        )
    {
        return new
        {
            usersDb.Users,
            usersDb.Roles,
            productsDb.Products
        };
    }
}

Hitting F5 in Visual Studio should open the browser and navigating to /api/values should return the following JSON:

{
   "users":[
      {
         "id":1,
         "email":"me@example.com",
         "aboutUser":"About user"
      }
   ],
   "roles":[
      {
         "key":"Admin",
         "description":"Super Admin"
      }
   ],
   "products":[
      {
         "id":1,
         "name":"Teapot",
         "imageUrl":"/products/teapot.png"
      }
   ]
}

Final Notes

A few notes for those who got this far:

• I haven't tested this with other EF Core providers, however it should work.
• I know the example is super simple, we don't even have foreign keys, however that's not the point of this article.
• If you have any questions, you can find me in the comments.