LINQ to SQL, where the identifier is not in some_list

Question

I am trying to make a LINQ to SQL statement that filters the results where the identifier is not in some list of integers. I understand that the .contains () method cannot be used in Linq to SQL, but to explain what I would like to do, here is what I would 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 can do this?

Thanks!

Answer 1

Contains can be used in LINQ to SQL ... this is the usual way to execute "IN" queries. I have a vague recollection that there are some limitations, but that could definitely work. Constraints may be associated with types ... historyList a List<T> ? It is probably not supported for arbitrary IEnumerable<T> , for example.

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

One point to note: I think that the readability of your request will improve if you try to keep one sentence 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 probably simpler:

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