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.

Welcome to LINQ-to-Entities, the replacement for LINQ-to-SQL that isn't half as functional... — Jonathan Rupp, Apr 28 '09 at 22:42
I raised a similar conversion issue here; http://stackoverflow.com/questions/4093592/how-to-lookup-entities-in-entityframework-4-by-a-finder-interface-works-in-linq which I have not yet had any luck in solving. It appears there are many things you can do in LinqToSql that you cannot do in EF. It's very annoying / disappointing. Your question is a little bit different but the end result is similar. I hope you have more success than I did. — Joshua Hayes, Nov 29 '10 at 03:37

score 2 · Accepted Answer · edited May 23 '17 at 12:08

2

Try this post: "NOT IN" clause in LINQ to Entities

edited May 23 '17 at 12:08

Community

1
1

answered Apr 28 '09 at 22:15

Lukasz

8,710
12
44
72

Thank you for that link, I have found my way on to http://blogs.msdn.com/phaniraj/archive/2008/07/17/set-based-operations-in-ado-net-data-services.aspx. I might get it working that way, but sheesh (no offence ofcourse!), isn't there a less complex way to do something this trivial? – pyrocumulus Apr 28 '09 at 22:45

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

1 Answers1