I'm trying to create a Linq Expression to compare a date column with DateTime.Now

Here's my code

private void ProcessQueueEntries()
{
    _logger.LogInformation("ProcessQueueEntries");

    try
    {
        var entries = _repo.ReadQueueEntries()
                           .Where(MatchQueueEntries())
                           .OrderBy(t => t.TaskNumber)
                           .ToList()
                           .Select(te => te.ToQueueEntry());
        ....
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, "ProcessQueueEntries");
    }
}

private static Expression<Func<TaskEntry, bool>> MatchQueueEntries()
{            
    var nowValue = Expression.Constant(DateTime.Now, typeof(DateTime?));
    var taskEntryExpr = Expression.Parameter(typeof(TaskEntry));
    var scheduledTimeValue = Expression.PropertyOrField(taskEntryExpr, "ScheduledTime");            
    var scheduledTestExpr = Expression.LessThan(scheduledTimeValue, nowValue);

    return Expression.Lambda<Func<TaskEntry, bool>>(scheduledTestExpr, taskEntryExpr);
}

It is generating the following sql

SELECT [i].[TaskNumber], ...
FROM [Tasks] AS [i]
WHERE ... 
AND ([i].[ScheduledTime] < '2022-08-08T13:36:31.4981662 01:00')
ORDER BY [i].[TaskNumber]

and generating an conversion exception

Microsoft.Data.SqlClient.SqlException: 
'Conversion failed when converting date and/or time from character string.'

What is the correct way to express DateTime.Now within a Linq expression such that it doesn't generate an exception like this - or perhaps there's a setting on the datacontext which I've not set correctly to handle dates?

CodePudding user response：

DateTime.Now is static property call and you have to repeat that in Expresion Tree:

private static PropertyInfo _nowProperty = typeof(DateTime).GetProperty(nameof(DateTime.Now), BindingFlags.Static | BindingFlags.Public);

private static Expression<Func<TaskEntry, bool>> MatchQueueEntries()
{
    var nowValue           = (Expression)Expression.Property(null, _nowProperty);
    var taskEntryExpr      = Expression.Parameter(typeof(TaskEntry));
    var scheduledTimeValue = Expression.PropertyOrField(taskEntryExpr, "ScheduledTime");

    if (scheduledTimeValue.Type != nowValue.Type)
        nowValue = Expression.Convert(nowValue, scheduledTimeValue.Type);

    var scheduledTestExpr  = Expression.LessThan(scheduledTimeValue, nowValue);

    return Expression.Lambda<Func<TaskEntry, bool>>(scheduledTestExpr, taskEntryExpr);
}