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.

Update: Copied from comment by @DavidKempfner

As of EntityFramework.6.2.0 it generated SQL that checked for
!(searchAlpha2.Trim() == string.Empty),
I.E. It ignored the searchAlpha2 == null || part.

Use this instead:

!string.IsNullOrEmpty(entity.searchAlpha2.Trim())

Answer 2

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);

Answer 3

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

Answer 4

I know that there an already accepted answer for this question but I got an idea to share.

Instead of putting multiple checks in the LINQ or using if statement to apply where clause on list result, you can use a simple trick. Just declare a bool variable and assign IsNullOrWhitespace of add to linq like:

bool isNull = string.IsNullOrWhiteSpace(searchAlpha2); 
var countries = db.Countries.Where(c => isNull  || c.Alpha2.Contains(searchAlpha2)).ToList();