Cross database querying in EF

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 ?

possible duplicate of [Entity Framework - how to manage tables in different databases but on the same server?](http://stackoverflow.com/questions/11544516/entity-framework-how-to-manage-tables-in-different-databases-but-on-the-same-s) — ken2k, Jan 15 '13 at 16:08
Yes I exposed Posts in database1, but I don't understand, what do you mean **through SQL View or alias** ? — Saber Amani, Jan 15 '13 at 16:08

score 30 · Answer 1 · edited Aug 22 '19 at 14:53

30

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.

edited Aug 22 '19 at 14:53

Aaroninus

1,062
2
17
35

answered Nov 14 '14 at 04:29

long2know

1,280
10
9

1

Using EntityFramework v6. It doesn't recognize ReaderExecuting event: 'myContext.ReaderExecuting(DbCommand, DbCommandInterceptionContext)': no suitable method found to override – Mike W Aug 30 '22 at 19:45
1

This is a method on DbCommandInterceptor .. not DbContext. You have to create the interceptor to "intercept" when reading from the DB occurs. https://learn.microsoft.com/en-us/ef/ef6/fundamentals/logging-and-interception – long2know Sep 09 '22 at 23:41
I'm sorry, not going to believe that that works until I see it in action (and this incomplete example isnt that). The querying fails not because the generated SQL code is wrong (which you are updating here), but because application level code in EF checks that you are doing cross db queries (because you are referencing objects from two different contexts) and throws an Exception even before SQL generation has started. – Reinis Aug 11 '23 at 11:43
The code snippet is from production code (albeit from nearly a decade ago using legacy EF 6.x and with names changed to protect the innocent). You are free to believe as you will and be wrong. I'm not here to change your mind wrt to a post from 2014. – long2know Aug 25 '23 at 20:53

score 26 · Accepted Answer · answered Jan 15 '13 at 16:09

26

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.

answered Jan 15 '13 at 16:09

Ladislav Mrnka

360,892
59
660
670

2

Of course this will not work at all if the database you wish to join on is not local. Link server joins perform poorly as well. I wish that the Context would honor the Database Ownership Chaining configuration option as this seems like a basic authentication issue that could be easily overcome. – ewahner Mar 10 '14 at 20:35

score 11 · Answer 3 · answered Jan 15 '13 at 16:15

You can use ExecuteStoreQuery, like:

var myOb = context.ExecuteStoreQuery<PlainOldClrObject>(
        @"select  * 
          from    db1.dbo.table1 t1
          join    db2.dbo.table2 t2
          on      t2.t1_id = t1.id
          where   t1.id  = {0}",
        table1Id).FirstOrDefault();

You'd have to define a PlainOldClrObject class with the columns as properties with getters/setters, like:

class PlainOldClrObject
{
    public int Id ( get; set; }
    public int Name ( get; set; }
    ...
}

score 5 · Answer 4 · edited May 23 '17 at 12:10

5

No, you can't. You will have to create to contexts and do the joining your self. See here.

You could resolve to database trickery, creating a view in one database the reflects a table in the other one.

edited May 23 '17 at 12:10

Community

1
1

answered Jan 15 '13 at 16:08

zmbq

38,013
14
101
171

1

Might be acceptable if the db is on the same host and the data sets are small, but in general, client side joins perform badly. – Andomar Jan 15 '13 at 16:10

Cross database querying in EF

4 Answers4

Linked