EF Core and Multiple Databases

Question

I have a legacy system with three databases

1. Vendor
2. CustomCode
3. LogData

Vendor contains control and log data from our Vendors app.

CustomCode contains lots of views and stored procedures that joins to Vendor and LogData

LogData contains results from our CustomCode processes. eg: Daily/Weekly/Monthly summaries and results.

I'm writing a website that will plot data on a map. The list of units is from a view in CustomCode. The Summary record is from LogData, and the individual log points are retrieved from Vendor by a stored proc in CustomCode.

I started with a DbContext for CustomCode, but can't seem to Navigate to properties in a 2nd DbContext to LogData

Can I link navigation properties between objects in different contexts?

Can I have once context with multiple databases connected ?

Please note, this is nothing to do with multi-tenant or multi-schema

Answer 1

Can I link navigation properties between objects in different contexts?

No.

Can I have one context with multiple databases connected?

No.

Suggestion:

If the databases can communicate to each other (ie on same server), which appears to be already done since

CustomCode contains lots of views and stored procedures that joins to Vendor and LogData

then create a stored procedure to perform the desired queries (which can join tables from separate databases).

From there you should be able to expose and execute the procedure from Entity Framework to perform the desired functionality.

This would avoid have multiple contexts and trying to join the data in memory, which can have adverse effects if the data set is large.

Answer 2

No, You cannot link navigation properties between objects in different contexts. A context represents a particular connection or DB. You can try getting data from multiple contexts (DBs) and join them and use in-memory.

Answer 3

Also answered elsewhere (https://stackoverflow.com/a/54347237/861352), but here's the gist:

This actually appears to be a known issue, with a solution in the pipeline (although it hasn't been prioritised yet):

https://github.com/aspnet/EntityFrameworkCore/issues/4019

I did however find an interim solution to this problem, and it's based on two sources:

https://stackoverflow.com/a/26922902/861352 (EF6 solution) https://weblogs.asp.net/ricardoperes/interception-in-entity-framework-core

And here it is:

---

How To Do (Same Server) Cross DB Joins With One EF Core DbContext

---

You'll need to install the Microsoft.Extensions.DiagnosticAdapter Nuget Package

using System;
using System.Data.Common;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.Extensions.DiagnosticAdapter;

namespace Example
{
    public class CommandInterceptor
    {
        [DiagnosticName("Microsoft.EntityFrameworkCore.Database.Command.CommandExecuting")]
        public void OnCommandExecuting(DbCommand command, DbCommandMethod executeMethod, Guid commandId, Guid connectionId, bool async, DateTimeOffset startTime)
        {
            var secondaryDatabaseName = "MyOtherDatabase";
            var schemaName = "dbo";
            var tableName = "Users";

            command.CommandText = command.CommandText.Replace($" [{tableName}]", $" [{schemaName}].[{tableName}]")
                                                     .Replace($" [{schemaName}].[{tableName}]", $" [{secondaryDatabaseName}].[{schemaName}].[{tableName}]");
        }
    }
}

Replace 'MyOtherDatabase', 'dbo' and 'Users' with your Database name, table schema and table name, maybe from a config etc.

Then attach that interceptor to your context.

using System.Diagnostics;
using Microsoft.EntityFrameworkCore.Infrastructure;

var context = new MultipleDatabasesExampleDbContext(optionsBuilder.Options);

// Add interceptor to switch between databases
var listener = context.GetService<DiagnosticSource>();
(listener as DiagnosticListener).SubscribeWithAdapter(new CommandInterceptor());

In my case I put the above in MultipleDatabasesExampleDbContextFactory method.

Now you can just use the context as if you were referencing one database.

context.Customers // Default database defined in connection string
context.Users     // MyOtherDatabase (a different database on the same server)