String.IsNullOrWhiteSpace in LINQ Expression

I have the following code:

return this.ObjectContext.BranchCostDetails.Where(
    b => b.TarrifId == tariffId && b.Diameter == diameter
        || (b.TarrifId==tariffId && !string.IsNullOrWhiteSpace(b.Diameter))
        || (!b.TarrifId.HasValue) && b.Diameter==diameter);

And I get this error when I try to run the code:

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

How can I solve this problem and write code better than this?

Solution 1:

You need to replace

!string.IsNullOrWhiteSpace(b.Diameter)

with

!(b.Diameter == null || b.Diameter.Trim() == string.Empty)

For Linq to Entities this gets translated into:

DECLARE @p0 VarChar(1000) = ''
...
WHERE NOT (([t0].[Diameter] IS NULL) OR (LTRIM(RTRIM([t0].[Diameter])) = @p0))

and for Linq to SQL almost but not quite the same

DECLARE @p0 NVarChar(1000) = ''
...
WHERE NOT (LTRIM(RTRIM([t0].[TypeName])) = @p0)

Solution 2:

In this case it is important to distinguish between IQueryable<T> and IEnumerable<T>. In short IQueryable<T> is processed by a LINQ provider to deliver an optimized query. During this transformation not all C# statements are supported, as it either is not possible to translate them to a back-end specific query (e.g. SQL) or because the implementer did not foresee the need for the statement.

In contrast IEnumerable<T> is executed against the concrete objects and, therefore, will not be transformed. So, it is quite common that constructs, which are useable with IEnumerable<T>, cannot be used with IQueryable<T> and also that IQueryables<T> backed by different LINQ providers do not support the same set of functions.

However, there are some workarounds (like Phil's answer), which modify the query. Also, as a more general approach it is possible to drop back to an IEnumerable<T> before continuing with the specification of the query. This, however, might have a performance hit - especially when using it on restrictions (e.g. where clauses). In contrast, when dealing with transformations the performance hit is a lot smaller, sometimes even non existent - depending on your query.

So the above code could also be rewritten like this:

return this.ObjectContext.BranchCostDetails
    .AsEnumerable()
    .Where(
        b => b.TarrifId == tariffId && b.Diameter == diameter
        || (b.TarrifId==tariffId && !string.IsNullOrWhiteSpace(b.Diameter))
        ||(!b.TarrifId.HasValue) && b.Diameter==diameter
    );

NOTE: Ths code will have an higher performance impact than Phil's answer. However, it shows the principle.

Solution 3:

Use an expression visitor to detect references to string.IsNullOrWhiteSpace and break them down into a simpler expression (x == null || x.Trim() == string.Empty).

So below is an extended visitor and an extension method to make use of it. It requires no special config to use, simply call WhereEx instead of Where.

public class QueryVisitor: ExpressionVisitor
{
    protected override Expression VisitMethodCall(MethodCallExpression node)
    {
        if (node.Method.IsStatic && node.Method.Name == "IsNullOrWhiteSpace" && node.Method.DeclaringType.IsAssignableFrom(typeof(string)))
        {
            //!(b.Diameter == null || b.Diameter.Trim() == string.Empty)
            var arg = node.Arguments[0];
            var argTrim = Expression.Call(arg, typeof (string).GetMethod("Trim", Type.EmptyTypes));

            var exp = Expression.MakeBinary(ExpressionType.Or,
                    Expression.MakeBinary(ExpressionType.Equal, arg, Expression.Constant(null, arg.Type)),
                    Expression.MakeBinary(ExpressionType.Equal, argTrim, Expression.Constant(string.Empty, arg.Type))
                );

            return exp;
        }

        return base.VisitMethodCall(node);
    }
}

public static class EfQueryableExtensions
{
    public static IQueryable<T> WhereEx<T>(this IQueryable<T> queryable, Expression<Func<T, bool>> where)
    {
        var visitor = new QueryVisitor();
        return queryable.Where(visitor.VisitAndConvert(where, "WhereEx"));
    }
}

So if you run myqueryable.WhereEx(c=> !c.Name.IsNullOrWhiteSpace()) it will be converted to !(c.Name == null || x.Trim() == "") before being passes to whatever (linq to sql/entities) and converted to sql.