Dynamic LINQ OrderBy Date only on DateTime Object Entity Framework

Question

I'm currently using the Dynamic LINQ (original codeplex link) library for dynamic search functionality. I have a requirement that I need to OrderBy a DateTime? field only taking into account the Date and not the time. I'm querying a SQL Azure database using EntityFramework.

Here's the sample entity.

public class SampleEntity
{
    public DateTime? DateCreated { get; set; }
    public bool Flag { get; set; }
}

Here's the code to query the database.

var orderByString = "DateCreated.Value.Date asc, Flag"; //This is a dynamic string
var query = _context.Set<SampleEntity>().OrderBy(orderByString);

This expression ("DateCreated.Value.Date") is parsed without issues by System.Linq.Dynamic but it throws the following error (which is understandable) due to the fact that it's not supported by LINQ to Entities. Keep in mind that this has to work on an IQueryable (I can't use the answer from this post) since I need the sorting to be done server side.

The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

The solution is to use DbFunctions.TruncateTime() as expressed in this answer and others. However, that's not going to work with System.Linq.Dynamic

Any ideas on how to tackle this problem. Possible solution is to add a another column to the DB with only the Date part of DateCreated and query that column. However, I would rather not do that and was looking for some other solution to this problem. Another approach would be to generate the lambda expression dynamically and return the DbFunctions.TruncateTime and then execute that against the DB. Any input is appreciated.

Thanks,

Answer 1

You can use the approach from my answer to Dynamic Linq + Entity Framework: datetime modifications for dynamic select, i.e. post process the query expression with custom ExpressionVisitor and replace the unsupported methods with their DbFunctions equivalents. In this particular case, replace the DateTime.Date property with DbFunctions.TruncateTime method call:

public static class QueryableExtensions
{
    public static IQueryable<T> BindDbFunctions<T>(this IQueryable<T> source)
    {
        var expression = new DbFunctionsBinder().Visit(source.Expression);
        if (expression == source.Expression) return source;
        return source.Provider.CreateQuery<T>(expression);
    }

    public static IQueryable BindDbFunctions(this IQueryable source)
    {
        var expression = new DbFunctionsBinder().Visit(source.Expression);
        if (expression == source.Expression) return source;
        return source.Provider.CreateQuery(expression);
    }

    class DbFunctionsBinder : ExpressionVisitor
    {
        protected override Expression VisitMember(MemberExpression node)
        {
            if (node.Expression != null && node.Expression.Type == typeof(DateTime) && node.Member.Name == "Date")
            {
                var dateValue = Expression.Convert(Visit(node.Expression), typeof(DateTime?));
                var methodCall = Expression.Call(typeof(DbFunctions), "TruncateTime", Type.EmptyTypes, dateValue);
                return Expression.Convert(methodCall, typeof(DateTime));
            }
            return base.VisitMember(node);
        }
    }
}

Sample usage:

var orderByString = "DateCreated.Value.Date asc, Flag"; //This is a dynamic string
var query = _context.Set<SampleEntity>().OrderBy(orderByString).BindDbFunctions();