Archive for September, 2008

Overcoming LINQ to SQL’s limitations with joining to local sequences, large and small

If you’ve used LINQ to SQL a lot you’ve probably encountered this before:

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

This happens when attempt to join a table to a local array or list in your LINQ query. When I first saw this error I thought it was the stupidest limitation in LINQ. And I kind of still do considering how simple it is to fix, I think the LINQ to SQL guys should have been able to abstract the problem away somehow.

The way you overcome it (as the error suggests) is by using the Contains() extension method. But you have to use it in reverse to what I think is intuitive and use the Contains() on the local array passing in the Table as the parameter.

Example

string[] driveNames = new string[] { "Drive1", "Drive2" };
var q = from eachDrive in context.Drives
        where driveNames.Contains(eachDrive.DriveName)
        select eachDrive;

EDIT: I previously had written a function to abstract this away, but it seems it is not possible to do this and preserve the deferred execution.

Hope this helps someone out!

1 Comment