TimeSpan type does not map correctly to TIME MySQL type

[ptsneves]

Create a model with a Timespan

class MyModel {
   TimeSpan t;
}
---
MyModel m() {t = TimeSpan.MaxValue};
db_context.Add(m);
await db_context.SaveChangesAsync()

The TimeSpan type creates a TIME typed column which has a smaller constraint than TimeSpan. Specifically TIME only goes from '-838:59:59.000000' to '838:59:59.000000' . while TimeSpan can reach 256204778:48:05.477580.

Unhandled exception. Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
 ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Incorrect TIME value: '256204778:48:05.477580'
 ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Incorrect TIME value: '256204778:48:05.477580'
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ServerSession.cs:line 774
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 49
   at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 130
   at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 391
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\CommandExecutor.cs:line 62
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, 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.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, 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.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

[mguinness]

Take a look at Value Conversions, specifically TimeSpanToStringConverter and TimeSpanToTicksConverter if you need a timespan greater than a month.

[ptsneves]

@mguinness Thanks for the tip. Am i correct in interpreting the timespan converter as being translated to number of ticks, that is a Long in the database?
Also if there is nothing you can do i will close the bug report.

[lauxjpn]

The TimeSpan type creates a TIME typed column which has a smaller constraint than TimeSpan. Specifically TIME only goes from '-838:59:59.000000' to '838:59:59.000000' . while TimeSpan can reach 256204778:48:05.477580.

This is expected. We currently handle TimeSpan as values being smaller than 24 hours. So even new TimeSpan(1, 0, 0, 0) will not work. This mimics the behavior of SQL Server.

As @mguinness pointed out, using TimeSpanToTicksConverter is the way to go here. It can be used like this:

entity.Property(e => e.BestServedBefore)
    .HasColumnType("bigint")
    .HasConversion(new TimeSpanToTicksConverter());

It is equivalent to the following code:

entity.Property(e => e.BestServedBefore)
    .HasColumnType("bigint")
    .HasConversion(v => v.Ticks, v => new TimeSpan(v));

Here is a fully functional example:

using System;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Infrastructure;
using Pomelo.EntityFrameworkCore.MySql.Storage;

namespace IssueConsoleTemplate
{
    public class IceCream
    {
        public int IceCreamId { get; set; }
        public string Name { get; set; }
        public TimeSpan BestServedBefore { get; set; }
    }

    public class Context : DbContext
    {
        public DbSet<IceCream> IceCreams { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseMySql(
                    "server=127.0.0.1;port=3306;user=root;password=;database=Issue1046",
                    b => b
                        .ServerVersion(new ServerVersion("8.0.20-mysql"))
                        .CharSetBehavior(CharSetBehavior.NeverAppend))
                .UseLoggerFactory(
                    LoggerFactory.Create(
                        b => b
                            .AddConsole()
                            .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<IceCream>(
                entity =>
                {
                    entity.Property(e => e.BestServedBefore)
                        .HasColumnType("bigint")
                        .HasConversion(new TimeSpanToTicksConverter());
                    
                    entity.HasData(
                        new IceCream
                        {
                            IceCreamId = 1,
                            Name = "Vanilla",
                            BestServedBefore = new TimeSpan(0, 12, 0, 0)
                        },
                        new IceCream
                        {
                            IceCreamId = 2,
                            Name = "Chocolate",
                            BestServedBefore = new TimeSpan(0, 23, 59, 59)
                        },
                        new IceCream
                        {
                            IceCreamId = 3,
                            Name = "Artificial Vanilla",
                            
                            // This will not work out-of-the box.
                            // Usage of a converter is necessary.
                            BestServedBefore = new TimeSpan(42, 11, 0, 0)
                        }
                    );
                });
        }
    }

    internal class Program
    {
        private static void Main()
        {
            using var context = new Context();

            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            var iceCreams = context.IceCreams
                .OrderBy(i => i.IceCreamId)
                .ToList();

            Debug.Assert(iceCreams.Count == 3);
            Debug.Assert(iceCreams[0].BestServedBefore == new TimeSpan(0, 12, 0, 0));
            Debug.Assert(iceCreams[1].BestServedBefore == new TimeSpan(0, 23, 59, 59));
            Debug.Assert(iceCreams[2].BestServedBefore == new TimeSpan(42, 11, 0, 0));
        }
    }
}