Comparing strings as dates using EF core 3

Question

I'm migrating some code from asp.net to asp.net core, and I can't find a way to compare dates in string format "yyyyMMdd", ie Datediff(day, column, "value") == x, the database is SQLServer and changing the column type is not an option.

I'm using reflexion which complicates things.

the code in asp.net is

    public static Expression ExpressionsDiffDate(Expression cte, Expression property)
    {
        MethodInfo DateDiff = typeof(SqlFunctions).GetMethod("DateDiff", new Type[] { typeof(string), typeof(string), typeof(string) });

        if (property.Type == typeof(DateTime) || property.Type == typeof(DateTime?))
        {
            DateDiff = typeof(SqlFunctions).GetMethod("DateDiff", new Type[] { typeof(string), typeof(DateTime), typeof(DateTime) });
            property = Expression.Convert(property, typeof(DateTime?));
            cte = ConvertExpressionToDate(cte);
        }

        return Expression.Call(
                    DateDiff,
                    Expression.Constant("day"),
                    cte,
                    property
            );
    }

In this example I use the method datediff in the class sqlfunctions, that has plenty of overloads, depending on the column type I either use the one that takes datetime as parameters or the one that takes strings.

I managed to this for columns of type DateTime in .net core

    public static Expression ExpressionsDiffDate(Expression cte, Expression property)
    {
        ParameterExpression exFun = Expression.Parameter(typeof(DbFunctions));
        MethodInfo DateDiff = typeof(SqlServerDbFunctionsExtensions).GetMethod("DateDiffDay", new Type[] { typeof(DbFunctions), typeof(DateTime), typeof(DateTime) });

        return Expression.Call(
                    DateDiff,
                    exFun,
                    cte,
                    property
            );
    }

But I can't figure out how to do so for columns with the type string, since it does not have any overloads that take strings.

Thank you

Answer 1

The following "double cast" technique

(DateTime)(object)stringExpr

or

(DateTime?)(object)stringExpr

can be used to trick C# compiler to treat string type expression as DateTime, thus allows you to call DateTime or DateTime? overloads of DateDiff methods (or use DateTime operators).

EF Core query translator for SqlServer will generate CAST to datetime or datetime2 type, which should try default SqlServer conversion from string.

e.g. something like this

if (property.Type == typeof(string))
    property = Expression.Convert(Expression.Convert(property, typeof(object)), typeof(DateTime));