.NET.Refactor()

 

One of the key problems with LINQ is you cannot do cross data context joins. Now, there is a good reason why you can’t do this using the standard LINQ framework. You can run into security issues and you can run into potentially strange data results.

Now, typically when you have multiple data contexts you are connecting to multiple databases for each context. There are some cases when you would want to have multiple data contexts for the same database.

In the example I am going to show you how you can have 2 databases, with 2 data contexts and join them together in the same query.

Before we begin, I am going to be setting up some test databases with some tables and test data. This is to show that you can actually have 2 different databases and have 2 different data contexts and join them together.

Step 1:

Creating the database:

Create database DatabaseA
GO
Create Database DatabaseB
GO

 

Now that we have our databases, we can create our tables and insert some dummy data.

Create Table DatabaseA.dbo.TableADatabaseA
(
    PrimaryKeyA INT IDENTITY(1, 1)
    , TableAValue CHAR(1)
)
GO
Create Table DatabaseB.dbo.TableBDatabaseB
(
    PrimaryKeyB INT IDENTITY(1, 1)
    , TableAJoinValue INT
    , TableBValue CHAR(1)
)
GO
INSERT INTO DatabaseA.dbo.TableADatabaseA VALUES ('A')
INSERT INTO DatabaseB.dbo.TableBDatabaseB VALUES (1, 'B')
GO

Okay great. We now have two databases, two tables and a single row in each table to work with.

If we were to run this query in management studio:

 

select
TB.TableBValue from DatabaseB.dbo.TableBDatabaseB TB
INNER JOIN DatabaseA.dbo.TableADatabaseA TA
on TA.PrimaryKeyA = TB.TableAJoinValue

We would get the result of “B” in our query results.

We want to get the same result using LINQ.

Step 2:

In Visual Studio I created a sample project which contains my 2 data contexts:

In my ContextA I have the following table from our DatabaseA:

In my ContextB i have the following table from our DatabaseB:

 

Step 3:

Now that we have our database and our data contexts setup, we need to write some code!

Before we get into the actual working code, let’s look at code that we think would work:

 

var contextA = new ContextADataContext();
var contextB = new ContextBDataContext();
var query = from a in contextA.TableADatabaseAs
                   join b in contextB.TableBDatabaseBs
                     on a.PrimaryKeyA equals b.TableAJoinValue
                        select b;
Console.WriteLine(query.First().TableBValue);

This code looks just fine. However, if you were to execute it, you will get the following error:

 

So, how are we going to fix this?

We are going to create two methods. Each method will be  returning IEnumerable<T>

Let’s create those methods so you can see what I am talking about.

Here is the code for those methods:

static IEnumerable<TableADatabaseA> GetTableA()
        {
            var context = new ContextADataContext();
            return (from t in
                        context.TableADatabaseAs
                    select t).AsQueryable();

        }

        static IEnumerable<TableBDatabaseB> GetTableB()
        {
            var context = new ContextBDataContext();
            return (from t in
                        context.TableBDatabaseBs
                    select t).AsQueryable();
        }

 

As you can see, I am returning an IEnumerable<T> but the query is being returned as AsQueryable(). This is the key, returning AsQueryable(). If you were to return as AsEnumerable() you would get the same error.

Now, for our main query:

            var query = from a in GetTableA()
                        join b in GetTableB()
                        on a.PrimaryKeyA equals b.TableAJoinValue
                        select b;
            Console.WriteLine(query.First().TableBValue);

As you can see, our tables turn into methods.

If you were to execute this you now see this, which is the result we want!

Congratulations! We now have a cross data context JOIN!!

Now, with this you do end up with a drawback. When it executes your queries it will execute them separately and then do the join after the queries execute. Which means you lose the lazy loading.

If you look at SQL profiler you can see this happening when you execute this query:

While this may not be the best practice for SQL, it does provide you with a solution to actually do a JOIN on multiple data contexts.

I hope that this helps you get a good start on how to do a cross data context join.