Entity Framework Core to SQL Server IN Clause

Question

We are converting from LINQ to SQL to Entity Framework Core 2.2 and are finding that the translation of Contains operations do not become IN clauses in SQL Server. What is happening is that EF Core is pulling back all of the data using the other conditions and then filtering it down locally. This is not acceptable. We can use EF.Functions.Contains but this requires us to enable Full Text Search in SQL Server, which is over kill.

Any idea how to get a statement like the following to translate to an IN clause in SQL Server?

var myValues = new [] { 1, 2, 3, 4, 5 };
var qry = _context.Table.Where(t => myValues.Contains(t.TableProperty));

Okay, maybe I over simplified the code to keep it simple for the question. The actual code looks like:

voterQuery = voterQuery.Where(v => voterFilter.VoterStatus.Select(p => p.Value).Contains(v.VotStatus.ToString()));

What is happening in our code is that we are building up an IQueryable from user selections on a filtering screen. voterFilters contains a collection of these selection criteria. VoterStatus is one of the selection criteria which is a List<CheckedListItem>, which are from a Winforms CheckedListItems control.

The selection of p.Value returns a List of strings. I have tried to project the list of strings to an array, with the same results of the IN clause not being created or a server query. Perhaps, EF Core does not allow strings to be used for the IN clause values. Any insight would be appreciated.

Answer 1

Currently (as of v2.2.3), EF Core requires the expression used for Contains to be a simple IEnumerable<T> variable (no LINQ operators) where the T is primitive type.

Which means you need to move the voterFilter.VoterStatus.Select(p => p.Value) into variable outside the query expression tree and use that variable inside:

var voterStatusFilter = voterFilter.VoterStatus.Select(p => p.Value);
voterQuery = voterQuery.Where(v => voteStatusFilter.Contains(v.VotStatus.ToString()));