You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Input string was not in a correct format when compare with TimeSpan.
The format string was incorrectly set to "yyyy-MM-dd HH\\:mm\\:ss.FFFFFF" by Pomelo, while for TimeSpan, it shouldn't contains the Date part in the format string.
Exception message: System.FormatException: Input string was not in a correct format.
Stack trace:
at System.Globalization.TimeSpanFormat.FormatCustomized[TChar](TimeSpan value, ReadOnlySpan`1 format, DateTimeFormatInfo dtfi, ValueListBuilder`1& result)
at System.Globalization.TimeSpanFormat.TryFormat[TChar](TimeSpan value, Span`1 destination, Int32& charsWritten, ReadOnlySpan`1 format, IFormatProvider formatProvider)
at System.Text.ValueStringBuilder.AppendFormatHelper(IFormatProvider provider, String format, ReadOnlySpan`1 args)
at System.String.FormatHelper(IFormatProvider provider, String format, ReadOnlySpan`1 args)
at System.String.Format(IFormatProvider provider, String format, Object arg0)
at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMapping.GenerateNonNullSqlLiteral(Object value)
at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlDateTimeOffsetTypeMapping.GenerateProviderValueSqlLiteral(Object value)
at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMapping.GenerateSqlLiteral(Object value)
at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitSqlConstant(SqlConstantExpression sqlConstantExpression)
at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlQuerySqlGenerator.VisitExtension(Expression extensionExpression)
at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlQuerySqlGenerator.VisitSqlBinary(SqlBinaryExpression sqlBinaryExpression)
at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlQuerySqlGenerator.VisitExtension(Expression extensionExpression)
at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlQuerySqlGenerator.VisitSqlBinary(SqlBinaryExpression sqlBinaryExpression)
at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlQuerySqlGenerator.VisitExtension(Expression extensionExpression)
at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitSelect(SelectExpression selectExpression)
at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GenerateRootCommand(Expression queryExpression)
at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GetCommand(Expression queryExpression)
at Microsoft.EntityFrameworkCore.Query.Internal.RelationalCommandCache.GetRelationalCommandTemplate(IReadOnlyDictionary`2 parameters)
at Microsoft.EntityFrameworkCore.Internal.RelationCommandCacheExtensions.RentAndPopulateRelationalCommand(RelationalCommandCache relationalCommandCache, RelationalQueryContext queryContext)
at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
So this is partly a bug and partly a missing feature.
The bug part is the weird exception that is thrown instead of a clear one that explains why this doesn't currently work. Which brings us to the missing feature part.
We currently do not officially support to directly substract DateTime or DateTimeOffset values from each other, because MySQL does not have a great data type to represent durations/time spans.
We currently represent a CLR TimeSpan as a MySQL time type, which isn't great due to its limited range of -838:59:59 to 838:59:59.
To fix that, we would need to represent TimeSpan values probably as ticks with a bigint type. This should be entirely possible, we just haven't implemented it yet. And since this would be a breaking change, we can only implement this for a new major version of Pomelo and would need to provide the option for users to enable the legacy behavior if desired.
@hez2010 The currently supported way to do what you want is to use the EF.Functions.DateDiffMinute() extension method (which uses the MySQL TIMESTAMPDIFF() function):
(This assumes, as your OP code does, that i.Time is initially usually greater than the current date/time. Otherwise, swap arguments. Also, we might rename DateDiff...() to TimestampDiff..() for the 9.0 release.)
Internal implementation details for us to later implement this:
The SQL Server provider throws a clear exception in SqlServerSqlTranslatingExpressionVisitor.VisitBinary().
Npgsql implements working code.
Affected areas are especially MySqlSqlTranslatingExpressionVisitor.VisitBinary() and MySqlSqlExpressionFactory.ApplyTypeMappingOnSqlBinary().
We do not want to backport any of this (neither the bigint implementation, nor the clear exception) to ensure that we don't break existing code in our stable branches.
Steps to reproduce
The model:
The issue
Input string was not in a correct format when compare with TimeSpan.
The format string was incorrectly set to
"yyyy-MM-dd HH\\:mm\\:ss.FFFFFF"
by Pomelo, while forTimeSpan
, it shouldn't contains the Date part in the format string.Further technical details
MySQL version: 8.0.36
Operating system: Ubuntu 22.04 x64
Pomelo.EntityFrameworkCore.MySql version: 8.0.2
Microsoft.AspNetCore.App version: 8.0
The text was updated successfully, but these errors were encountered: