Entity Framework: Linq !contains based on table from different database

Question

Using Entity Framework, I'm trying to work around the fact that Linq can't handle joins between tables from different databases. The databases:

DB1.dbo.Table1: Id, XmlText

DB2.dbo.Table2: Id, TimeStamp, ColA, ColB...

I want to find the rows in Table2 where a property in XmlText is set to true, that is, XmlText contains 'xmlProp=true'

In SQL, I would simply write

SELECT *
FROM [DB2].[dbo].[Table2]
INNER JOIN [DB1].[dbo].[Table1] ON [Table1].Id = [Table2].Id
WHERE XmlText like '%xmlProp=true%'

Since I can't use join with Linq (different databases, remember?), I'm doing:

var query = from t1 in db1.Table1
    where db1.Text.Contains('<xmlProp>false</xmlProp>)
    select t1.Id;

var set = new HashSet<Guid>();
foreach (var elem in query)
    set.Add(elem);

var query2 = from t2 in db2.Table2
    where !set.Contains(t2.Id)
    select t2;

I could of course search for 'true' instead, but fewer rows has false so the set will be smaller, thus increasing performance.

Now: is there a better way to do this? The SQL queries that are generated are massive (long list of ID's to avoid...), and it feels stupid to store the ID's in the memory just to pass them back to the SQL server again. Anyone?

Thanks, M

Answer 1

You can either use two DataContexts or you can set up Views and 3 part naming if they are under the same server so that one database can make the call.

But as you said before when you said you can't have two contexts in one query, you can't have them intermingled when the actual execution occurs.

You can use two DataContexts you just have to make sure they are executed and brought to memory before the join, and just join the entities in memory. I know this is not as good as deferring the execution till the actual enumeration occurs especially if you are bringing a lot of information into memory and heavily filtering because iterating over a collection once is much better than repeated passes but this should get you your answer right away.

Just replace the server, database, and table names to whatever is in your environment.

example where you're referencing items from different contexts and it fails:

void Referencing_Both_In_One_Query()
{
    const string sc1 = @"Server=YOURSQLSERVER;Database=DEVDATABASE1;Trusted_Connection=True;";
    const string sc2 = @"Server=YOURSQLSERVER;Database=DEVDATABASE2;Trusted_Connection=True;";

    using (var dc = new DataContext(sc1))
    {
        using (var dc2 = new DataContext(sc2))
        {
            var users1 = dc.GetTable<Users>();
            var users2 = dc2.GetTable<Users>();

            //You'll get some error saying the query contains references to items defined on a different data context.
            var joined = (from u1 in users1
                join u2 in users2 on u1.Login equals u2.Login
                select u2.Login);

            joined.ToList(); //<< Execution happens here when we enumerate. and it's executing across 2 different data contexts.            
        }
    }
}

Here is another example when you enumerate ahead of the join:

void Enumerating_Then_Joining()
{
    const string sc1 = @"Server=YOURSQLSERVER;Database=DEVDATABASE1;Trusted_Connection=True;";
    const string sc2 = @"Server=YOURSQLSERVER;Database=DEVDATABASE2;Trusted_Connection=True;";

    using (var dc = new DataContext(sc1))
    {
        using (var dc2 = new DataContext(sc2))
        {
            var users1 = dc.GetTable<Users>().Select(x => x).ToList();  //<< Enumerate here executing on your datacontext 1
            var users2 = dc2.GetTable<Users>().Select(x => x).ToList(); //<< Enumerate here executing on your datacontext 2

            // now we work on the in memory entities.
            var joined = (from u1 in users1
                        join u2 in users2 on u1.Login equals u2.Login
                        select u2.Login);

            joined.ToList();    
        }
    }
}