Two LINQ data contexts proving a problem

Question

I'm getting this error when using LINQ2SQL:

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

Here's the code:

    var instances = (from i in context.List
                     join j in context.CatsList on i.ListID equals j.ListID
                     join c in context.Cats on j.CatID equals c.CatID
                     where c.SID == Current.SID
                     orderby i.Title
                     select i).Distinct();

The problem, as far as I can ascertain, is that the Current object is actually a LINQ2SQL object returned from a property executing a different LINQ statement.

So, therefore, LINQ2SQL doesn't like executing a query on the database where the query has to be built from one LINQ statement including another statement's result.

My problem with that is that (I'll try to summarise the issue here) the Current object is retrieved using the same context as the query above and ultimately the Current.SID should simply resolve to an int, so what is the compiler's problem with executing it?

In short, why is it not possible to execute a LINQ query using a previous query's returned object as an argument?

Answer 1

This is a solution to the issue, rather than a direct answer of your final question, but you can probably get by with:

var sid = Current.SID;
var instances = (from i in context.List
                 join j in context.CatsList on i.ListID equals j.ListID
                 join c in context.Cats on j.CatID equals c.CatID
                 where c.SID == sid
                 orderby i.Title
                 select i).Distinct();