EF6 Linq - How to create an filter expression equivalent to “where(List == null || List.Contains(obj))”?

Question

I'm trying to do the following (SelectedIdCollection is List and cb.Id is int) -

db.Items.Where(cb => (SelectedIdCollection == null || SelectedIdCollection.Contains(cb.Id)))

Basically, if SelectedIdCollection is null then return everything, if it is not null then filter by it.

But it throws the following error -

An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code. Cannot compare elements of type 'System.Collections.Generic.IList`1[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]'. Only primitive types, enumeration types and entity types are supported.

Is there any other way of writing this where condition?

Answer 1

The exception is being thrown because you are comparing a runtime variable (SelectedIdCollection != null), and EF doesn't know how to translate that into SQL.

Can you do something like this instead?

var items = db.Items.AsQueryable();

if(SelectedIdCollection != null)
{
   items = items.Where(cb => SelectedIdCollection.Contains(cb.Id));
}

if(date1 != null)
{
   items = items.Where(cb => cb.Date1 == date1);
}

This will potentially be faster in SQL as well, because the query planner might choose different indexes if it doesn't need to read all the columns to filter.

Answer 2

Since SelectedIdCollection is a variable captured from outside your expression, you can deal with it being null before making the expression, and then treat it as non-null:

var getEverything = SelectedIdCollection==null;
var targetCollection = SelectedIdCollection ?? new int[0];
var res = db.Items.Where(cb => getEverything || targetCollection.Contains(cb.Id));

Now targetCollection is guaranteed to be non-null, and getEverything flag covers the condition that requires selecting everything from the database.