如何解决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?

本文是小编为大家收集整理的关于如何解决SqlError :failed when converting date and/or time from character string. EF二进制表达式?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

本文来自:IT宝库(https://www.itbaoku.cn)

我正在尝试使用表达式来动态创建与实体框架核心2.2一起使用的比较操作.除了日期以外的一切都很好.

这是查询

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)
}

这是我得到的错误

system.data.sqlclient.sqlerror
转换日期和/或从字符串转换时间时失败.

我直接在SQL Server Management Studio中使用了该日期值,并且执行.

无知的任何帮助或线索.太多的谷歌搜索和无处可去.每个示例是SQL字符串而不是动态表达式.

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<>));
}

我期望一个美丽的结果设置而不是令人讨厌的错误.

推荐答案

SQL Server数据类型映射是解决问题的解决方案.从我的研究中,SQLCommand对象依赖于数据类型映射将从CLR转换为SQL数据类型.

使用常数表达式在DateTime方面无济于事,因为它已序列化为类似的字符串格式:

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

例如,生成的SQL看起来像这样:

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

如果日期字符串格式是这样的:''2020-05-03T14:32:05',那么SQL Server会很高兴,但是Alas Expression非常严格,因此数据类型传递此字符串格式的DateTime属性.

足够的贴花,让我们浏览一些代码:

...

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

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

...

lambda表达式:

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

可以做到这一点,并允许DBSET生成SQL参数命令.

一旦用DBSET执行,由此产生的iQueryable将是带有参数的命令.可以从生成的SQL进行验证,并且数据类型映射现在可以将CLR数据类型转换为SQL数据类型,而无需抛出可怕的"转换日期和/或从字符字符串转换时间"的"转换失败".

其他推荐答案

Wizzyno的答案为我解决了问题.这是完整的实现:

private static Expression GetValueExpression(object valueTyped)
{
    if (valueTyped is DateTime)
    {
        var dateTimeValue = (DateTime)valueTyped;
        Expression<Func<DateTime>> exprValueVar = () => dateTimeValue;
        return exprValueVar.Body;
    }

    return Expression.Constant(valueTyped);
}

用法...

var valueExpression = GetValueExpression(typedValue);
exp = Expression.Equal(property, valueExpression);

本文地址:https://www.itbaoku.cn/post/2558227.html

问题描述

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'.

其他推荐答案

The answer from Wizzyno solved the problem for me. Here's the full implementation:

private static Expression GetValueExpression(object valueTyped)
{
    if (valueTyped is DateTime)
    {
        var dateTimeValue = (DateTime)valueTyped;
        Expression<Func<DateTime>> exprValueVar = () => dateTimeValue;
        return exprValueVar.Body;
    }

    return Expression.Constant(valueTyped);
}

Usage...

var valueExpression = GetValueExpression(typedValue);
exp = Expression.Equal(property, valueExpression);