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!

Advertisements
  1. #1 by Dulari on December 18, 2012 - 11:37 am

    What if you want to join on multiple columns?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: