Accessing multiple databases from the same DbContext in EF Core
Image from Pexels by mentatdgt

Accessing multiple databases from the same DbContext in EF Core

Multiple database access from the same DbContext in Entity Framework Core 5

It is not uncommon to have a requirement to access tables or views in different databases in the same query. In order to be able to do your joins on tables or views in different databases you need to do it in the same connection instance which is bound to DbContext, therefore this access as to be accomplished from the same DbContext instance.

Let try to see what is the obstacle of doing tis with EF Core in a sample project which will access two databases in order to execute query and return records from the database. I will use a simple ASP.NET Core application with EF Core that connects to Database1 and using the same login in SQL Server, accesses table Planets in Database2.

 Database

We'll set the connection to Database1 in connection string in config.json

{
  "ConnectionStrings": {
    "Database1": "data source=.;initial catalog=Database1;user id=sa;password=sa;MultipleActiveResultSets=True;"
  }
}
    

Just to make things faster I will skip data scaffolding from existing database and just manually configure the entities and the db context. I'll start with an entity class for the Planets table

using System;

namespace MulitpleDb.Sample.Data
{
    public class Planet
    {
        public int Id { get; set; }
        public String Name { get; set; }
    }
}

    

Before we attach the entity to the DbContext we need to configure it. It can be done via attributes but I prefer fluent API configuration as it gives you more control over how entities will be treated

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace MulitpleDb.Sample.Data
{
    public class PlanetsConfiguration : IEntityTypeConfiguration<Planet>
    {
        public void Configure(EntityTypeBuilder<Planet> builder)
        {
            builder.ToTable("Planets","Database2.dbo");
            builder.HasKey(e => e.Id);
            builder.Property(a => a.Name).IsRequired();
        }
    }
}

    

You notice that I used ToTable method overload that takes schema as a String parameter.

Overload

 

The intention of this is to tell EF Core that we do not want to access table Planets directly but rather with full [database].[schema].[table] path in sql query EF Core will generate under the hood.

Now we are ready to configure DbContext for Database1. Because I actually want to see what is the query that will be executed against the SQL Server, I will inject ILoggerFactory into proper DbContext constructor.

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace MulitpleDb.Sample.Data
{
    public class Database1Context : DbContext
    {
        private readonly ILoggerFactory _loggerFactory;
        public Database1Context(
            DbContextOptions<Database1Context> options,
            ILoggerFactory loggerFactory) : base(options)
        {
            _loggerFactory = loggerFactory;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (optionsBuilder.IsConfigured) return;
            optionsBuilder.UseLoggerFactory(_loggerFactory);
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.ApplyConfiguration<Planet>(new PlanetsConfiguration());
        }

        public DbSet<Planet> Planets { get; set; }
    }
}

    

The final part is to wire up the DbContext to dependency injection so that we can use it in the controller

 public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<Database1Context>(options =>
            {
                options.UseSqlServer(Configuration.GetConnectionString("Database1"));
            });
            services.AddControllers();
            services.AddSwaggerGen(c =>
            {
                c.SwaggerDoc("v1", new OpenApiInfo { Title = "MulitpleDb.Sample", Version = "v1" });
            });
        }
    
Note

Since I am using ASP.NET Core 5, Swagger is already in place so that we can easily test our API endpoints

In the end we just try to get the data from the Databse2, table Planets

namespace MulitpleDb.Sample.Controllers
{
    [ApiController]
    [Route("[controller]")]
    public class PlanetsController : ControllerBase
    {
        
        readonly ILogger<PlanetsController> _logger;
        readonly Database1Context _database1Context;
        public PlanetsController(
            Database1Context database1Context,
            ILogger<PlanetsController> logger)
        {
            _database1Context = database1Context;
            _logger = logger;
        }

        [HttpGet]
        public async Task<IEnumerable<String>> Get()
        {
            return await _database1Context.Planets.AsNoTracking().Select(p=>p.Name).ToArrayAsync();
        }
    }
}
    

Unfortunately the call to this endpoint blows up with an exception, but since we injected the ILoggerFactory into our DbContext instance we can easily see what is the problem.

Console

This is because EF Core does not understand that we actually want to go to Database2 first and then to dbo schema. Obviously we need to do something about the query before it executes against SQL Server instance.

We can manipulate the query in EF Core before it gets executed by Using Diagnostic Listeners in EF Core. If you have a quick look at this article, you can quickly figure out ow to add interceptor to mutate the query before execution and since it is a simple string down the line, Replace method will be enough.

namespace MulitpleDb.Sample.Data
{
    public class CommandInterceptor : IObserver<KeyValuePair<string, object>>
    {
        public void OnCompleted()
        {
        }

        public void OnError(Exception error)
        {           
        }

        public void OnNext(KeyValuePair<string, object> value)
        {
            if (value.Key == RelationalEventId.CommandExecuting.Name)
            {
                var command = ((CommandEventData)value.Value).Command;
                command.CommandText = command.CommandText.Replace(
                    "[Database2.dbo].",
                    "[Database2].[dbo].");
            }
        }
    }
}
    

To put the interceptor into work, we need to add a listener first

namespace MulitpleDb.Sample.Data
{
    public class GlobalListener : IObserver<DiagnosticListener>
    {
        private readonly CommandInterceptor _commandInterceptor = new CommandInterceptor();
        public void OnCompleted()
        {
        }

        public void OnError(Exception error)
        {            
        }

        public void OnNext(DiagnosticListener value)
        {
            if (value.Name == DbLoggerCategory.Name)
                value.Subscribe(_commandInterceptor);
        }
    }
}
    

Finally we just need to subscribe our listener and that can be done in ConfigureServices method in Startup class

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<Database1Context>(options =>
            {
                options.UseSqlServer(Configuration.GetConnectionString("Database1"));
            });
            DiagnosticListener.AllListeners.Subscribe(new GlobalListener());

            services.AddControllers();
            services.AddSwaggerGen(c =>
            {
                c.SwaggerDoc("v1", new OpenApiInfo { Title = "MulitpleDb.Sample", Version = "v1" });
            });
        }
    

Let's try to invoke our method from Swagger

Swagger

Disclaimer

Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.


About the author

DEJAN STOJANOVIC

Dejan is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform including ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects

CONNECT WITH DEJAN  Loginlinkedin Logintwitter Logingoogleplus Logingoogleplus

JavaScript

read more

SQL/T-SQL

read more

Umbraco CMS

read more

PowerShell

read more

Comments for this article