C# Generic DateTime.ToString() with custom format

Question

When using:

DateTime.ToString().Contains("2016")

Entity Framework produces:

CAST(DateValue AS nvarchar(max)) LIKE '%2016%'

This uses the default date-format "mon dd yyyy hh:miAM (or PM)"

I would like to user "yyyy-mm-dd hh:mi:ss (24h)" which is obtainable with something like:

CONVERT(VARCHAR(max), DateValue, 20) LIKE '%2016%'

I need help implementing this format to an existing generic method.

static Expression<Func<T, TResult>> Expr<T, TResult>(Expression<Func<T, TResult>> source) { return source; }
static MethodInfo GetMethod(this LambdaExpression source) { return ((MethodCallExpression)source.Body).Method; }
static readonly MethodInfo Object_ToString = Expr((object x) => x.ToString()).GetMethod();
static readonly MethodInfo String_Contains = Expr((string x) => x.Contains("y")).GetMethod();

public static IQueryable<T> Filter<T>(this IQueryable<T> query, List<SearchFilterDto> filters)
 where T : BaseEntity
{
    if (filters != null && filters.Count > 0 && !filters.Any(f => string.IsNullOrEmpty(f.Filter)))
    {
        var item = Expression.Parameter(query.ElementType, "item");
        var body = filters.Select(f =>
        {
            var value = f.Column.Split('.').Aggregate((Expression)item, Expression.PropertyOrField);
            if (value.Type != typeof(string))
            {
                value = Expression.Call(value, Object_ToString);
            }

            return (Expression)Expression.Call(value, String_Contains, Expression.Constant(f.Filter));
        })
        .Where(r => r != null)
        .Aggregate(Expression.AndAlso);

        var predicate = Expression.Lambda(body, item);
        MethodInfo whereCall = (typeof(Queryable).GetMethods().First(mi => mi.Name == "Where" && mi.GetParameters().Length == 2).MakeGenericMethod(query.ElementType));
        MethodCallExpression call = Expression.Call(whereCall, new Expression[] { query.Expression, predicate });
        query = query.Provider.CreateQuery<T>(call);
    }
    return query;
}

Please note, this is an example - it will not always be "2016" and not always be a year. The user may type the time, or "01" to recall all records either on the 1st day of the month, January or in 2001. It's a very flexible filter.

I also understand that many people will not like this situation, but I am really looking for a solution here and not be told "don't do this"

The solution also needs to cater for LINQ to Entities, so I cant simply .ToString("MMM d yyyy H:mm tt") as this will result in:

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

The code works with the default date-format. The reason for my question is to change the date-format at SQL level, by manipulating the query in Entity Framework.

Answer 1

The only way I found to produce the desired result is manually building it with expression like this

Expression<Func<DateTime, string>> Date_ToString = date =>
    DbFunctions.Right("000" + date.Year.ToString(), 4) + "-" +
    DbFunctions.Right("0" + date.Month.ToString(), 2) + "-" +
    DbFunctions.Right("0" + date.Day.ToString(), 2) + " " +
    DbFunctions.Right("0" + date.Hour.ToString(), 2) + ":" +
    DbFunctions.Right("0" + date.Minute.ToString(), 2) + ":" +
    DbFunctions.Right("0" + date.Second.ToString(), 2);

Ugly, I know. And frankly you don't want to see the EF generated SQL from the above expression - a huge monster compared to the desired CONVERT(...). But at least it works.

Here is the code. One could build the above expression using System.Linq.Expressions, but I'm too lazy for that and used a simple parameter replacer.

The modified part:

if (value.Type != typeof(string))
{
    if (value.Type == typeof(DateTime))
        value = value.ToDateString();
    else if (value.Type == typeof(DateTime?))
        value = Expression.Condition(
            Expression.NotEqual(value, Expression.Constant(null, typeof(DateTime?))),
            Expression.Property(value, "Value").ToDateString(),
            Expression.Constant(""));
    else
        value = Expression.Call(value, Object_ToString);
}

and the used helpers:

static readonly Expression<Func<DateTime, string>> Date_ToString = date =>
    DbFunctions.Right("000" + date.Year.ToString(), 4) + "-" +
    DbFunctions.Right("0" + date.Month.ToString(), 2) + "-" +
    DbFunctions.Right("0" + date.Day.ToString(), 2) + " " +
    DbFunctions.Right("0" + date.Hour.ToString(), 2) + ":" +
    DbFunctions.Right("0" + date.Minute.ToString(), 2) + ":" +
    DbFunctions.Right("0" + date.Second.ToString(), 2);

static Expression ToDateString(this Expression source)
{
    return Date_ToString.ReplaceParameter(source);
}

static Expression ReplaceParameter(this LambdaExpression expression, Expression target)
{
    return new ParameterReplacer { Source = expression.Parameters[0], Target = target }.Visit(expression.Body);
}

class ParameterReplacer : ExpressionVisitor
{
    public ParameterExpression Source;
    public Expression Target;
    protected override Expression VisitParameter(ParameterExpression node)
    {
        return node == Source ? Target : base.VisitParameter(node);
    }
}