search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.63k stars 3.15k forks source link

Conversion failed when converting date and/or time from character string #23500

Closed Shiko1st closed 1 year ago

Shiko1st commented 3 years ago

It seems a breaking change from EF Core 3.1 to EF Core 5. I having this error while execution of custom query that worked well in EF Core 3.1:

An exception occurred while iterating over the results of a query for context type 'xxx.Data.Dal.yyyDbContext'.
Microsoft.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from character string.

This error occured both on Azure SQL Server and MsSqlLocalDb on my local machine.

What do we have

1. No-key entity

public class DailyResultInfo
{
    public DateTime Day { get; set; }
    public Guid? DailyResultId { get; set; }
    public Guid? EmployeeId { get; set; }
}
builder.Entity<DailyResultInfo>().HasNoKey();

2. Custom query

2.1. Query itself

select
    days.Day
    ,results.Id as DailyResultId
    ,results.CreatedById as EmployeeId
from zzz.GetDays('2020-11-23', 4) days
left join zzz.DAILY_RESULTS results
    on days.Day = results.Day and results.CreatedById = 'c2aed1b6-99a1-4aa9-728f-08d88f619a78'

2.2. DAILY_RESULTS table

Is described by this class:

[Table("DAILY_RESULTS")]
public class DailyResult
{
    public Guid Id { get; set; }
    public DateTime Day { get; set; }
    public Guid CreateById { get; set; }
    public DateTimeOffset CreatedAt { get; set; }
}

2.3. DAILY_RESULTS.Day field is defined as date by conversion from other field:

eb.Property(e => e.Day)
    .HasColumnType("date")
    .HasComputedColumnSql($"CAST({nameof(DailyResult.CreatedAt)} AS date) PERSISTED");

2.4. GetDays is a function that generates a list of dates

It returns a list of dates starting from a specified date. It allows us to have a record in result set even if there were no record in DAILY_RESULTS table for some day and specified user.

create function zzz.GetDays (@StartDate as date, @Count as int) returns table as return (
    select top (@Count)
        DATEADD(day, CAST(ROW_NUMBER() over (order by s1.[object_id]) as int) - 1, @StartDate) as Day
    from sys.all_objects as s1
    cross join sys.all_objects as s2
);
go

2.5. Query execution

Query is executed like this:

var data = await DbContext.Set<DailyResultInfo>()
    .FromSqlRaw(queryString, startDay.ToString("yyyy-MM-dd"), daysCount, employee.Id);
    .OrderByDescending(d => d.Day)
    .ToArrayAsync();

Where queryString is a custom query described in (1). EF Core 5 translates this into something like this:

Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=[@__p_1='14', p0='2020-10-31' (Size = 4000), p1='27', p2='8db9be21-369a-4fdc-209f-08d87df4cc02'], CommandType='Text', CommandTimeout='300']
SELECT TOP(@__p_1) [d].[DailyResultId], [d].[Day], [d].[EmployeeId]
    FROM (
        select
            days.Day
            ,results.Id as DailyResultId
            ,results.CreatedById as EmployeeId
        from zzz.GetDays('@p0', @p1) days
        left join zzz.DAILY_RESULTS results
            on days.Day = results.Day and results.CreatedById = '@p2'
) AS [d]
ORDER BY [d].[Day] DESC

The problem

Everything was OK on EF Core 3.1.

But after migration to EF Core 5, this query leads to this exception:

An exception occurred while iterating over the results of a query for context type 'xxx.Data.Dal.yyyDbContext'.
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, TdsParserStateO
bject 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](AAsyncCallContext`1 context)
--- End of stack trace from previous location ---
    at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.BufferedDataRecord.InitializeAsync(DbDataReader reader, IReadOnlyList`1 columns, CancellationToken cancellat
ionToken)
    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.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationTok
en)
    at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken
 cancellationToken)
    at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken
 cancellationToken)
    at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
      ClientConnectionId:eb2f9bda-19d8-417f-9958-9b9d3616a514
      Error Number:241,State:1,Class:16
      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, TdsParserStateO
bject 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](AAsyncCallContext`1 context)
--- End of stack trace from previous location ---
    at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.BufferedDataRecord.InitializeAsync(DbDataReader reader, IReadOnlyList`1 columns, CancellationToken cancellat
ionToken)
    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.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationTok
en)
    at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken
 cancellationToken)
    at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken
 cancellationToken)
    at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
ClientConnectionId:eb2f9bda-19d8-417f-9958-9b9d3616a514
Error Number:241,State:1,Class:16

I've found #14095 where people mentioned that there were a problem with conversion to DateTime in certain conditions. I tried to play with types and type conversion in various combinations:

...but nothing helps.

Attempt to specify sql type directly in model builder like this eb.Property(e => e.Day).HasColumnType("date"); also doesn't help.

Remove sorting doesn't help.

I tried to trace this in debugger and on the first look it shows me that TdsParser even don't enter actual data parsing.

The same query being executed using Dapper over the same DbConnection and being executed using external tool like DataGrip parsed well and returns data as expected.

Environment

.NET Core 5 ASP.NET Core 5 Microsoft.EntityFrameworkCore.SqlServer 5.0.0 Microsoft.Data.SqlClient 2.1.0 Azure SQL Server MsSqlLocalDb OS: Win7 IDE: Rider 2020.2.4

smitpatel commented 3 years ago

We need a stand-alone repro here. There are too many errors in the snippets above to effectively reproduce the error. The root cause of error is when trying to convert a character string to one of the datetime related types in SQL when the format of the string does not identify the requested type correctly. That means it is data dependent error.

Further, in the snippets above

Shiko1st commented 3 years ago 
* no info about `DAILY_RESULTS` table

Appended to description as §2.2

* What exactly is the computed column SQL setting

Was described initially, now §2.3

* What is queryString in the LINQ query?

Query described at the beginning of the "Custom query" section, now §2.1

* In the generated SQL the user provided SQL contains cast to datetimeoffset which can be one of the error point.

This is a custom query I'm talking about. Initially there were no cast (see §2.1). When this error happend I tried to play with various output date types in custom query and DailyResultInfo class.

* zzz.GetDays is passing parameter value of first parameter in quotes. For constant quotes would be needed but not when passing value as parameter.

It seems this is the problem. I've got the same error playing with parameters in query editor. But this call was not changed and worked in EF Core 3.1 welll... Is it possible EF Core 5 formats final query in different way?

smitpatel commented 3 years ago

Is it possible EF Core 5 formats final query in different way?

We haven't changed anything in this area. You should be able to compare the SQL generated in 3.1/5.0 easily.

ajcvickers commented 3 years ago

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.