Entity Framework Core Using multiple DbContexts

Question

I'm having a problem that when I try to access a field in my PartsDbContext I get the following error:

System.Data.SqlClient.SqlException: 'Invalid object name 'fieldName''

It seems that this is because I'm trying to make my PartsDbContext use the same database as my ApplicationDbContext which is used with Identity. I need to know how to setup a 2nd dbcontext to work with EF core that uses/creates a different database.

I've tried creating a 2nd connection string but that gets me this error:

System.Data.SqlClient.SqlException: 'Cannot open database "PartsDb" requested by the login. The login failed. Login failed for user 'DESKTOP-4VPU567\higle'.'

Here's my code:

appsettings.json

"ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=aspnet-PrecisionCustomPC-b14db89e-86ad-4855-a17f-ac64a04339aa;Trusted_Connection=True;MultipleActiveResultSets=true",
    "PartsConnection":  "Server=(localdb)\\mssqllocaldb;Database=PartsDb"
},
"Logging": {
    "IncludeScopes": false,
    "LogLevel": {
        "Default": "Warning"
    }
}

PartsDbContext.cs

public class PartsDbContext : DbContext
{
    public DbSet<PartsViewModels.Tower> Towers { get; set; }
    public DbSet<PartsViewModels.Motherboard> Motherboards { get; set; }

    public PartsDbContext(DbContextOptions<PartsDbContext> options)
        : base(options)
    {
    }
}

Startup.cs

