Linq to SQL gives NotSupportedException when using local variables

Question

It appears to me that it matters whether you use a variable to temporary store an IQueryable or not. See the simplified example below:

This works:

List<string> jobNames = new List<string> { "ICT" };
var ictPeops = from p in dataContext.Persons
               where (  from j in dataContext.Jobs
                        where jobNames.Contains(j.Name)
                        select j.ID).Contains(p.JobID)
               select p;

But when I use a variable to temporary store the subquery I get an exception:

List<string> jobNames = new List<string> { "ICT" };
var jobs = from j in dataContext.Jobs
           where jobNames.Contains(j.Name)
           select j.ID;
var ictPeops = from p in dataContext.Persons
               where jobs.Contains(p.JobID)
               select p;

"System.NotSupportedException: Queries with local collections are not supported"

I don't see what the problem is. Isn't this logic that is supposed to work in LINQ?

UPDATE: Yesterday I found the workaround to get 1 query while using multiple variables:

  var jobs = from j in dataContext.Jobs
             where jobNames.Contains(j.Name)
             select j.ID;
  var ictPeops = from p in dataContext.Persons
                 join j in jobs on p.JobID equals j
                 select p;

But still I'm confused. Can anyone shed some light on why the first query didn't work when using a variable?

Answer 1

Let me explain how Linq to SQL works. When you write query in the code, this code is not executed as other .net code and Linq to Objects. This code then is broken down into expression tree and compiled to SQL. If you write everything as single expression it is converted to SQL entirely. When you break to two queries it will be broken into two separate queries. And Linq To SQL can't assemble them.

Answer 2

try converting var jobs to Type of IList

var jobs = (from j in dataContext.Jobs
            where jobNames.Contains(j.Name)
            select j.ID).ToList();

Answer 3

Out of curiosity, does this work? (I'm not a big LINQ-to-SQL dude)

var jobNames = from s in new string[] { "ICT" } 
        select s; 
var jobs = from j in dataContext.Jobs 
       where jobNames.Contains(j.Name) 
       select j.ID; 
var ictPeops = from p in dataContext.Persons 
           where jobs.Contains(p.JobID) 
           select p; 

EDIT: Ok, how about one big query? :)

var ictPeops = 
    from p in dataContext.Persons
        let jobs =
           from j in dataContext.Jobs
           let jobNames = from s in new string[]{"ICT"} select s
           where jobNames.Contains(j.Name)
           select j.ID
    where jobs.Contains(p.JobID)
    select p;

Answer 4

LINQ-2-SQL translates your code into T-SQL. It is able to pass your List of job names over as a parameter easily. But, in your failing query you are trying to join a SQL table (Persons) to a C# object (jobs); this is a complex C# type which cannot be translated into SQL. You probably need to convert jobs into a simple int array before using it in the second query. LINQ-2-SQL might be able to handle that.