SQL "not in" syntax for Entity Framework 4.1

Question

I have a simple issue with Entity Framework syntax for the "not in" SQL equivalent. Essentially, I want to convert the following SQL syntax into Entity Framework syntax:

select  ID from    dbo.List where   ID not in (list of IDs) 

Here is a method that I use for looking up a single record:

public static List GetLists(int id) {     using (dbInstance db = new dbInstance())     {         return db.Lists.Where(m => m.ID == id);     } } 

Here is a pseudo-method that I want to use for this:

public static List<List> GetLists(List<int> listIDs) {     using (dbInstance db = new dbInstance())     {         return db.Lists.Where(**** What Goes Here ****).ToList();     } } 

Can anyone give me pointers as to what goes in the Where clause area? I read some forums about this and saw mention of using .Contains() or .Any(), but none of the examples were a close enough fit.

Answer 1

Give this a go...

public static List<List> GetLists(List<int> listIDs) {     using (dbInstance db = new dbInstance())     {         // Use this one to return List where IS NOT IN the provided listIDs         return db.Lists.Where(x => !listIDs.Contains(x.ID)).ToList();          // Or use this one to return List where IS IN the provided listIDs         return db.Lists.Where(x => listIDs.Contains(x.ID)).ToList();     } } 

These will turn into approximately the following database queries:

SELECT [Extent1].* FROM [dbo].[List] AS [Extent1] WHERE  NOT ([Extent1].[ID] IN (<your,list,of,ids>)) 

or

SELECT [Extent1].* FROM [dbo].[List] AS [Extent1] WHERE  [Extent1].[ID] IN (<your,list,of,ids>) 

respectively.