Join List Data from two separate Data Contexts

Question

I have been searching for the solution to this problem and this is what I have so far:

var ProductInfo = (from p in twd.Products
                               orderby p.PC
                               where p.DELMARK == active
                               select p).AsEnumerable();
        var BuyersData =
            (from x in db.MinimumProductInfo
             where x != null
             orderby x.ItemCode, x.Region
             let pnote =
                 (from pn in db.ProductNotes
                  where pn != null
                  where x.MinimumProductInfoID == pn.MinimumProductInfoID
                      && pn.NoteTypeFlag == "p"
                  orderby pn.NoteDate descending
                  select pn).FirstOrDefault()
             let cnote =
                 (from c in db.ProductNotes
                  where c != null
                  where x.MinimumProductInfoID == c.MinimumProductInfoID
                       && c.NoteTypeFlag == "c"
                  orderby c.NoteDate descending
                  select c).FirstOrDefault()
             let product =
                 (from p in ProductInfo
                  where x.ItemCode == p.PC
                  select p).FirstOrDefault()
             select new ProductInfoWithNoteList
             {
                 MinimumProductInfoID = x.MinimumProductInfoID,
                 ItemCode = x.ItemCode,
                 EquivCode = x.EquivCode,
                 Description = product.PDESC,
                 MinimumOnHandQuantity = x.MinimumOnHandQuantity,
                 MaximumOHandQuantity = x.MaximumOHandQuantity,
                 MinimumOrderQuantity = x.MinimumOrderQuantity,
                 LeadTimeInWeeks = x.LeadTimeInWeeks,
                 Region = x.Region,
                 Comment = cnote.ItemNote,
                 PermanentNote = pnote.ItemNote
             }).ToArray();

It looks correct but I am getting an error,

'The specified LINQ expression contains references to queries that are associated with different contexts.'

What this code is supposed to do is pull out all the active product codes from the first table using the twd datacontext then use data from that database in the db.MinimumProductInfo table. The reason they have 2 separate data contexts are they are completely different databases, the first is our ERP and the second is one that we are building in house.

What am I missing? I know it is possible to do this by separating the two datacontexts then adding them together because I have seen it done with single instances but I cannot find how to do it with list data.

The error is clear: you cannot mix 2 queries from different contexts. One is `twd` and one is `db` — CodingYoshi, Oct 13 '17 at 00:47
@CodingYoshi, I know what the error says and means but I know there are workarounds for it as well. I just cannot figure out the workaround. — djblois, Oct 13 '17 at 00:48
`twd.Products` & `db.MinimumProductInfo` use different `DbSet` and data context, you can't combine both queries without materializing one of them as `IEnumerable` first. — Tetsuya Yamamoto, Oct 13 '17 at 00:48
@TetsuyaYamamoto, isn't that what I did when I cast it .AsEnumurable? — djblois, Oct 13 '17 at 00:50
Well, seems you're performing cross join with materialized context & `IQueryable` in `select` statement which EF doesn't support, can you have a try to materialize all entities in both contexts like `db.MinimumProductInfo.AsEnumerable()` or `db.ProductNotes.AsEnumerable()`? — Tetsuya Yamamoto, Oct 13 '17 at 01:07
Yep. The queries are executed server-side, so that for cross join with different data contexts you need to use *LINQ to Objects* instead of *LINQ to Entities* by materializing all contexts and merge them. — Tetsuya Yamamoto, Oct 13 '17 at 01:10
You can make two separate calls to two separate databases and than join the results in memory. — Robert, Oct 13 '17 at 07:05

score 0 · Answer 1 · answered Oct 13 '17 at 01:15

Instead of this:

let product =
             (from p in ProductInfo
              where x.ItemCode == p.PC
              select p).FirstOrDefault()
         select new ProductInfoWithNoteList
         {
             MinimumProductInfoID = x.MinimumProductInfoID,
             ItemCode = x.ItemCode,
             EquivCode = x.EquivCode,
             Description = product.PDESC,
             MinimumOnHandQuantity = x.MinimumOnHandQuantity,
             MaximumOHandQuantity = x.MaximumOHandQuantity,
             MinimumOrderQuantity = x.MinimumOrderQuantity,
             LeadTimeInWeeks = x.LeadTimeInWeeks,
             Region = x.Region,
             Comment = cnote.ItemNote,
             PermanentNote = pnote.ItemNote
         }).ToArray();

Try this by removing the let product clause and not filling the properties associated with ProductInfo because we will do that afterwards (See I have commented out the Description property):

select new ProductInfoWithNoteList
{
    MinimumProductInfoID = x.MinimumProductInfoID,
    ItemCode = x.ItemCode,
    EquivCode = x.EquivCode,
    //Description = product.PDESC,
    MinimumOnHandQuantity = x.MinimumOnHandQuantity,
    MaximumOHandQuantity = x.MaximumOHandQuantity,
    MinimumOrderQuantity = x.MinimumOrderQuantity,
    LeadTimeInWeeks = x.LeadTimeInWeeks,
    Region = x.Region,
    Comment = cnote.ItemNote,
    PermanentNote = pnote.ItemNote
}).ToArray();

Now that you have your BuyersData and ProductInfo in memory, set the Description property or all the items in BuyersData:

foreach(var thisBuyerData in BuyersData)
{
    var thisPi = ProductInfo.SingleOrDefault(x => x.PC == thisBuyerData.ItemCode);

    thisBuyerData.Description = thisPi?.PDESC;
}

CodingYoshi, off the top of your head can you think of any ways to speed this up? This is a really slow method. — djblois, Oct 13 '17 at 12:41
@djblois If you are sure that is the slow part then make `ProductInfo` a dictionary and make the key `PC` — CodingYoshi, Oct 13 '17 at 16:14
Thank you, I have never used a dictionary before. How would I convert this in to a dictionary? — djblois, Oct 13 '17 at 16:28
@djblois `var dic = ProductInfo.ToDictionary(x => x.PC);`. Make sure you do this outside the loop (obvious but just saying). Then in your loop look up the item in the dictionary. See my answer [here](https://stackoverflow.com/a/43299245/4228458) how to do that. — CodingYoshi, Oct 13 '17 at 20:33

Join List Data from two separate Data Contexts

1 Answers1