Alexandru Alexandru - 1 month ago 13
C# Question

Searching a DateTime using a search string in Entity Framework code first

I have an MVC project which outputs a nullable DateTime (

DateTime?
) as a string to the user for each model item as shown below (my user interface has paging so I only do this computation for a small number of records - in other words, this part is okay):

foreach (DocumentEvent item in Model.items)
@(item?.TimeUtc?.ToString() ?? "N/A")


I want to add a search functionality. I've tried searching as follows, but this is not performant because
AsEnumerable
materializes my list and I am now in the C# world, enumerating through each record:

using (var context = new ClientEventsContext())
var items = context.Events.AsEnumerable().Where(x => {
(x?.TimeUtc?.ToString() ?? "N/A").Contains(model.search)
});


Instead I want to take advantage of my SQL Server database. How can I build an SQL Server-friendly query for the above code without
AsEnumerable
that will produce the same results as my current logic?

Answer

Here is how you can build and use LINQ to Entities compatible conversion from date to string in M/d/yyyy h:mm:ss tt format. Rather that embedding that monster inside the query, I will use a custom "marker" method and will bind the implementation using ExpressionVisitor. This way you can experiment and change the format if needed (even add some controlling arguments) w/o affecting the readability of the query.

First, the implementation:

public static class EFExtensions
{
    public static string ToCustomDateFormat(this DateTime value)
    {
        // Should never happen
        throw new InvalidOperationException();
    }

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

    class CustomDateFormatBinder : ExpressionVisitor
    {
        protected override Expression VisitMethodCall(MethodCallExpression node)
        {
            if (node.Method.DeclaringType == typeof(EFExtensions) && node.Method.Name == "ToCustomDateFormat")
            {
                var date = Visit(node.Arguments[0]);
                var year = DatePart(date, v => DbFunctions.Right("0000" + v.Year, 4));
                var month = DatePart(date, v => v.Month.ToString());
                var day = DatePart(date, v => v.Month.ToString());
                var hour = DatePart(date, v => (1 + (v.Hour + 11) % 12).ToString());
                var minute = DatePart(date, v => DbFunctions.Right("0" + v.Minute, 2));
                var second = DatePart(date, v => DbFunctions.Right("0" + v.Second, 2));
                var amPM = DatePart(date, v => v.Hour < 12 ? "AM" : "PM");
                var dateSeparator = Expression.Constant("/");
                var timeSeparator = Expression.Constant(":");
                var space = Expression.Constant(" ");
                var result = Expression.Call(
                    typeof(string).GetMethod("Concat", new Type[] { typeof(string[]) }),
                    Expression.NewArrayInit(typeof(string),
                        month, dateSeparator, day, dateSeparator, year, space,
                        hour, timeSeparator, minute, timeSeparator, second, space, amPM));
                return result;    
            }
            return base.VisitMethodCall(node);
        }

        Expression DatePart(Expression date, Expression<Func<DateTime, string>> part)
        {
            var parameter = part.Parameters[0];
            parameterMap.Add(parameter, date);
            var body = Visit(part.Body);
            parameterMap.Remove(parameter);
            return body;
        }

        Dictionary<ParameterExpression, Expression> parameterMap = new Dictionary<ParameterExpression, Expression>();

        protected override Expression VisitParameter(ParameterExpression node)
        {
            Expression replacement;
            return parameterMap.TryGetValue(node, out replacement) ? replacement : node;
        }
    }
}

then the usage:

var items = context.Events
    .Where(x => x.TimeUtc != null && 
        x.TimeUtc.Value.ToCustomDateFormat().Contains(model.search))
    .ApplyCustomDateFormat();
Comments