CODEWITHSUNDEEP
c#linq-to-sql
nicojs
nicojs

Reputation: 2055

Linq to SQL gives NotSupportedException when using local variables

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?

Upvotes: 5

Views: 5406

Answers (4)

JerKimball
JerKimball

Reputation: 16934

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;

Upvotes: 0

300 baud
300 baud

Reputation: 1672

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.

Upvotes: 5

Asad
Asad

Reputation: 21938

try converting var jobs to Type of IList

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

Upvotes: 0

Andrey
Andrey

Reputation: 60105

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.

Upvotes: -1

Related Questions