public void ConfigureServices(IServiceCollection services)
{
    // Add framework services.
    services.AddDbContext<ApplicationDbContext>(options =>
         options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

    services.AddIdentity<ApplicationUser, IdentityRole>()
        .AddEntityFrameworkStores<ApplicationDbContext>()
        .AddDefaultTokenProviders();

    services.AddEntityFramework()
        .AddDbContext<PartsDbContext>(options =>
          options.UseSqlServer(Configuration.GetConnectionString("PartsConnection")));

    services.AddMvc();

    services.AddAuthorization(options =>
    {
        options.AddPolicy("RequireAdminRole", policy => policy.RequireRole("Admin"));
    });

    // Add application services.
    services.AddTransient<IEmailSender, AuthMessageSender>();
    services.AddTransient<ISmsSender, AuthMessageSender>();
}

AdminController.cs

[Authorize(Policy = "RequireAdminRole")]
public class AdminController : Controller
{
    private readonly PartsDbContext _context;

    public AdminController(PartsDbContext context)
    {
        _context = context;
    }

    public IActionResult Index()
    {
        return View();
    }

    public IActionResult Towers()
    {
        var model = _context.Towers.ToList();
        return View(model);
    }
}

The line var model = _context.Towers.ToList(); is where the error is showing up.

Once again. I want to setup my PartsDbContext to work with Entity Framework Core in a way that EF-Core will automatically create the database.

Answer 1

It is possible to have your application use multiple contexts with multiple databases purely via configuration. My solution outlined below does have some limitations which I describe at the end. It is overkill for most use cases but if you want to add ad-hoc new services to an existing application with separate databases with a view to spawning them off as separate microservices later, if appropriate, try this:

First, you need to define some config structures that will be populated in your appsettings.json file:

public enum DatabaseType
{
    None = 0,
    SqlServer = 1,
    MySql = 2
}

public class DatabaseConfiguration
{
    public DatabaseType DatabaseType { get; set; }
    public string Name { get; set; } = "";
    public string ConnectionString { get; set; } = "";
}

public class DatabaseSettings
{
    public DatabaseConfiguration[] Databases { get; set; } = Array.Empty<DatabaseConfiguration>();
}

I am including a stripped down repository implementation for simplicity. The key thing to note here is that we have just one DbContext type. A collection of them is registered at startup and the appropriate one is obtained at runtime by the Repository constructor

public interface IEntity { }

public interface IRepository<T> where T : class, IEntity
{
    IQueryable<T> Get(Expression<Func<T, bool>> filter, params string[] includes);
}

public class Repository<T> : IRepository<T> where T : class, IEntity
{
    protected readonly MyDbContext _dbContext;

    public Repository(Dictionary<Type, Func<MyDbContext>> dbContextLookup)
    {
        _dbContext = dbContextLookup[typeof(T)].Invoke();
    }

    public IQueryable<T> Get(Expression<Func<T, bool>> filter, params string[] includes)
    {
        filter ??= (t => true);
        var query = _dbContext.Set<T>().Where(filter);
        foreach (var include in includes ?? [])
        {
            query = query.Include(include);
        }
        return query;
    }
}

public class MyDbContext : DbContext
{
    private readonly HashSet<Type> entityTypes;

    public MyDbContext(DbContextOptions options, IEnumerable<Type> entityTypes) : base(options)
    {
        this.entityTypes = entityTypes.ToHashSet();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        foreach (var type in entityTypes)
        {
            modelBuilder.Entity(type);
        }
    }
}

Finally, in the startup code, we have to bind to the configuration and build the collection of DbContexts

public static class DatabaseRegistrationExtensions
{
    public static IServiceCollection RegisterDatabases(this IServiceCollection services, IConfiguration configuration, HashSet<Type> assemblyMarkers)
    {
        var options = configuration.Get<DatabaseSettings>().Databases.ToDictionary(db => db.Name, BuildContextOptions);
        var registrationMap = BuildRegistrationMap(options.Keys, assemblyMarkers);
        var dbContextLookup = registrationMap.ToDictionary(r => r.Key, r => new Func<MyDbContext>(() => new MyDbContext(options[r.Value], registrationMap.Keys));
        services.AddSingleton(dbContextLookup);
        services.AddTransient(typeof(IRepository<>), typeof(Repository<>)));
        return services;
    }

    private static DbContextOptions BuildContextOptions(DatabaseConfiguration config) =>
        config.DatabaseType switch
        {
            DatabaseType.None => throw new ArgumentException("Invalid Database Type", nameof(config)),
            DatabaseType.SqlSqlServer => new DbContextOptionsBuilder().UseSqlServer(config.ConnectionString).Options,
            DatabaseType.MySql => new DbContextOptionsBuilder().UseMySql(config.ConnectionString, MySqlServerVersion.LatestSupportedServerVersion).EnableDetailedErrors().Options,
            _ => throw new ArgumentException("Unknown Database Type", nameof(config))
        };

    private static Dictionary<Type, string> BuildRegistrationMap(IEnumerable<string> databases, IEnumerable<Type> markers)
    {
        Validate(databases, markers); // code excluded for brevity, checks for empty or inconsistent supplied parameters
        var result = new Dictionary<Type, string>();
        foreach (var marker in markers)
        {
            var entityTypes = GetAssemblyTypes(marker).Where(IsEntityType).ToHashSet();
            foreach (var type in entityTypes)
            {
                var matches = databases.Where(type.FullName.Contains).ToHashSet();
                if (matches.IsNullOrEmpty())
                {
                    throw new ArgumentException("Logging + appropriate message");
                }
                if (matches.Count > 1)
                {
                    throw new ArgumentException("Logging + appropriate message");
                }
                result.Add(type, matches.Single());
            }
        }
        return result;
    }

    private static Type[] GetAssemblyTypes(Type assemblyMarker) => assemblyMarker.Assembly.GetTypes();

    private static readonly Func<Type, bool> IsEntityType = (Type t) => !t.IsInterface && Array.Exists(t.GetInterfaces(), i => i == typeof(IEntity));
}

Limitations

• Every database entity type you intend to use needs to inherit from IEntity
• Every database entity type you intend to use must contain the configured name of exactly one of the databases you configure somewhere in its namespace. Name clashes are a genuine risk if your domain contains repetition (e.g. one db called "Customer" and one called "CustomerManager")
• Due to the implementation of OnModelCreating, each context has all the entities across all your databases registered, although if you only access your data via Repository classes it's safe. I am aware this is far from ideal, readers are invited to comment if they have suggestions for improvement.
• Trickier, to manage database passwords for your ci/cd pipelines, there would be more work to manage secrets compared to a vanilla implementation

Benefits

• This allows you to add up to N databases to your application with minimal work, provided you stay within the constraints listed above
• No need for different DbContext classes and the associated headache of linking different repositories to different contexts
• Support across different database technologies (supported by EF), could be extended to other types besides the two I have tested for

Answer 2

In addition to the prior comments, you can use this way:

dotnet ef migrations add InitialCreate --context MyContext

dotnet ef database update --context MyContext

or

Add-Migration InitialCreate -Context MyContext

Update-Database -Context MyContext

Answer 3

First of all, thanks @Joe Higley answer this questions,I want to add more situation to help more people.

My situation is i'm trying to use EF-Identity and Area to create a admin panel, in my admin area own their own controller/models/views..., also contain a new DBcontext.

There is problems, if you try to context.Database.EnsureCreated(); to initialize DB there will show

System.Data.SqlClient.SqlException: 'Invalid object name 'fieldName''

Ref this link Migrations with Multiple Providers

We can use migrations and use --context to set which DbContext you want to run

In VScode you can run

dotnet ef migrations add InitialCreate --context BlogContext
dotnet ef database update

In Package Management Console you can run

Add-Migration InitialCreate -Context BlogContext
Update-Database

Answer 4

I cannot comment yet, but I would like to add to the answer.

Currently I am working my way through this tutorial: https://learn.microsoft.com/en-us/aspnet/core/tutorials/razor-pages/model?view=aspnetcore-5.0&tabs=visual-studio

But I too started out with ApplicationDbContext for Identity. So, I ran into a similar problem. Your answer helped me out, thanks!

The tutorial however suggests a cleaner way to do this.

1. Add a data model
2. Scaffold the data model!

This step is huge. It creates the Context class, a connection string in appsettings.json, adds the Context in Startup.cs and more. For usage of scaffolding take a look at linked tutorial.

3. Run given commands in the PMC and you are set.
   • Add-Migration init -Context ModelContext
   • Update-Database -Context ModelContext

So, I would suggest to use scaffolding as it does the most work for you.

Answer 5

I figured it out. This mostly came about because I accidentally deleted the database that Identity was using and I needed to figure out how to get it back.

Apparently there's nothing wrong with my connection string the way it is. I just needed to go into the package manager and type these commands in this order:

1. Add-Migration init -Context PartsDbContext
2. Update-Database -Context PartsDbContext

I found this out because that is what I had to do to get my ApplicationDbContext working again and it turns out that this step is done for you when you create a new MVC Core Web Application in Visual Studio using Individual User Authentication.

So basically the steps for adding more DbContexts is to:

1. Create a DbContext Class
2. Create a Connection string for that DbContext in appsettings.json
3. Add the DbContext to your configured services in Startup.cs
4. Setup the DbContext in the controllers that will use it.
5. Open the package manager and run the 2 lines above. (if "-Context" doesn't work try "--context"
6. Run your program and let EntityFrameworkCore take care of the rest.