LINQ to SQL where ID not in some_list

Question

I'm trying to make a LINQ to SQL statement which filters results where the ID is not in some list of integers. I realise the .contains() method cannot be used in Linq to SQL but for the purposes of explaining what I'd like to do, here's what I'd like to do:

nextInvention = (from inv in iContext.Inventions
                    where !historyList.Contains(inv.Id)
                    orderby inv.DateSubmitted ascending
                    select inv).First<Invention>();

Any idea how I might go about doing this?

Thanks!

Answer 1

Contains can be used in LINQ to SQL... it's the normal way of performing "IN" queries. I have a vague recollection that there are some restrictions, but it definitely can work. The restrictions may be around the types involved... is historyList a List<T>? It probably isn't supported for arbitrary IEnumerable<T>, for example.

Now, I don't know whether inverting the result works to give a "NOT IN" query, but it's at least worth trying. Have you tried the exact query from your question?

One point to note: I think the readability of your query would improve if you tried to keep one clause per line:

var nextInvention = (from inv in iContext.Inventions 
                     where !historyList.Contains(inv.Id)
                     orderby inv.DateSubmitted ascending
                     select inv)
                    .First();

Also note that in this case, the method syntax is arguably simpler:

var nextInvention = iContext.Inventions
                            .Where(inv => !historyList.Contains(inv.Id))
                            .OrderBy(inv => inv.DateSubmitted)
                            .First();