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?

i was not able to reproduce that. please upload yout Linq to SQL schema and send us link — Andrey, Mar 11 '10 at 14:31

score 5 · Accepted Answer · answered Mar 12 '10 at 15:15

5

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.

answered Mar 12 '10 at 15:15

300 baud

1,672
1
12
18

I don't think you understand the problem. The "complex" c# type your referring to is an IQueryable which source is the same datacontext. When I convert it into an IEnumerable (like an array) it then it works, but linq2sql will execute 2 queries. That is not an option. – nicojs Mar 12 '10 at 15:29
I think the exception that was generated described the problem quite succinctly: "Queries with local collections are not supported". It is a limitation of LINQ-2-SQL. – 300 baud Mar 12 '10 at 15:44
If jobNames is a simple data structure (i.e. List, Array) then the code "where jobNames.Contains(j.Name)" results in SQL that looks like "WHERE Name IN (@p0, @p1, ...)". But since since jobNames is an IQueryable, L2S cannot translate this. Your third example works because the syntax is different: "join j in jobs on p.JobID equals j". L2S stills knows at this point that 'j' refers to the Jobs table so the resulting SQL would look something like: "FROM [Persons] AS [t0] INNER JOIN [Jobs] AS [t1] ON [t0].[JobID] = [t1].[ID]" – 300 baud Mar 12 '10 at 16:23

score 0 · Answer 2 · answered Mar 11 '10 at 14:08

0

try converting var jobs to Type of IList

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

answered Mar 11 '10 at 14:08

Asad

21,468
17
69
94

Nope, this is not what I'm looking for. Of course ToList is gonna work, but than 2 queries are generated. In my business that would mean the second select query would have about 30000 intput parameters and that should not be the solution. – nicojs Mar 11 '10 at 14:18

JerKimball · Answer 3 · 2010-03-12T15:21:20.750

0

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;

edited Mar 12 '10 at 15:21

answered Mar 11 '10 at 19:46

JerKimball

16,584
3
43
55

Nope, same problem. The first part will work, because jobNames is of type IEnumerable and is treated the same as the list of strings I used. Same problem with the selection if ictPeops though.... – nicojs Mar 12 '10 at 14:33
Nope, with your second query I get the same famous System.NotSupportedException. Note that my very first query does work. – nicojs Mar 12 '10 at 15:33
Oh, I saw - I was just curious about the "is supported" overlap between LINQ-to-SQL and "normal" LINQ. – JerKimball Mar 12 '10 at 15:43

score -1 · Answer 4 · answered Mar 11 '10 at 14:05

-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.

answered Mar 11 '10 at 14:05

Andrey

59,039
12
119
163

This is not correct. Because the two queries are both expression trees, LINQ to SQL will produce one SQL statement. That is not the problem here. – Steven Mar 11 '10 at 14:08
"LINQ to SQL will produce one SQL statement" - can you prove that? – Andrey Mar 11 '10 at 14:12
See my update, given linq query generates following query: SELECT [t0].[ID], [t0].[Name], [t0].[JobID] FROM [dbo].[Person] AS [t0] INNER JOIN [dbo].[Job] AS [t1] ON [t0].[JobID] = [t1].[ID] WHERE [t1].[Name] IN (@p0) -- @p0: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [ICT] -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1 – nicojs Mar 12 '10 at 14:37

Linq to SQL gives NotSupportedException when using local variables

4 Answers4