"where" clause: entity id is in a potentially null array

Question

I have an array of office ids, and the array is potentially null. I want the EF query to return all records if the officeIdsToSelect array is null, or only the matching records if it is not null. However this:

int[] officeIdsToSelect = new int[] { 1, 2, 3 };
Office[] selectedOffices = (from item in new TdsDb().Offices
                            where (officeIdsToSelect == null || officeIdsToSelect.Contains(item.OfficeID))
                            select item).ToArray();

throws an exception:

System.NotSupportedException : Cannot compare elements of type 'System.Int32[]'. Only primitive types (such as Int32, String, and Guid) and entity types are supported.

Specifically Linq to Entities is objecting to officeIdsToSelect == null. I understand what it's saying (one of the clearer EF error messages...)

So how can I get what I want here?

Answer 1

EF can't translate officeIdsToSelect == null to SQL.

In the other hand, EF is clever enough to translate officeIdsToSelect.Contains(item.OfficeID) to WHERE OfficeID IN (1, 2, 3).

So basically, you could simply do:

Office[] selectedOffices;

if (officeIdsToSelect == null)
{
    selectedOffices = new TdsDb().Offices.ToArray();
}
else
{
    selectedOffices = (from item in new TdsDb().Offices
                            where officeIdsToSelect.Contains(item.OfficeID)
                            select item).ToArray();
}

EDIT:

If your actual query is more complicated and you don't want to duplicate it, what you could do is conditionally add a Where clause depending on the value of your int array.

// Here's a query that is NOT YET executed (deferred execution)
var query = (from item in new TdsDb().Offices
                            ...... your whole complicated request here
                            select item);

// Conditionally adds a where clause if required
if (officeIdsToSelect != null)
{
    // Still not yet executing the query. We're just building the query for now
    query = query.Where(z => officeIdsToSelect.Contains(z.OfficeID));
}

// OK, now executes the query and get the results
Office[] selectedOffices = query.ToArray();

if the conditional Where doesn't overwrite the original Where clause; but is additive

Yes, that's the power of LINQ to Entities: fluent programming and deferred execution.

Fluent programming means you can chain methods, and this is possible with LINQ thanks to the IQueryable extension methods.

For example, IQueryable<T>.Where(...) returns also an IQueryable<T> object. It internally adds a predicate to the query, then returns the query you specified as parameter.

The other important part is the deferred execution. This allow to not execute the query until the data is actually requested. It's only when you actually need the data that the request in actually executed against your database.

In the above example, it's the .ToArray() command that actually executes the query.

See this nice MSDN article for details about query execution mechanisms.