WAQS Querying: DateTime calculation

7 reasons to use WAQS

WAQS documentation

 

DateTime calculation with LINQ To Entities

First, with the different version of LINQ To Entities, you can have some break changes with DateTime.Now.

Indeed, in the past, it uses the value of the current DateTime of the computer that executes the L2E query and now, with latest versions, Entity Framework uses SQL Server current DateTime.

Instead of avoiding this, WAQS uses the client computer current DateTime with DateTime.Now and adds a property DbDateTime in the client context that you can use in order to use DB DateTime.

 

In addition, LINQ to Entities does not support DateTime calculation. So, for example, if you want to write a LINQ query that returns invoices not paid from more than 30 days, it is not possible to do it with a “normal” way so with something like this:

from i in context.Invoices
where !i.Paid && (DateTime.Now - i.Order.OrderDate).Days > 30
select i;


Instead of it we have to use some static methods define in EntityFunctions class:



from i in context.Invoices
where !i.Paid && EntityFunctions.DiffDays(DateTime.Now, i.Order.OrderDate) > 30
select i;


But the issue with this is the fact that, IMHO, the layer that define the queries should not have any dependence on Entity Framework.



 



Using WAQS, you can use the “normal” way:



var invoices = await (from i in _context.Invoices.AsAsyncQueryable()
                where !i.Paid && (_context.DbDateTime - i.Order.OrderDate).Days > 30
                select i).ExecuteAsync();


Indeed DateTime calculation is supported by LINQ To WAQS.



 



Now the question is: how does it work?



How does it work?



First, in the DAL layer, WAQS generates this code:



[EdmFunction("Edm", "DiffDays")]
public static int DiffDays(DateTime dateTime1, DateTime dateTime2)
{
     return (dateTime1 - dateTime2).Days; }


The attribute EdmFunction specifies that we have to use the Entity Framework function DiffDays. So the body of the method is ignored in LINQ To Entities. It’s equivalent than using EntityFunctions class.



Then, in the service, when we got the query from LINQ To WAQS, we will change (DateTime substraction).Days to use this method.



But, here, the issue is the fact that we don’t want to reference to the DAL layer from the Service one because we want to only use interfaces.



So, WAQS DAL interfaces generation creates an interface INorthwindWAQSEntities (in our sample) that extends IObjectContext interface (an interface generated by WAQS without any reference to entity framework except its namespace: WCFAsyncQueryableServices.DAL.Interfaces.L2E) that defines a DiffDaysExpression method:



Expression DiffDaysExpression (Expression dateTime1Expresion, Expression dateTime2Expresion);


Then, in the DAL layer, we have this method to implements the interface:


Expression IObjectContext.DiffDaysExpression (Expression dateTime1Expresion, Expression dateTime2Expresion)


{


     return Expression.Call(typeof(NorthwindWAQSEntities).GetMethod("DiffDays"), dateTime1Expresion, dateTime2Expresion);


}

 



Now in the service, WAQS transforms the LINQ To WAQS query into LINQ To Entities query.



In the service we have a special L2E version (with no reference to EF). In the global.asax.cs added by WAQS, the Unity configuration specifies to use it.



When you use the L2E version, WAQS transforms the (DateTime difference).Days using the DAL expression.



So, for example in our sample, WAQS uses this code to build the L2E Expression:



if (property.Name == "Date" && source.Type == typeof(DateTime))
     return objectContext.GetDateExpression (source);


BinaryExpression subtractExpression = source as BinaryExpression;
if (subtractExpression != null && subtractExpression.NodeType == ExpressionType.Subtract
    && source.Type == typeof(TimeSpan))

    if (subtractExpression.Left.Type == typeof(DateTime)) 
    {
          switch (property.Name)
         {
              case "Days":
                  return objectContext.DiffDaysExpression (subtractExpression.Right, subtractExpression.Left);
              case "Hours":
                  return objectContext.DiffHoursExpression (subtractExpression.Right, subtractExpression.Left);
              case "Minutes":
                  return objectContext.DiffMinutesExpression (subtractExpression.Right, subtractExpression.Left);
              case "Seconds":
                  return objectContext.DiffSecondsExpression (subtractExpression.Right, subtractExpression.Left);
              case "Milliseconds":
                  return objectContext.DiffMillisecondsExpression (subtractExpression.Right, subtractExpression.Left);
         } 
    } 
    else if (subtractExpression.Left.Type == typeof(DateTimeOffset)) 
    {
        switch (property.Name)
        {
             case "Days":
                  return objectContext.DiffDaysOffsetExpression (subtractExpression.Right, subtractExpression.Left);
             case "Hours":
                  return objectContext.DiffHoursOffsetExpression (subtractExpression.Right, subtractExpression.Left);
             case "Minutes":
                  return objectContext.DiffMinutesOffsetExpression (subtractExpression.Right, subtractExpression.Left);
             case "Seconds":
                  return objectContext.DiffSecondsOffsetExpression (subtractExpression.Right, subtractExpression.Left);
             case "Milliseconds"
                  return objectContext.DiffMillisecondsOffsetExpression (subtractExpression.Right, subtractExpression.Left);
        } 
    }
}



Note that we could not build the Call expression in the service. Indeed, if you try to define DiffDays method non static and use this one in DAL interface, you have an issue because Expression does not apply polymorphism and so the method used in Expression won’t have the EdmFunction attribute.



In addition, note that other calculation like AddDays, AddHours, etc / DateTime + TimeSpan / dateTime – TimeSpan also are supported in LINQ To WAQS (not in LINQ To Entities).

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>