日期时间文字中无法将类型为“System.String”的对象转换为类型“System.DateTime”

Question

我们有一个MySQL数据库，用于运行特定游戏类型的游戏主机。我有一个linq语句，可以返回主机ID、名称以及上一次在主机上玩的游戏的标题和开始时间（如果有）。

context.HostConfigurations.Select (h => new HostStatus {
                id = h.IdHostConfigurations,
                hostname = h.Hostname,
                gameTitle = h.IdGamesLast.HasValue ? h.LastGameNavigation.GameTypeNavigation.Title : "-",
                gameStart = h.IdGamesLast.HasValue ? h.LastGameNavigation.Gamestart : DateTimeOffset.MinValue,
                gameStartFriendly = h.IdGamesLast.HasValue ? h.LastGameNavigation.Gamestart.ToString ("ddd HH:mm") : "-"
            }).ToList ();

日志输出如下：

INFO: Microsoft.EntityFrameworkCore.Database.Command Executed DbCommand (21ms) [Parameters=[@__nowMinus24Hrs_0='?' (DbType = DateTimeOffset), @__nowMinusTenMinutes_1='?' (DbType = DateTimeOffset)], CommandType='Text', CommandTimeout='30']
SELECT `h`.`idHostConfigurations` AS `id`, `h`.`hostname`, CASE
    WHEN `h`.`idGamesLast` IS NOT NULL
    THEN `h.LastGameNavigation.GameTypeNavigation`.`title` ELSE '-'
END AS `gameTitle`, CASE
    WHEN `h`.`idGamesLast` IS NOT NULL
    THEN `h.LastGameNavigation`.`gamestart` ELSE '0001-01-01 00:00:00.000000'
END AS `gameStart0`, CASE
WHEN `h`.`idGamesLast` IS NOT NULL
    THEN TRUE ELSE FALSE
END, `h.LastGameNavigation`.`gamestart`
FROM `HostConfigurations` AS `h`
LEFT JOIN `Games` AS `h.LastGameNavigation` ON `h`.`idGamesLast` = `h.LastGameNavigation`.`idGames`
LEFT JOIN `GameTypes` AS `h.LastGameNavigation.GameTypeNavigation` ON `h.LastGameNavigation`.`idGameTypes` = `h.LastGameNavigation.GameTypeNavigation`.`idGameTypes`

ERROR: Microsoft.EntityFrameworkCore.Query An exception occurred while iterating over the results of a query for context type '...'.
System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.DateTime'.
   at MySqlConnector.Core.Row.GetDateTime(Int32 ordinal) in C:\projects\mysqlconnector\src\MySqlConnector\Core\Row.cs:line 285
   at MySqlConnector.Core.Row.GetDateTimeOffset(Int32 ordinal) in C:\projects\mysqlconnector\src\MySqlConnector\Core\Row.cs:line 288
   at MySql.Data.MySqlClient.MySqlDataReader.GetFieldValue[T](Int32 ordinal) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 261
   at lambda_method(Closure , DbDataReader )
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.BufferlessMoveNext(DbContext _, Boolean buffer, 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.AsyncQueryingEnumerable`1.AsyncEnumerator.MoveNext(CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext(CancellationToken cancellationToken)

如果我从lambda方法中注释掉'gameStart = ...'这一行，就不会发生异常。显然，由于MinValue的datetime文本在结果集中被解释为字符串，这是MySQL连接器的一个严重限制？

非常感谢任何解决方法！

- Anders Emil

Answer 1

有一个解决方法，您可以声明一个min date变量为DateTimeOffset? min = DateTimeOffset.MinValue并在查询中使用它。

context.HostConfigurations.Select (h => new HostStatus {
            id = h.IdHostConfigurations,
            hostname = h.Hostname,
            gameTitle = h.IdGamesLast.HasValue ? h.LastGameNavigation.GameTypeNavigation.Title : "-",
            gameStart = h.IdGamesLast.HasValue ? h.LastGameNavigation.Gamestart : min ,
            gameStartFriendly = h.IdGamesLast.HasValue ? h.LastGameNavigation.Gamestart.ToString ("ddd HH:mm") : "-"
        }).ToList ();

- pavinan

---

非常好，谢谢。因此选择DateTimeOffset参数将生成具有正确System.DateTime类型的结果集。我自己想不出来。 - Anders Emil

Answer 2

目前我已经找到了一个解决连接器问题的方法，通过强制本地计算表达式来实现：

        var hosts = await context.HostConfigurations.Include (h => h.LastGameNavigation).ThenInclude (lg => lg.GameTypeNavigation).ToListAsync ();

        var l = new List<HostStatus> ();

        hosts.ForEach (h => {
            l.Add (new HostStatus {
                id = h.IdHostConfigurations,
                hostname = h.Hostname,
                gameTitle = h.IdGamesLast.HasValue ? h.LastGameNavigation.GameTypeNavigation.Title : "-",
                gameStart = h.IdGamesLast.HasValue ? h.LastGameNavigation.Gamestart : DateTimeOffset.MinValue,
                gameStartFriendly = h.IdGamesLast.HasValue ? h.LastGameNavigation.Gamestart.ToString ("ddd HH:mm") : "-",
            });
        });

- Anders Emil