LINQ to SQL: "Method 'Boolean Contains(System.String)' has no supported translation to SQL."

Question

I have a hashset of strings representing tmdbId's for movies I have on disk - called moviesOnDisk.

I have a database of movie objects, indexed on the tmdbId.

I want to delete the records that exist in the database but don't exist on disk.

I have this line to get the difference:

var toDelete = Database.Movies.Where(x => !moviesOnDisk.Contains(x.TMDbId));

this gives me no results and the following message:

Method 'Boolean Contains(System.String)' has no supported translation to SQL.

Is there a work around for this? Obviously I can iterate over both lists, but I am going for best performance.

Answer 1

Change your where clause to !moviesOnDisk.ToList().Contains(x.TMDbId).

@Rob provided a great explanation in comments on why Contains will work on IEnumerable, but not on a HashSet:

It works because Contains is a specific implementation on HashSet. When translating to SQL, it has a set of supported methods, including Queryable.Contains() - which is a different method from what you've written. HashSet.Contains has a different implementation (that is, hashing the value and doing a lookup), and can't be converted to SQL