Range<DateTime> Conversion failed when converting date and/or time from character string

blackWins commented 3 months ago

Exception occurred when using range filtering

Project information：abp v8.1.3

filter model

public class MyFilterRequestDto : FilterBase
 {
      public virtual Range<DateTime> CreateTime { get; set; }
}


2024-05-29 15:26:11.303 +08:00 [ERR] Conversion failed when converting date and/or time from character string.
Microsoft.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from character string.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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.ReadAsyncExecute(Task task, Object state)
   at Microsoft.Data.SqlClient.SqlDataReader.InvokeAsyncCall[T](SqlDataReaderBaseAsyncCallContext`1 context)
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.BufferedDataRecord.InitializeAsync(DbDataReader reader, IReadOnlyList`1 columns, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.InitializeAsync(IReadOnlyList`1 columns, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.InitializeAsync(IReadOnlyList`1 columns, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
   at Volo.Abp.Application.Services.AbstractKeyReadOnlyAppService`5.GetListAsync(TGetListInput input)
   at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo)
   at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue`1.ProceedAsync()
   at Volo.Abp.GlobalFeatures.GlobalFeatureInterceptor.InterceptAsync(IAbpMethodInvocation invocation)
   at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter`1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func`3 proceed)
   at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo)
   at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue`1.ProceedAsync()
   at Volo.Abp.Validation.ValidationInterceptor.InterceptAsync(IAbpMethodInvocation invocation)
   at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter`1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func`3 proceed)
   at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo)
   at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue`1.ProceedAsync()
   at Volo.Abp.Authorization.AuthorizationInterceptor.InterceptAsync(IAbpMethodInvocation invocation)
   at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter`1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func`3 proceed)
   at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo)
   at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue`1.ProceedAsync()
   at Volo.Abp.Auditing.AuditingInterceptor.InterceptAsync(IAbpMethodInvocation invocation)
   at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter`1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func`3 proceed)
   at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo)
   at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue`1.ProceedAsync()
   at Volo.Abp.Uow.UnitOfWorkInterceptor.InterceptAsync(IAbpMethodInvocation invocation)
   at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter`1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func`3 proceed)
   at lambda_method3601(Closure, Object)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|26_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
ClientConnectionId:c5d7060c-8d9e-4950-9ae6-f51fdeb3bb96
Error Number:241,State:1,Class:16
2024-05-29 15:26:11.303 +08:00 [ERR] ---------- Exception Data ----------
HelpLink.ProdName = Microsoft SQL Server
HelpLink.ProdVer = 15.00.4198
HelpLink.EvtSrc = MSSQLServer
HelpLink.EvtID = 241
HelpLink.BaseHelpUrl = https://go.microsoft.com/fwlink
HelpLink.LinkId = 20476

enisn commented 3 months ago

Thanks for reporting with the example code, I'll check it asap

enisn commented 3 months ago

It's probably happened after PR https://github.com/enisn/AutoFilterer/pull/69

enisn commented 3 months ago

Hand-written query expression is compiled as filter.CreateTime.Min <= Convert(x.CreateTime, Nullable'1) instead x.CreateTime >= 2023-08-12 3:17:30 PM

It's probably a Nullable check problem. I'll work on it

enisn commented 3 months ago

It works with SqlServer provider, can you provide me which Entity Framework provider do you use in this case?

blackWins commented 3 months ago

SqlServer

blackWins commented 3 months ago

abp project file

MyAbpEntityFrameworkCoreModule.cs

    public override void ConfigureServices(ServiceConfigurationContext context)
    {
        context.Services.AddAbpDbContext<ManageDbContext>(options =>
        {
            options.AddDefaultRepositories(includeAllEntities: true);
        });

        context.Services.AddAbpDbContext<MyAbpDbContext>(options =>
        {
            options.AddDefaultRepositories(includeAllEntities: true);
        });

        Configure<AbpDbContextOptions>(options =>
        {
            options.UseSqlServer();
        });

    }

ManageDbContext

[ConnectionStringName("manage")]
public class ManageDbContext: AbpDbContext<ManageDbContext>
{
    public ManageDbContext(DbContextOptions<ManageDbContext> options)
        : base(options)
    {
    }

    public DbSet<ProjectInfo> ProjectInfos { get; set; }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);
        builder.Entity<ProjectInfo>(b =>
        {
            b.ToTable(MyAbpConsts.DbTablePrefix + "ProjectInfo");
            b.ConfigureByConvention();
        });
   }
}

ProjectInfoAppService

public class ProjectInfoAppService : CrudAppService<ProjectInfo, ProjectInfoDto, Guid, ProjectInfoGetListInput, CreateUpdateProjectInfoDto, CreateUpdateProjectInfoDto>, IProjectInfoAppService
{
    public ProjectInfoAppService(IRepository<ProjectInfo, Guid> repository) : base(repository)
    {
    }

    protected override async Task<IQueryable<ProjectInfo>> CreateFilteredQueryAsync(ProjectInfoGetListInput input)
    {
        return (await base.CreateFilteredQueryAsync(input)).ApplyFilter(input);
    }
}

blackWins commented 3 months ago

Here's the problem .0000000

  SELECT COUNT(*)
  FROM [StockInfo] AS [s]
  WHERE CASE
      WHEN [s].[CreateTime] >= '2024-06-01T00:00:00.0000000' THEN CAST(1 AS bit)
      ELSE CAST(0 AS bit)
  END & CASE
      WHEN [s].[CreateTime] <= '2024-06-29T00:00:00.0000000' THEN CAST(1 AS bit)
      ELSE CAST(0 AS bit)
  END = CAST(1 AS bit)

enisn commented 3 months ago

It's generated by the EF Core provider. AutoFilterer doesn't generate SQL queries. It generates only LINQ expressions.

Does it work when you write it manually like below?

.Where(x => x.CreationTime <= DateTime.Now)

blackWins commented 3 months ago

.Where(x => x.CreationTime <= DateTime.Now) The execution was successful

blackWins commented 3 months ago

    protected override async Task<IQueryable<StockInfo>> CreateFilteredQueryAsync(StockInfoGetListInput input)
    {
        return (await base.CreateFilteredQueryAsync(input))
        .Where(x => x.CreateTime > input.CreateTime.Min && x.CreateTime < input.CreateTime.Max);
        //.ApplyFilter(input);
    }

enisn commented 3 months ago

Which AutoFilterer version are you currently using? This has to be parameterized after PR #59

blackWins commented 3 months ago

3.0.0

blackWins commented 3 months ago

This issue occurs in sqlserver 2016

enisn / AutoFilterer