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

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.