Entity Framework: One Database, Multiple DbContexts. Is this a bad idea?

My impression to date has been that a DbContext is meant to represent your database, and thus, if your application uses one database, you'd want only one DbContext.

However, some colleagues want to break functional areas out into separate DbContext classes.

I believe this comes from a good place -- a desire to keep the code cleaner -- but it seems volatile. My gut's telling me it's a bad idea, but unfortunately, my gut feeling is not a sufficient condition for a design decision.

So I'm looking for:

A) concrete examples of why this might be a bad idea;

B) assurances that this will all work out just fine.

You can have multiple contexts for single database. It can be useful for example if your database contains multiple database schemas and you want to handle each of them as separate self contained area.

The problem is when you want to use code first to create your database - only single context in your application can do that. The trick for this is usually one additional context containing all your entities which is used only for database creation. Your real application contexts containing only subsets of your entities must have database initializer set to null.

There are other issues you will see when using multiple context types - for example shared entity types and their passing from one context to another, etc. Generally it is possible, it can make your design much cleaner and separate different functional areas but it has its costs in additional complexity.

I wrote this answer about four years ago and my opinion hasn't changed. But since then there have been significant developments on the micro-services front. I added micro-services specific notes at the end...

I'll weigh in against the idea, with real-world experience to back up my vote.

I was brought on to a large application that had five contexts for a single database. In the end, we ended up removing all of the contexts except for one - reverting back to a single context.

At first the idea of multiple contexts seems like a good idea. We can separate our data access into domains and provide several clean lightweight contexts. Sounds like DDD, right? This would simplify our data access. Another argument is for performance in that we only access the context that we need.

But in practice, as our application grew, many of our tables shared relationships across our various contexts. For example, queries to table A in context 1 also required joining table B in context 2.

This left us with a couple poor choices. We could duplicate the tables in the various contexts. We tried this. This created several mapping problems including an EF constraint that requires each entity to have a unique name. So we ended up with entities named Person1 and Person2 in the different contexts. One could argue this was poor design on our part, but despite our best efforts, this is how our application actually grew in the real world.

We also tried querying both contexts to get the data we needed. For example, our business logic would query half of what it needed from context 1 and the other half from context 2. This had some major issues. Instead of performing one query against a single context, we had to perform multiple queries across different contexts. This has a real performance penalty.

In the end, the good news is that it was easy to strip out the multiple contexts. The context is intended to be a lightweight object. So I don't think performance is a good argument for multiple contexts. In almost all cases, I believe a single context is simpler, less complex, and will likely perform better, and you won't have to implement a bunch of work-arounds to get it to work.

I thought of one situation where multiple contexts could be useful. A separate context could be used to fix a physical issue with the database in which it actually contains more than one domain. Ideally, a context would be one-to-one to a domain, which would be one-to-one to a database. In other words, if a set of tables are in no way related to the other tables in a given database, they should probably be pulled out into a separate database. I realize this isn't always practical. But if a set of tables are so different that you would feel comfortable separating them into a separate database (but you choose not to) then I could see the case for using a separate context, but only because there are actually two separate domains.

Regarding micro-services, one single context still makes sense. However, for micro-services, each service would have its own context which includes only the database tables relevant to that service. In other words, if service x accesses tables 1 and 2, and service y accesses tables 3 and 4, each service would have its own unique context which includes tables specific to that service.

I'm interested in your thoughts.

Distinguishing contexts by setting the default schema

In EF6 you can have multiple contexts, just specify the name for the default database schema in the OnModelCreating method of you DbContext derived class (where the Fluent-API configuration is). This will work in EF6:

public partial class CustomerModel : DbContext
{	
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("Customer");

        // Fluent API configuration
    }	
}

This example will use "Customer" as prefix for your database tables (instead of "dbo"). More importantly it will also prefix the __MigrationHistory table(s), e.g. Customer.__MigrationHistory. So you can have more than one __MigrationHistory table in a single database, one for each context. So the changes you make for one context will not mess with the other.

