Using two different data context in a LINQ JOIN Query

Question

I went through the many questions that were asked regarding this and tried to find solution but no luck. So here is my situation:

private IQueryable<tblB> MT;
var IDs = (from z in db1.tblA
           where z.TA == User.Identity.Name
           select z).ToArray();

MT = from s in db2.tblB
     join a in IDs on s.BP equals a.BP                                  
     select new tblB() { LastName = s.LastName}); 

return View(MT.ToPagedList(pageNumber, pageSize));

I'm getting exception at the return statement - $exception {"Unable to create a constant value of type 'tblA'. Only primitive types or enumeration types are supported in this context."} System.NotSupportedException

When I debug IDs array, I see it has data from tblA but 2nd query with join doesn't seem to work. What mistake am I making. Help!!!

Shouldn't you be using db1.tblA.AsEnumerable() & db1.tblB.AsEnumerable()? — jdweng, Jun 12 '17 at 15:00

score 3 · Accepted Answer · answered Jun 12 '17 at 14:33

3

You need to use Contains in order to generate and IN sql clause:

First, change the first query to return the primitive data you need:

var IDs = (from z in db1.tblA
           where z.TA == User.Identity.Name
           select z.BP).ToArray();

Then use that in-memory list in the second query:

MT = from s in db2.tblB
     where IDs.Contains(s.BP)                                
     select new tblB() { LastName = s.LastName});

By the way, this is not a 2 contexts operations. You're loading data from the first context into memory (notice the .ToArray()) and then using these in-memory data to query the second context.

answered Jun 12 '17 at 14:33

Zein Makki

27,184
5
44
56

Now I get the exception at return line {"The entity or complex type 'tblB' cannot be constructed in a LINQ to Entities query."} – Jun 12 '17 at 14:48
@citm09 This is another issue. that is not related to your original question and here is an answer to it : https://stackoverflow.com/a/12916245/3185569 – Zein Makki Jun 12 '17 at 15:04
I was creating a new new tblB object which was giving me that exception. Once I removed that exception went away. Thanks you so much. I was stuck for hours and didn't know what to do. – Jun 12 '17 at 15:17

score 0 · Answer 2 · answered Jun 12 '17 at 14:55

So you want as a result all elements of tblB that have a property BP equal to at least one of the BP properties of the elements in IDs.

The problem is that after ToList() IDs is in local memory, while your db1.tblB is on the database. You have to bring both sequences to the same platform, either both to your database, or both to local memory. Whatever is more efficient depends on the actual sizes of the sequences and of the results

Use Contains if you want to perform the query on database side. The complete list of IDs will be transferred to the database, where the query will be executed and the results will be transferred to local memory.

Use this method if IDs is fairly short and your result is not almost the complete tblA

var result = db2.tblB
   .Where(elementOfTableB => IDs.Contains(elementOftableB);

No need to create a new tblB object, apparently you want the complete tblB object.

Use AsEnumerable if you expect that there are a lot of IDs in comparison to the number of element in tblB. Transferring the IDs to the database would take considerably more time than transferring the complete tblB to local memory.

db2.TblB                              // take all tblBelements
    .AsEnumerable()                   // bring to local memory
    .join(IDs,                        // join with list IDs
       tblBElement => tblBElement.BP  // take from each tblBElement the BP
       idElement => idElement.BP      // take from each ID the BP
       (tblBElement, idElement) =>    // when they match take the two records
                                      // and get  what you want. 
       tblBElement);                  // in this case the complete tblBElement

Using two different data context in a LINQ JOIN Query

2 Answers2