Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
blackWins opened this issue May 29, 2024 · 13 comments
Open

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

blackWins opened this issue May 29, 2024 · 13 comments
Labels
autofilterer bug Something isn't working

Comments

@blackWins
Copy link

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

image


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 enisn added bug Something isn't working autofilterer labels May 29, 2024
@enisn
Copy link
Owner

enisn commented May 29, 2024

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

@enisn
Copy link
Owner

enisn commented May 29, 2024

It's probably happened after PR #69

@enisn
Copy link
Owner

enisn commented May 29, 2024 •
edited
Loading

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

image

@enisn
Copy link
Owner

enisn commented Jun 4, 2024

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

@blackWins
Copy link
Author

SqlServer

@blackWins
Copy link
Author

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
Copy link
Author

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
Copy link
Owner

enisn commented Jun 7, 2024

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
Copy link
Author

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

@blackWins
Copy link
Author 

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

image

@enisn
Copy link
Owner

enisn commented Jun 7, 2024

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

@blackWins
Copy link
Author

3.0.0

@blackWins
Copy link
Author

This issue occurs in sqlserver 2016

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
autofilterer bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@enisn @blackWins