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

clinqsql

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.

Best Answer

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.

Related Solutions

LINQ To SQL exception: Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains operator

Replace the usages of Any with Contains in your query. eg:

searchTerms.Contains(c.Email)

This should get the result you're looking for. It looks backwards, but it's correct- it'll generate an IN operator for each field inside a Contains with all the elements in searchTerms.

The AddressLine1 part won't work this way- you'll have to loop-generate the comparisons yourself with

c.addressLine1.Contains(...)

Something like PredicateBuilder can be helpful for this.

Sql – How to Join to first row

SELECT   Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM     Orders
JOIN     LineItems
ON       LineItems.LineItemGUID =
         (
         SELECT  TOP 1 LineItemGUID 
         FROM    LineItems
         WHERE   OrderID = Orders.OrderID
         )

In SQL Server 2005 and above, you could just replace INNER JOIN with CROSS APPLY:

SELECT  Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description
FROM    Orders
CROSS APPLY
        (
        SELECT  TOP 1 LineItems.Quantity, LineItems.Description
        FROM    LineItems
        WHERE   LineItems.OrderID = Orders.OrderID
        ) LineItems2

Please note that TOP 1 without ORDER BY is not deterministic: this query you will get you one line item per order, but it is not defined which one will it be.

Multiple invocations of the query can give you different line items for the same order, even if the underlying did not change.

If you want deterministic order, you should add an ORDER BY clause to the innermost query.

Example sqlfiddle

Best Answer

Related Solutions

LINQ To SQL exception: Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains operator

Sql – How to Join to first row

Related Topic