LINQ to SQL: "The Boolean Contains (System.String) method does not support SQL translation."

Question

I have a hashset of strings representing tmdbId for the movies that I have on disk - called movieOnDisk.

I have a movie object database indexed by tmdbId .

I want to delete records that exist in the database but do not 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:

The 'Boolean Contains (System.String)' method does not support translating to SQL.

Is there any work for this? Obviously, I can iterate over both lists, but I'm going to get maximum performance.

Answer 1

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

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

This works because Contains is a specific implementation on a HashSet. When translating to SQL, it has a set of supported methods, including Queryable.Contains () - this is another method from what you wrote. HashSet.Contains has a different implementation (i.e. hashing a value and doing a search) and cannot be converted to SQL

Answer 2

change movieOnDisk to an array, it will work.
array element type must match TMDbId type