When adding the migration, specify the fully qualified name of your configuration class (derived from DbMigrationsConfiguration) as parameter in the add-migration command:

add-migration NAME_OF_MIGRATION -ConfigurationTypeName FULLY_QUALIFIED_NAME_OF_CONFIGURATION_CLASS

A short word on the context key

According to this MSDN article "Chapter - Multiple Models Targeting the Same Database" EF 6 would probably handle the situation even if only one MigrationHistory table existed, because in the table there is a ContextKey column to distinguish the migrations.

However I prefer having more than one MigrationHistory table by specifying the default schema like explained above.

Using separate migration folders

In such a scenario you might also want to work with different "Migration" folders in you project. You can set up your DbMigrationsConfiguration derived class accordingly using the MigrationsDirectory property:

internal sealed class ConfigurationA : DbMigrationsConfiguration<ModelA>
{
    public ConfigurationA()
    {
        AutomaticMigrationsEnabled = false;
        MigrationsDirectory = @"Migrations\ModelA";
    }
}

internal sealed class ConfigurationB : DbMigrationsConfiguration<ModelB>
{
    public ConfigurationB()
    {
        AutomaticMigrationsEnabled = false;
        MigrationsDirectory = @"Migrations\ModelB";
    }
}

Summary

All in all, you can say that everything is cleanly separated: Contexts, Migration folders in the project and tables in the database.

I would choose such a solution, if there are groups of entities which are part of a bigger topic, but are not related (via foreign keys) to one another.

If the groups of entities do not have anything to do which each other, I would created a separate database for each of them and also access them in different projects, probably with one single context in each project.

Simple example to achieve the below:

    ApplicationDbContext forumDB = new ApplicationDbContext();
    MonitorDbContext monitor = new MonitorDbContext();

Just scope the properties in the main context: (used to create and maintain the DB) Note: Just use protected: (Entity is not exposed here)

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext()
        : base("QAForum", throwIfV1Schema: false)
    {

    }
    protected DbSet<Diagnostic> Diagnostics { get; set; }
    public DbSet<Forum> Forums { get; set; }
    public DbSet<Post> Posts { get; set; }
    public DbSet<Thread> Threads { get; set; }
    public static ApplicationDbContext Create()
    {
        return new ApplicationDbContext();
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
    }
}

MonitorContext: Expose separate Entity here

public class MonitorDbContext: DbContext
{
    public MonitorDbContext()
        : base("QAForum")
    {

    }
    public DbSet<Diagnostic> Diagnostics { get; set; }
    // add more here
}

Diagnostics Model:

public class Diagnostic
{
    [Key]
    public Guid DiagnosticID { get; set; }
    public string ApplicationName { get; set; }
    public DateTime DiagnosticTime { get; set; }
    public string Data { get; set; }
}

If you like you could mark all entities as protected inside the main ApplicationDbContext, then create additional contexts as needed for each separation of schemas.

They all use the same connection string, however they use separate connections, so do not cross transactions and be aware of locking issues. Generally your designing separation so this shouldn't happen anyway.

Reminder: If you do combine multiple contexts make sure you cut n paste all the functionality in your various RealContexts.OnModelCreating() into your single CombinedContext.OnModelCreating().

I just wasted time hunting down why my cascade delete relationships weren't being preserved only to discover that I hadn't ported the modelBuilder.Entity<T>()....WillCascadeOnDelete(); code from my real context into my combined context.

Inspired by [@JulieLerman 's DDD MSDN Mag Article 2013][1]

	public class ShippingContext : BaseContext<ShippingContext>
{
  public DbSet<Shipment> Shipments { get; set; }
  public DbSet<Shipper> Shippers { get; set; }
  public DbSet<OrderShippingDetail> Order { get; set; } //Orders table
  public DbSet<ItemToBeShipped> ItemsToBeShipped { get; set; }
  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
    modelBuilder.Ignore<LineItem>();
    modelBuilder.Ignore<Order>();
    modelBuilder.Configurations.Add(new ShippingAddressMap());
  }
}

---

