Also answered elsewhere (https://mcmap.net/q/830477/-performing-projections-on-multiple-databases-with-only-one-dbcontext), 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://mcmap.net/q/496640/-cross-database-querying-in-ef (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)
