如何解决SqlError :failed when converting date and/or time from character string. EF二进制表达式？[英] how to fix SqlError :failed when converting date and/or time from character string. EF binaryexpression?

问题描述

I am trying to use expressions to dynamically create comparison operations to use with Entity Framework Core 2.2. Works fine with everything but date.

This is the query

new System.Linq.Expressions.Expression.MethodCallExpressionProxy(((Remotion.Linq.QueryableBase<ORMModel.Sale>)tq).Expression).DebugView

.Call System.Linq.Queryable.Where(
.Constant<Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[ORMModel.Sale]>(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[ORMModel.Sale]),
'(.Lambda #Lambda1<System.Func`2[ORMModel.Sale,System.Boolean]>))

.Lambda #Lambda1<System.Func`2[ORMModel.Sale,System.Boolean]>(ORMModel.Sale $Sale) {
$Sale.CreateDT > (System.DateTime).Constant<System.DateTime>(12/25/2018 5:00:00 AM)
}

This is the error I get

System.Data.SqlClient.SqlError
Conversion failed when converting date and/or time from character string.

I have used that date value directly in SQL Server Management Studio and it executes.

Any help or clues for the clueless appreciated. Too much googling and getting nowhere. Every example is SQL strings not dynamic expressions.

public static Expression GetBinaryExpression<TSource>(string propertyName, int comparer, dynamic valueIn)
{
    PropertyInfo pi = GetPropertyInfo<TSource>(propertyName);
    Type pt = pi.PropertyType;

    var parameter = Expression.Parameter(typeof(TSource), typeof(TSource).Name);
    var property = Expression.Property(parameter, propertyName);

    dynamic valueTyped;

    if (IsNullableType(pt))
    {
        valueTyped = Convert.ChangeType(valueIn, Nullable.GetUnderlyingType(pt));
    }
    else
    {
        valueTyped = Convert.ChangeType(valueIn, pt);
    }

    var constant = Expression.Constant(valueTyped);

    var val = Expression.Convert(constant, pt);

    Expression exp = null;

    switch (comparer)
    {
        case 1:
            exp = Expression.Equal(property, val);
            break;
        case 2:
            exp = Expression.NotEqual(property, val);
            break;
        case 3:
            exp = Expression.GreaterThan(property, val);
            break;
        case 4:
            exp = Expression.GreaterThanOrEqual(property, val);
            break;
        case 5:
            exp = Expression.LessThan(property, val);
            break;
        case 6:
            exp = Expression.LessThanOrEqual(property, val);
            break;
    }

    if (exp.CanReduce) exp = exp.Reduce();

    return Expression.Lambda<Func<TSource, Boolean>>(exp, parameter);
}

private static bool IsNullableType(Type type)
{
    return type.IsGenericType && 
    type.GetGenericTypeDefinition().Equals(typeof(Nullable<>));
}

I expect the return of a beautiful result set instead of an exasperating error.

推荐答案

SQL Server Data Type Mappings is the solution to the problem. From my researching, the SqlCommand object relies on data type mapping to convert from CLR to SQL data types.

Using constant expressions won't help when it comes to DateTime as it is serialized to a string format like this:

'2020-05-03T14:32:05.000435Z'

E.g the generated sql would look like this:

SELECT [s].[Routine], [s].[CreatedOn] FROM Routines as s
WHERE [s].[CreatedOn] >= '2020-05-03T14:32:05.000435Z'

If the date string format is something like this: ''2020-05-03T14:32:05' then the sql server would be happy, but alas Expression are so strict with it comes to data type and you can't pass this string format for a DateTime property.

Enough blabbing, let's go through some code:

...

Expression<Func<TProperty>> exprValueVar = () => valueTyped;

exp = Expression.Equal(property, exprDateVar.Body);

...

The lambda expression:

Expression<Func<TProperty>> exprValueVar = () => valueTyped;

does the trick and allows DbSet to generate an sql parametized command.

Once executed with a DbSet, the resulting IQueryable would be a command with parameters. That can be verified from the generated sql and data type mapping can now convert the CLR data type to SQL data type without throwing the dreaded 'Conversion failed when converting date and/or time from character string'.