Cross database querying in EF

c# cross-database entity-framework sql-server

Question

Is there any way to implement cross database querying in Entity Framework? Let's imagine I've two Entities User and Post, User entity is in database1 and Post is in database2, which means those entities are in separate databases. How should I get user's posts in Entity Framework ?

1
18
1/27/2018 6:03:57 AM

Accepted Answer

EF context does not support cross database queries. You need to expose posts in database1 through SQL View (or synonym) and use it as part of that database.

24
1/15/2013 4:09:50 PM

Popular Answer

I know this is an old question, but this is actually possible. If the databases are on the same server, then all you need to do is use a DbCommandInterceptor.

As an example, if I attach a DbCommandInterceptor to MyContext, I can intercept all command executions and replace the specified table(s) in the query with my full-db paths.

public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
    // Here, I can just replace the CommandText on the DbCommand - but remember I
    // want to only do it on MyContext
    var context = contexts.FirstOrDefault() as MyContext;
    if (context != null)
    {
        command.CommandText = command.CommandText
            .Replace("[dbo].[ReplaceMe1]", "[Database1].[dbo].[Customers]")
            .Replace("[dbo].[ReplaceMe2]", "[Database2].[dbo].[Addresses]")
            .Replace("[dbo].[ReplaceMe3]", "[Database3].[dbo].[Sales]");
    }

    base.ReaderExecuting(command, interceptionContext);
}

The nice thing also about this approach is that the EF Model Mapping still works properly and respects column attributes, requires no views, and requires no stored procedures.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow