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

While working with LINQ queries (IQueryable<T>) one may face the error of 'LINQ to Entities does not recognize the method...’

For example consider following LINQ statement:

dbContext.Employees.Where(c => !string.IsNullOrWhiteSpace(c.Name));

There will be not any compile error and it'll be compiled successfully, however you'll get the error (mentioned below) when this query get executed.

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

Reason: Once you get the error, you'll try to figure out what went wrong with this LINQ statement, as there is no error if you compile the above statement.

Simple reason of this error is IQueryable<T> creates the SQL friendly query and once you enumerate (i.e. by accessing any property or converting it into IEnumerable<T> or List) over this query it'll get executed. That means every LINQ statement written by you is then converted to relevant SQL statement, and when you try to use static method of string like 'IsNullOrWhiteSpace', 'IsNullOrEmpty' etc., it won't be translated into SQL query. Because these methods has no supported translation to SQL.

Solution: As I mentioned earlier LINQ to Entities queries are translated to SQL statements, but there is no way to translate the logic of your custom comparer to SQL statement, so to avoid the error either we need to modify the LINQ statement so that it can easily be translated into SQL or the custom comparisons has to be done in memory.

Solution1 -> Modify your statement to avoid error:

We can modify above mentioned statement by replacing: !string.IsNullOrWhiteSpace(c.Name) to !(c.Name == null || c.Name.Trim() == string.Empty)

And this statement will be easily translated into LINQ to Entities or LINQ to SQL.

Solution2 -> Convert to IEnumerable<T> and then do the custom operation in memory.

Modify the statement as: dbContext.Employees.AsEnumerable()             .Where(c => !string.IsNullOrWhiteSpace(c.Name));

Second approach have a drawback though for example if you are trying to filter out from thousands or millions records through the where clause if you convert your query into IEnumerable<T> query will be executed on to the SQL and all data will be stored in the memory for further operations so every time you fire this query you need a good amount of memory for your custom operation and thus there will be performance issue but in case of "Solution 1" first query is generated with all the necessary filters and then only matched data will be returned back instead of all records. So it’s all on you which solution is good for your requirements

Hope that helps J