'IN' & 'NOT IN' in Linq query

Question

I have tested the following query in LINQPad and it runs fine, but VS2010 doesn't like it.

var topJobs = from j in streetlightDBEntities.Job
    let mjobid = from m in streetlightDBEntities.Job.Include("Streetlight")
                 where m.Streetlight.StreetlightId == j.Streetlight.StreetlightId
                 orderby m.DateCompleted descending
                 select m.JobId
        where mjobid.Take(5).Contains(j.JobId)
        select j.JobId;

var notTopJobs = streetlightDBEntities.Job.Where(c => !topJobs.Contains(c.JobId));

I got the following error:

LINQ to Entities does not recognize the method 'Boolean Contains[String](System.Linq.IQueryable`1[System.String], System.String)' method, and this method cannot be translated into a store expression.

Answer 1

Use Queryable.Any<TSource>.

Instead of this:

var notTopJobs = streetlightDBEntities
      .Job
      .Where(c => !topJobs.Contains(c.JobId));

Do this:

var notTopJobs = streetlightDBEntities
      .Job
      .Where(c => !topJobs.Any(x => x.JobId == c.JobId))

Do the same for your original query.