LINQ to Entities Does Not Recognize IsNullOrWhiteSpace Method

Question

I have the code below:

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 the Entity Framework v6 Code First on top of the SQL database.

In addition to performance, if I do not turn it on IsNullOrWhitespace, I do not get results when the filter criteria is empty (I tested both empty and empty values); however, when a value is present, this works as expected.

I get an error message:

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

I am trying to use searchXXX strings to filter by columns. I tried using RegEx.IsMatch, SqlMethods.Like and the code below, but everyone gives me errors saying that these functions are not allowed (errors come either from EntityFramework.SqlServeror from Linq to Entities). I saw numerous posts here where it was done successfully, although - so interestingly, am I missing something fundamental?

Answer 1

- " " , , - sql .

, (? SqlServer) (.. xx_CI_xx), , :

var myQueryable = db.Countries.AsQueryable();

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

var countries = myQueryable.ToList();

, PredicateBuilder

JB: StuartLC 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);

Answer 2

,

string.IsNullOrWhiteSpace(searchAlpha2)

to

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

, SQL.

Answer 3

when you use linq in Entity Framework to get data from DataBase, you only need to use with functions that Entity Framework can convert to sql query.