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

If you want to use your statement in current form you might want to replace

string.IsNullOrWhiteSpace(searchAlpha2)

to

!(searchAlpha2 == null || searchAlpha2.Trim() == string.Empty)

and all the other values too, for it to get translated to working SQL.