EF Core and Multiple Databases

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)

In EF Core 5.0 new features, It is now easier to create a DbContext instance without any connection or connection string. Also, the connection or connection string can now be mutated on the context instance. This feature allows the same context instance to dynamically connect to different databases.

Reference: https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-5.0/whatsnew#use-a-c-attribute-to-indicate-that-an-entity-has-no-key

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.

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.