Is there a way to compare LocalDate After Retrieving Timezones on Npgsql EF Core?

Question

Nodatime plugin does not support parameterised timezones. I would like to create a custom helper method to perform the logic of the code below. Is the reason my LINQ is not able to be translated because of the parameterised timezone or is it not possible to write a generic expression for comparing values of a custom type?

//Wrong
var potatoesCreatedToday = await dbContext.Potatoes.Where(m => m.CreatedOn.InZone(timezone).Date == localDate).ToListAsync;

//Correct
var potatoesCreatedToday = await dbContext.Potatoes.Where(m => m.CreatedOn.InZone(DateTimeZoneProviders.Tzdb[timezoneName]).Date == localDate).ToListAsync;

//Custom helper method
public static IQueryable<T1> InstantFilter(IQueryable<T1> source, string op, string[] values, string propertyName, DateTimeZone timezone)
{
    // Check if the type T has a property named "CreatedOn"
    var createdOnProperty = typeof(T1).GetProperty(propertyName);

    var value = Instant.FromUnixTimeMilliseconds(Convert.ToInt64(values[0].Trim().ToLower())).InZone(timezone).Date;

    if (createdOnProperty != null)
    {
        var parameter = Expression.Parameter(typeof(T1), "m");
        var propertyExpression = Expression.Property(parameter, createdOnProperty);
        var dateConstant = Expression.Constant(value, typeof(LocalDate));
        var timezoneConstant = Expression.Constant(timezone, typeof(DateTimeZone));
        MethodInfo methodInfo = typeof(Instant).GetMethod("InZone", new[] { typeof(DateTimeZone) });
        MethodCallExpression callExpression = Expression.Call(propertyExpression, methodInfo, timezoneConstant);
        var dateProperty = typeof(ZonedDateTime).GetProperty("Date");
        var propertyExpression2 = Expression.Property(callExpression, dateProperty);
        Expression<Func<T1, bool>> predicate;


        if (op == ">")
        {
            predicate = Expression.Lambda<Func<T1, bool>>(Expression.GreaterThan(propertyExpression, dateConstant), parameter);
        }
        else if (op == "<")
        {
            predicate = Expression.Lambda<Func<T1, bool>>(Expression.LessThan(propertyExpression2, dateConstant), parameter);
        }
        else if (op == "<=")
        {
            predicate = Expression.Lambda<Func<T1, bool>>(Expression.LessThanOrEqual(propertyExpression, dateConstant), parameter);
        }
        else if (op == ">=")
        {
            predicate = Expression.Lambda<Func<T1, bool>>(Expression.GreaterThanOrEqual(propertyExpression, dateConstant), parameter);
        }
        else
        {
            predicate = Expression.Lambda<Func<T1, bool>>(Expression.Equal(propertyExpression, dateConstant), parameter);
        }

        return source.Where(predicate);
    }

    return source;
}

Error message

 .Where(t => t.CreatedOn.InZone((DateTimeZone)Asia/Singapore).Date < Friday, 8 December, 2023)' could not be translated

I tried to look up guides on npgsql and nodatime but I can't find a solution, so I would be extremely grateful if someone could point me to the right path.

https://www.npgsql.org/efcore/mapping/nodatime.html?tabs=with-datasource#operation-translation

**Updated (2023-12-12): The Nodatime plugin version 8 and above now supports parameterised timezones. Now a different problem arises, I think there is a limitation with Expressions since Expression.GreaterThan was probably designed to support only numeric values. The generated SQL query is missing single quotes for the timezone value 'Asia/Singapore' and I can't think of a way to add it. Below is the generated query.

SELECT * FROM potatoes AS p
WHERE CAST(p.created_on AT TIME ZONE Asia/Singapore::text AS date) < DATE '2023-12-12'

Answer 1

I took a slightly different approach to solving this problem. I retrieved two Instant values, one representing the start of the selected day and one representing the start of the next day (to represent end of the selected day). Using these two Instant values, I was able to create generic filters to compare dates based on a given timezone.

public static IQueryable<T1> InstantDateFilter(IQueryable<T1> source, string op, string[] values, string propertyName, DateTimeZone timezone)
{
    var sourceProperty = typeof(T1).GetProperty(propertyName);

    var startOfSelectedDayValue = Instant.FromUnixTimeMilliseconds(Convert.ToInt64(values[0].Trim().ToLower()))
        .InZone(timezone)
        .Date
        .AtStartOfDayInZone(timezone)
        .ToInstant();

    var startOfNextDayValue = Instant.FromUnixTimeMilliseconds(Convert.ToInt64(values[0].Trim().ToLower()))
        .InZone(timezone)
        .Date
        .PlusDays(1)
        .AtStartOfDayInZone(timezone)
        .ToInstant();

    if (sourceProperty != null)
    {
        var parameter = Expression.Parameter(typeof(T1), "m");
        var memberExpression = Expression.Property(parameter, sourceProperty);

        if (sourceProperty.PropertyType.IsGenericType && sourceProperty.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
        {
            memberExpression = Expression.Property(memberExpression, "Value");
        }
        var startOfDayConstant = Expression.Constant(startOfSelectedDayValue, typeof(Instant));
        var startOfNextDayConstant = Expression.Constant(startOfNextDayValue, typeof(Instant));

        Expression<Func<T1, bool>> predicate;


        if (op == ">")
        {
            predicate = Expression.Lambda<Func<T1, bool>>(Expression.GreaterThanOrEqual(memberExpression, startOfNextDayConstant), parameter);
        }
        else if (op == "<")
        {
            predicate = Expression.Lambda<Func<T1, bool>>(Expression.LessThan(memberExpression, startOfDayConstant), parameter);
        }
        else if (op == "<=")
        {
            predicate = Expression.Lambda<Func<T1, bool>>(Expression.LessThan(memberExpression, startOfNextDayConstant), parameter);
        }
        else if (op == ">=")
        {
            predicate = Expression.Lambda<Func<T1, bool>>(Expression.GreaterThanOrEqual(memberExpression, startOfDayConstant), parameter);
        }
        else
        {
            var firstPredicate = Expression.Lambda<Func<T1, bool>>(Expression.GreaterThanOrEqual(memberExpression, startOfDayConstant), parameter);
            var secondPredicate = Expression.Lambda<Func<T1, bool>>(Expression.LessThan(memberExpression, startOfNextDayConstant), parameter);
            predicate = Expression.Lambda<Func<T1, bool>>(Expression.AndAlso(firstPredicate.Body, new ExpressionReplacer(secondPredicate.Parameters[0], firstPredicate.Parameters[0]).Visit(secondPredicate.Body)), parameter);
        }

        return source.Where(predicate);
    }

    return source;
}