public class BaseContext<TContext>
  DbContext where TContext : DbContext
{
  static BaseContext()
  {
	Database.SetInitializer<TContext>(null);
  }
  protected BaseContext() : base("DPSalesDatabase")
  {}
}	

> "If you’re doing new development and you want to let Code First create or migrate your database based on your classes, you’ll need to create an “uber-model” using a DbContext that includes all of the classes and relationships needed to build a complete model that represents the database. However, this context must not inherit from BaseContext." JL

My gut told me the same thing when I came across this design.

I am working on a code base where there are three dbContexts to one database. 2 out of the 3 dbcontexts are dependent on information from 1 dbcontext because it serves up the administrative data. This design has placed constraints on how you can query your data. I ran into this problem where you cannot join across dbcontexts. Instead what you are required to do is query the two separate dbcontexts then do a join in memory or iterate through both to get the combination of the two as a result set. The problem with that is instead of querying for a specific result set you are now loading all your records into memory and then doing a join against the two result sets in memory. It can really slow things down.

I would ask the question "just because you can, should you?"

See this article for the problem I came across related to this design. https://stackoverflow.com/questions/7332920/the-specified-linq-expression-contains-references-to-queries-that-are-associated

Another bit of "wisdom". I have a database facing both, the internet and an internal app. I have a context for each face. That helps me to keep a disciplined, secured segregation.

I want to share a case, where I think the possibility of having multiple DBContexts in the same database makes good sense.

I have a solution with two database. One is for domain data except user information. The other is solely for user information. This division is primarily driven by the EU General Data Protection Regulation. By having two databases, I can freely move the domain data around (e.g. from Azure to my development environment) as long as the user data stays in one secure place.

Now for the user database I have implemented two schemas through EF. One is the default one provided by the AspNet Identity framework. The other is our own implementing anything else user related. I prefer this solution over extending the ApsNet schema, because I can easily handle future changes to AspNet Identity and at the same time the separation makes it clear to the programmers, that "our own user information" goes in the specific user schema we have defined.

In code first, you can have multiple DBContext and just one database. You just have to specify the connection string in the constructor.

public class MovieDBContext : DbContext
{
    public MovieDBContext()
        : base("DefaultConnection")
    {
        
    }
    public DbSet<Movie> Movies { get; set; }
}

Huh, spent quite a lot of time on a problem with separate DB contexts for each DB schema, hope that it will help somebody else...

I recently started working on a project that had one database with 3 schemas (DB first approach), one of them for user management. There was a DB context scaffolded from each separate schema. Of course, users were related to other schemas as well, eg. schema KB had a table Topic, which had "created by", "last modified by" etc. FK to identity schema, table appuser.

These objects were loaded separately in C#, firstly, topic was loaded from 1 context, then users were loaded via user IDs from the other db context - not nice, have to fix this! (similar to https://stackoverflow.com/questions/42301354/using-multiple-dbcontexts-in-the-same-database-with-ef-6)

First, I tried to add missing FK instructions from identity schema to KB schema, to EF modelBuilder in KB DB context. The same as if there was only 1 context, but I separated it to 2.

modelBuilder.Entity<Topic>(entity =>
{
  entity.HasOne(d => d.Creator)
	.WithMany(p => p.TopicCreator)
	.HasForeignKey(d => d.CreatorId)
	.HasConstraintName("fk_topic_app_users");

It didnt work, because kb db context didn't have any information about the user object, postgres returned error relation "AppUsers" does not exist. Select statement didnt have proper information about schema, field names etc.

I almost gave up, but then i noticed a switch "-d" when running dotnet ef dbcontext scaffold. Its short for -data-annotations - Use attributes to configure the model (where possible). If omitted, only the fluent API is used. With this switch specified, object properties were defined not in db context OnModelCreating(), but rather on the object itself, with attributes.

This way, EF got sufficient information for generating a proper SQL statement with proper field names and schemas.

TL;DR: separate DB contexts don't handle relations (FKs) between them well, each context only has information about its own entities. When specifying "-data-annotations" switch on dotnet ef dbcontext scaffold, these informations arent stored in each separate context, but on DB objects themselves.