LINQ to Entities does not recognize the method IsNullOrWhiteSpace

Question

I have the below code:

var countries = from c in db.Countries
    where (string.IsNullOrWhiteSpace(searchAlpha2) || (c.Alpha2 ?? string.Empty).ToUpper().Contains(searchAlpha2.ToUpper()))
    && (string.IsNullOrWhiteSpace(searchAlpha2) || (c.Alpha3 ?? string.Empty).ToUpper().Contains(searchAlpha3.ToUpper()))
    && (string.IsNullOrWhiteSpace(searchName) || (c.Name ?? string.Empty).ToUpper().Contains(searchName.ToUpper()))
    select c;

This code uses Entity Framework v6 Code First over a SQL database.

Aside from performance, if I don't include the IsNullOrWhitespace I get no results when the filter criteria are blank (I've tested both null and blank values); however when a value is present this works as expected.

I'm getting the error:

LINQ to Entities does not recognize the method 'Boolean IsNullOrWhiteSpace(System.String)' method, and this method cannot be translated into a store expression.

I'm trying to use the searchXXX strings to filter on columns. I've tried using RegEx.IsMatch, SqlMethods.Like, and the code below, but all give me errors saying those functions are not allowed (errors come from either EntityFramework.SqlServer or from Linq to Entities). I've seen numerous posts on here where this has been done successfully though - so wonder if I'm missing something fundamental?

Answer 1

I would suggest a different approach - use the ability to build queries up on the fly, and thus avoid passing optional query parameters to the expressions altogether - this will result in improved query plans when parsed to sql and executed on the database.

Also, if your database (?SqlServer) is not set to case sensitive collation (i.e. xx_CI_xx), you can avoid the casing conversion as well, as it is redundant:

var myQueryable = db.Countries.AsQueryable();

if (!string.IsNullOrWhiteSpace(searchAlpha2))
{
    myQueryable = myQueryable.Where(c => c.Alpha2.Contains(searchAlpha2));
}
...

var countries = myQueryable.ToList();

You can get this and a bunch more functionality using PredicateBuilder

Update

JB: based on StuartLC's answer, here's the code amended to use PredicateBuilder:

var predicate = PredicateBuilder.True<Country>();
if (!string.IsNullOrWhiteSpace(searchAlpha2))
    predicate = predicate.And(c => c.Alpha2 != null ? c.Alpha2.Contains(searchAlpha2) : false);
if (!string.IsNullOrWhiteSpace(searchAlpha3))
    predicate = predicate.And(c => c.Alpha3 != null ? c.Alpha3.Contains(searchAlpha3) : false);
if (!string.IsNullOrWhiteSpace(searchName))
    predicate = predicate.And(c => c.Name != null ? c.Name.Contains(searchName) : false);

IQueryable<Country> countries = db.Countries.AsExpandable().Where(predicate);