The query contains references to items defined on a different data context

Question

I have 2 var from same DBs, after union:

var projectedP1 = P1.Select(x => new Project_test { 
                                     ID_inString = x.ID.ToString(), 
                                     col1 = x.col1, 
                                     col2 = x.col2, 
                                     col3 = x.col3 });
var union = projectedP1.Union(P2);

when P1 alone or P2 alone, everything is fine But when 2 is union, i get this in run-time:

The query contains references to items defined on a different data context.

I tried this similar post, but dun understand. ANyone has any idea?

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

Solution

You can never join objects from two different contexts; because the union is compiled into a database query, it wouldn't know how to execute it since multiple DB's are not supported. The best you can do is to call ToList() on each query separately, which will execute the database queries, and do a LINQ-to-Objects union. This is entirely an iterative process.

Comments

[gan]

Thanks for clarification, LINQ sounds like weird to me

[Jon Skeet]

Well ignoring the LINQ part of things, how would you suggest running a single query using data from two different databases?

[Jon Skeet]

You don't need to call ToList - just AsEnumerable will do: P1.AsEnumerable().Union(P2).

[Brian Mains]

Thanks for the tip... You'd also have to do it on P2 because that would have to be executed against its context, would it not? I guess here since it's the second resultset, not executed within the context of the first operation, it would pass through fine and wouldn't attempt to translate the union as part of its query...

[Jon Skeet]

No, you don't have to do it on P2 because just doing it on P1 means it'll use the implementation in Enumerable, treating P1 as an IEnumerable<T> already.