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)
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.
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.
You can also use this to check for whitespace:
b.Diameter!=null && !String.IsNullOrEmpty(b.Diameter.Trim())
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With