Input string was not in a correct format when compare with TimeSpan

[hez2010]

Steps to reproduce

The model:

class Table
{
    public int Id { get; set; }
    public DateTimeOffset Time { get; set; }
}

await dbContext.Table.Where(i => i.Time - DateTime.UtcNow < TimeSpan.FromMinutes(42)).ToListAsync();

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 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()

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

[lauxjpn]

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):

await dbContext.Table.Where(i => EF.Functions.DateDiffMinute(DateTimeOffset.UtcNow, i.Time) < 42).ToListAsync();

(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.