Linq to SQL throws NotSupportedException when using local variables

Question

It seems to me that it is important whether you use a variable for temporary storage of IQueryable or not. See a simplified example below:

It 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 temporarily store a 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 do not understand what the problem is. Isn't that logic supposed to work in LINQ?

UPDATE: Yesterday I found a workaround for getting 1 query when 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 request did not work when using a variable?

Answer 1

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

Answer 2

try converting var jobs to IList type

 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 dude to LINQ-to-SQL)

 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: Okay, how about one big request? :)

 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

Let me explain how Linq to SQL works. When you write a query in code, this code is not executed as other .net code and Linq for objects. This code is then split into an expression tree and compiled into SQL. If you write everything as one expression, it is completely converted to SQL. When you switch to two queries, it splits into two separate queries. And Linq To SQL cannot assemble them.