Why Does LINQ to SQL Throw a 'Local Sequence Usage Restriction' Error When Joining with Local Collections?

LINQ to SQL Error: Local Sequence Usage Restriction

In LINQ to SQL, query operators cannot utilize local sequences except for the Contains() operator. This error often arises when attempting to perform joins between SQL sources and local sources.

The provided code snippet demonstrates a join between the Shop.Sections table and a local collection, obj.SectionObjects. However, this operation is invalid, resulting in the error: "Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator."

Solution: Data Retrieval Before Join

To resolve this error, retrieve the SQL data into memory before attempting the join. In this scenario, we do not perform a true join but rather a "select...where...selectid in" query. This can be achieved using the Contains() method:

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

This query translates to:

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

where the list of IDs in the in clause is derived from the local object collection. This approach ensures that all data used in the query is part of the SQL source, resolving the error.

The above is the detailed content of Why Does LINQ to SQL Throw a 'Local Sequence Usage Restriction' Error When Joining with Local Collections?. For more information, please follow other related articles on the PHP Chinese website!