WAQS Querying: Coalesce

7 reasons to use WAQS

WAQS documentation

 

In SQL, navigation properties don’t exist, you don’t have NullReferenceException and result is "flat".

If you use this query:

context.Orders.Select(o => new { o.Id, o.OrderDetails });


the SQL result is something like this:



image



If you have an Order without any detail, the SQL result is like this one:



image



Now think about this query:



context.Orders.Select(o => o.OrderDetails.Sum(od => od.Quantity * od.UnitPrice * (1 - od.Discount)));


With LINQ To Object it works fine.



But with LINQ To Entities, it could failed with InvalidOperationException “The cast to value type ‘System.Double’ failed because the materialized value is null. Either the result type’s generic parameter or the query must use a nullable type.”



Why?



If you have some Orders without any detail, the sum value returned by SQL is null.



In C#, because of type inference, we are expecting a double.



So LINQ to Entities failed because it can’t set a double property with null.



In order to fix it we can use this code:



context.Orders.Select(o => (double?)o.OrderDetails.Sum(od => od.Quantity * od.UnitPrice * (1 - od.Discount)) ?? 0);


But it’s a pain. In addition this is a L2E specific point so it does not make sense in WAQS specification writing.



Of course, we could use the Expression calculation partial method for it would be a pain too.



 



The cool thing is the fact that WAQS do it for you!



If you use a navigation property in a non nullable scalar type calculated property body, WAQS uses a cast and a coalesce in the Expression calculation generated code.



So, with these calculated properties WAQS specifications:



public static double GetAmount(this OrderDetail od)


{


     return od.Quantity * od.UnitPrice * (1 – od.Discount);


}


public static double GetTotal(this Order o)


{


     return o.OrderDetails.Sum(od => od.GetAmount());


}



WAQS uses this code to calculate the expression:



protected internal static Expression<Func<WebApplication25.Order, double>> GetTotalExpression (
bool isCoalesceEnabled = true)
{
    Func<WebApplication25.OrderDetail, double> getAmount_OrderDetail = (_) => default(double);
    Expression<Func<WebApplication25.Order, double>> exp = (o) => o.OrderDetails.Sum(od => getAmount_OrderDetail(od));
    Func<Expression, Expression> replaceExpFunc = null;
    replaceExpFunc = e =>
{
var invocationExpression = e as InvocationExpression;
MemberExpression memberExpression;
if (invocationExpression == null 
|| (memberExpression = invocationExpression.Expression as MemberExpression) == null)
return e; switch (memberExpression.Member.Name) { case "getAmount_OrderDetail": {
var getAmount_OrderDetailSubExp = OrderDetailDALSpecifications.GetAmountExpression (
isCoalesceEnabled : false);
if (getAmount_OrderDetailSubExp == null)
return null;
Expression subExp = getAmount_OrderDetailSubExp.Body.Replace(
getAmount_OrderDetailSubExp.Parameters[0], replaceExpFunc(invocationExpression.Arguments[0]));
return subExp;
}
default:
return e;
}
};
exp = (Expression<Func<WebApplication25.Order, double>>)exp.Replace(replaceExpFunc);
if (exp != null && isCoalesceEnabled)
exp = (Expression<Func<WebApplication25.Order, double>>)exp.Replace(exp.Body,
Expression.Coalesce(Expression.Convert(exp.Body, typeof(double?)), Expression.Constant(default(double))));
GetTotalExpression (ref exp);
return exp;
}






Now, if you want to get the total spent by customer, it is useless to use the Coalesce expression on Order total because Expression needs to use it in the Customer one. So that’s why WAQS generates the isCoalesceEnabled parameter.

This entry was posted in 12253, 16868, 7674. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>