How to join two different databases' tables in C# with Linq?

Question

I try to join two different databases tables in c# but it gives me an error how can I handle that ?

this is my query:

 var list = (from h in db.database1.AsEnumerable()
                        join j in NV_DB.database2.AsEnumerable()
                        on h.Creation_Date equals j.Creation_Date
                        where j.Ship_Status == 3 && h.CustomerNo == CustomerNo
                        select new
                        {
                            shipName = h.ShipName,
                            creationDate = j.Creation_Date,
                            endingDate = j.Ending_Date
                        }
                        ).ToList();

if I do like this it gives me System.OverflowException error. But when I run this in sql, it gives me just 30 records*

it gives me this error if I remove AsEnumarable(): System.NotSupportedException: 'The specified LINQ expression contains references to queries that are associated with different contexts.' — rnn, Feb 17 '20 at 08:08

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

You need to remove °AsEnumerable`. While it does not run the sql, when you use it in the where it actually brings the entire tables in memory and then performs the job where part of your query

Your answer is basically the first comment in the accepted answer here: Am I misunderstanding LINQ to SQL .AsEnumerable()?

While AsEnumerable doesn't evaluate the query at the time that it's called , it definitely has an effect. Anything further called on the query will be evaluated using LINQ to objects, so you can't compose additional elements onto the query (another Where or an OrderBy or anything of that nature) that will become part of the SQL statement.

In depth explanation here: https://www.codeproject.com/Articles/732425/IEnumerable-Vs-IQueryable

While querying data from database, IEnumerable executes select query on server side, load data in-memory on client side and then filter data. Hence does more work and becomes slow.

While querying data from database, IQueryable executes select query on server side with all filters. Hence does less work and becomes fas

it gives me this error if I remove it: System.NotSupportedException: 'The specified LINQ expression contains references to queries that are associated with different contexts.' — rnn, Feb 17 '20 at 08:09
Check this answer here: https://stackoverflow.com/questions/352949/linq-across-multiple-databases or https://www.codeproject.com/Articles/23303/LinqToSQL-Query-Multiple-Databases-in-One-LINQ-Exp — Athanasios Kataras, Feb 17 '20 at 08:14
@hakan It helps to Google for the error message https://stackoverflow.com/questions/7332920/error-the-specified-linq-expression-contains-references-to-queries-that-are-as — oleksa, Feb 17 '20 at 09:12

score 0 · Answer 2 · answered Feb 17 '20 at 15:32

To debug this, start dividing your statements into smaller steps:

var list1 = db.database1.AsEnumerable().ToList();
var list2 = NV_DB.database2.AsEnumerable().ToList();

var joinResult = list1.Join(list2,        // join list1 and list2
    list1Row => list1Row.CreationDate,    // from every row in list1 take the CreationDate
    list2Row => list2Row.CreationDate,    // from every row in list2 take the CreationDate

    (list1Row, list2Row) => new           // when they match, make one new object
    {
        // You only need the following properties:
        ShipName = list1Item.ShipName,
        CreationDate = list2Item.CreationDate,
        EndingDate = list2Item.EndingDate,

        ShipStatus = list2Item.ShipStatus,
        CustomerNo = list1Item.CustomerNo,
    })
    .ToList();

var whereResult = joinResult
    .Where(joinedRow => joinedRow.ShipStatus == 3
                     && joinedRow.CustomerNo == customerNo)
    .ToList();

var selectResult = whereResult.Select(whereResultRow => new
{
    ShipName = whereResultRow.ShipName,
    CreationDate = whereResultRow.CreationDate,
    EndingDate = whereResultRow.Ending_Date,
})
.ToList();

This is executed completely as enumerable (in your local process, not by the database management system). My guess would be that this runs smoothly.

Now combine thw first few statements:

var joinResult = db.database1.AsEnumerable()
    .Join(NV_DB.database2.AsEnumerable(),    // join list1 and list2
    list1Row => list1Row.CreationDate,       // from every row in list1 take the CreationDate
    list2Row => list2Row.CreationDate,       // from every row in list2 take the CreationDate

    (list1Row, list2Row) => new              // when they match, make one new object
    {
        // You only need the following properties:
        ShipName = list1Item.ShipName,
        CreationDate = list2Item.CreationDate,
        EndingDate = list2Item.EndingDate,

        ShipStatus = list2Item.ShipStatus,
        CustomerNo = list1Item.CustomerNo,
    })
    .ToList();

When this works, add the Where:

var whereResult = db.database1.AsEnumerable()
    .Join(NV_DB.database2.AsEnumerable(), ...)
    .Where(joinedRow => joinedRow.ShipStatus == 3
                     && joinedRow.CustomerNo == customerNo)
    .ToList();

Etc.

Using your debugger, you'll find the problem within a few minutes (depending on your compilation time). My guess is that it is within your join.

How to join two different databases' tables in C# with Linq?

2 Answers2