LINQ to Entities does not recognize the method IsNullOrWhiteSpace

asp.net-mvc-5 c# entity-framework-6 linq-to-entities linq-to-sql

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?

1
5
6/18/2014 3:48:10 PM

Accepted Answer

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);
5
6/18/2014 10:40:11 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow