Linq expression generated SQL formatting issues

[TorchZhu]

File a bug

Remember:

I am using Linq expression to dynamically splice SQL conditional expressions, and I get an exception when the model property is a 'DateTime' type:

  SELECT COUNT(*)
  FROM [TBL_Schedule] AS [t]
  WHERE (((([t].[ProjectName] = N'CY91928') AND ([t].[TestType] = N'CG_01')) AND ([t].[CreateDate] >= '2020-01-13T08:36:28.0000000')) AND ([t].[CreateDate] <= '2021-01-13T08:36:28.0000000')) AND ([t].[TestTerm] = N'CG_01_17')

Include your code

    public static Expression<Func<T, bool>> GetGreaterThanOrEqual<T>(string propertyName, string propertyValue, Type fieldType) where T : class
    {
        ParameterExpression parameter = Expression.Parameter(typeof(T), "p");//创建参数p
        MemberExpression member = Expression.PropertyOrField(parameter, propertyName);
        dynamic val = GetValue(fieldType, propertyValue);
        ConstantExpression constant = Expression.Constant(val);//创建常数
        return Expression.Lambda<Func<T, bool>>(Expression.GreaterThanOrEqual(member, constant), parameter);
    }

    public static Expression<Func<T, bool>> GetLessThanOrEqual<T>(string propertyName, string propertyValue, Type fieldType) where T : class
    {
        ParameterExpression parameter = Expression.Parameter(typeof(T), "p");//创建参数p
        MemberExpression member = Expression.PropertyOrField(parameter, propertyName);
        dynamic val = GetValue(fieldType, propertyValue);
        ConstantExpression constant = Expression.Constant(val);//创建常数
        return Expression.Lambda<Func<T, bool>>(Expression.LessThanOrEqual(member, constant), parameter);
    }

    /// <summary>
    /// 根据条件获取全部，并分页
    /// </summary>
    public Task<List<T>> GetAll(out int total, Expression<Func<T, bool>> where = null, int pageIndex = 1, int pageSize = 20, Expression<Func<T, object>> sort = null, Expression<Func<T, object>> descSort = null)
    {
        pageIndex = pageIndex < 1 ? 1 : pageIndex;
        pageSize = pageSize < 1 ? 1 : pageSize;
        var query = db.Set<T>().AsTracking();
        if (where != null)
        {
            query = query.Where(where);
        }
        total = query.Count(); **An exception was thrown here**
        if (sort != null)
        {
            query = query.OrderBy(sort);
        }
        if (descSort != null)
        {
            query = query.OrderByDescending(descSort);
        }
        return query.Skip(pageSize * (pageIndex - 1)).Take(pageSize).ToListAsync();
    }

Include stack traces

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT COUNT(*)
FROM [TBL_Schedule] AS [t]
WHERE (((([t].[ProjectName] = N'CY91928') AND ([t].[TestType] = N'CG_01')) AND ([t].[CreateDate] >= '2020-01-13T08:36:28.0000000')) AND ([t].[CreateDate] <= '2021-01-13T08:36:28.0000000')) AND ([t].[TestTerm] = N'CG_01_17')
fail: Microsoft.EntityFrameworkCore.Query[10100]
An exception occurred while iterating over the results of a query for context type 'ORT.Common.DAL.ORTContext'.
Microsoft.Data.SqlClient.SqlException (0x80131904): 从字符串转换日期和/或时间时，转换失败。
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
at Microsoft.Data.SqlClient.SqlDataReader.Read()
at Microsoft.EntityFrameworkCore.Storage.RelationalDataReader.Read()
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.MoveNext() ClientConnectionId:10f13174-5cc3-442d-ab1e-2a15e65b476e Error Number:241,State:1,Class:16 Microsoft.Data.SqlClient.SqlException (0x80131904): 从字符串转换日期和/或时间时，转换失败。 at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows) at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more) at Microsoft.Data.SqlClient.SqlDataReader.Read() at Microsoft.EntityFrameworkCore.Storage.RelationalDataReader.Read() at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.MoveNext()
ClientConnectionId:10f13174-5cc3-442d-ab1e-2a15e65b476e
Error Number:241,State:1,Class:16

Include provider and version information

EF Core version: 3.1.5
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: NET 3.1
Operating system:Windows10
IDE: Visual Studio 2019 16.8.3

[smitpatel]

When converting date and/or time from string, conversion failed

Likely the CreatedDate comparison constants have wrong type mapping. Can you try on 3.1.5 5.0.3 to see if it is fixed. If not, please share the model.

[ajcvickers]

EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.