How to use generic collections together with LINQ-to-entities

Question

I'm having trouble converting the following SQL-statement to LINQ-to-entities:

SELECT l.*
FROM locations l
WHERE l.id NOT IN (/* array of ids */)

In LINQ, I would like to see something like (where myCollection is a generic list of items to be excluded):

IQueryable<Location> locationQuery = from l in DataContext.Location
                                     where !myCollection.Contains(l)
                                     select l;

But that won't work because Contains isn't available in LINQ-to-entities (as I see it). In my best/closest LINQ attempt, I have my collection of Locations (a List) and I have a DataContext which retrieves all the existing Locations from the database:

List<Location> Route = new List<Location>();

// Some code to add Location entities from the DB to the Route collection

var innerQuery = from p in Route
                 select p.ID;

IQueryable<Location> locationQuery = from l in DataContext.Location
                                     where !((innerQuery).Any(k => k == l.ID))
                                     select l;

Obviously I want to get all the Locations from the DB which are not in my local collection. However the code will raise a NotSupportedException stating:

Unable to create a constant value of type 'Closure type'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

I have fiddled around with it, using different approaches but I just can't get it to work. Now I have read that LINQ-to-entities isn't capable of high-level comparisons (object level) which might explain the error above.

My actual question is, how do I do in LINQ what I can do so easily in SQL? I just want to select a bunch of entities from the DB, excluding the entities present in the local (non-DB) collection.

Answer 1

Try this post: http://stackoverflow.com/questions/432954/not-in-clause-in-linq-to-entities