Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator

Question

I am using LINQ in my project and my code is:

var SE = from c in Shop.Sections
                    join c1 in obj.SectionObjects on c.SectionId equals c1.SectionId
                    select c;

 dataGridView1.DataSource = SE;

but I face this error in line dataGridView1.DataSource = SE;
error message is:

Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.

Answer 1

You can't use a Join between a SQL source and a local source. You'll need to bring the SQL data into memory before you can join them. In this case, you're not really doing a join since you only take the elements from the first collection, what you want is a select...where...selectid in query, which you can get using the Contains method.

 var SE = Shop.Sections.Where( s => obj.SectionObjects
                                       .Select( so => so.SectionId )
                                       .Contains( s.SectionId ))
                       .ToList();

Translates to

select * from Sections where sectionId in (...)

where the values for the in clause come from the list of ids in the collection of local objects.

Answer 2

You can't join local source to SQL source, but you CAN join SQL source to local, v.v.

var SE = from c1 in obj.SectionObjects
              join c in Shop.Sections on c1.SectionId equals c.SectionId
              select c;

In other words, local source must come first

Answer 3

This should work and be done on the database side (using IN), rather than in memory:

var SE = from c in Shop.Sections 
        where obj.SectionObjects.Select(z => z.SectionId).Contains(c.SectionId)
        select c; 

L2S Profiler is very helpful for these kinds of things - you can compare the different SQL being generated by my solution and other solutions.