时间戳

首页 > 解决方案 > .Net Core 3 Mysql Pomelo DateTime 不兼容

mysql - .Net Core 3 Mysql Pomelo DateTime 不兼容

问题描述

我想使用.Net Core 3.1.3 Pomelo.EntityFrameworkCore.MySql v3.1.1 获取两个日期之间的任何数据

当我尝试使用 C# datetime 时，没有数据返回：

SELECT * FROM Availability where (StartDate >= '26.03.2020 00:43:47' And StartDate <= '26.03.2020 00:43:47') or (EndDate <= '26.06.2020 00:43:47' And EndDate <= '26.06.2020 00:43:47')

但我尝试 Mysql datetime 返回一些数据：

SELECT * FROM Availability where (StartDate >= '2020-03-26 00:43:47' And StartDate <= '2020-03-26 00:43:47') or (EndDate <= '2020-06-26 00:43:47' And EndDate <= '2020-06-26 00:43:47')

我的 C# 代码：

prop => prop.Availabilities.Any(o => (o.StartDate <= model.StartDate && o.StartDate >= model.StartDate) || (o.EndDate <= model.EndDate && o.EndDate >= model.EndDate))

我可以在 .Net Core 代码上做什么？

谢谢。

标签： mysqlasp.net-corepomelo-entityframeworkcore-mysql

解决方案

看看下面的代码，它演示了如何System.DateTime在 EF Core 中使用，并且没有问题：

using System;
using System.Diagnostics;
using System.Globalization;
using System.Linq;
using System.Threading;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate
{
    public class IceCream
    {
        public int IceCreamId { get; set; }
        public string Name { get; set; }
        public DateTime 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=So61433252",
                    b => b.ServerVersion("8.0.20-mysql"))
                .UseLoggerFactory(
                    LoggerFactory.Create(
                        b => b
                            .AddConsole()
                            .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<IceCream>()
                .HasData(
                    new IceCream
                    {
                        IceCreamId = 1,
                        Name = "Vanilla",
                        BestServedBefore = DateTime.Today.AddDays(30)
                    },
                    new IceCream
                    {
                        IceCreamId = 2,
                        Name = "Chocolate",
                        BestServedBefore = new DateTime(2020, 5, 1)
                    }
                );
        }
    }

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

                // This code will work with *any* current culture, because EF Core and
                // Pomelo don't use the current culture for DateTime formatting.
                // Demonstrated here by explicitly setting the German culture.
                // This is of course *not necessary* and just for demonstration
                // puroposes.
                Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("de-DE");

                // The following two queries use both, a literal `DATETIME`
                // value and a `System.DateTime` parameter.
                var today = DateTime.Today;

                var badIceCreams = context.IceCreams
                    .Where(i => i.BestServedBefore <= new DateTime(2020, 5, 6) ||
                                i.BestServedBefore <= today)
                    .ToList();

                var isAnyIceCreamBad = context.IceCreams
                    .Any(i => i.BestServedBefore <= new DateTime(2020, 5, 6) ||
                              i.BestServedBefore <= today);

                Debug.Assert(badIceCreams.Count == 1);
                Debug.Assert(badIceCreams[0].IceCreamId == 2);
                Debug.Assert(isAnyIceCreamBad == true);
            }
        }
    }
}

它生成以下 SQL 语句：

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      CREATE DATABASE `So61433252`;

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (24ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      CREATE TABLE `IceCreams` (
          `IceCreamId` int NOT NULL AUTO_INCREMENT,
          `Name` longtext CHARACTER SET utf8mb4 NULL,
          `BestServedBefore` datetime(6) NOT NULL,
          CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)
      );

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      INSERT INTO `IceCreams` (`IceCreamId`, `BestServedBefore`, `Name`)
      VALUES (1, '2020-06-05 00:00:00', 'Vanilla');

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      INSERT INTO `IceCreams` (`IceCreamId`, `BestServedBefore`, `Name`)
      VALUES (2, '2020-05-01 00:00:00', 'Chocolate');

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (8ms) [Parameters=[@__today_0='2020-05-06T00:00:00' (DbType = DateTime)], CommandType='Text', CommandTimeout='30']

      SELECT `i`.`IceCreamId`, `i`.`BestServedBefore`, `i`.`Name`
      FROM `IceCreams` AS `i`
      WHERE (`i`.`BestServedBefore` <= '2020-05-06 00:00:00') OR (`i`.`BestServedBefore` <= @__today_0)

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@__today_0='2020-05-06T00:00:00' (DbType = DateTime)], CommandType='Text', CommandTimeout='30']

      SELECT EXISTS (
          SELECT 1
          FROM `IceCreams` AS `i`
          WHERE (`i`.`BestServedBefore` <= '2020-05-06 00:00:00') OR (`i`.`BestServedBefore` <= @__today_0))

如您所见，即使我们将当前文化显式设置为de-DE（使用dd.MM.yyyy HH:mm:ss日期格式），该System.DateTime值仍然使用以下yyyy-MM-dd HH:mm:ss格式正确格式化：

WHERE (`i`.`BestServedBefore` <= '2020-05-06 00:00:00')

但是，由于您通常会使用变量来过滤日期，因此这些变量将被转换为参数，因此实际上您的查询根本不包含任何DATETIME文字，而仅包含参数引用。这可以在使用today变量的示例代码中看到，该变量转换为以下 SQL 片段：

OR (`i`.`BestServedBefore` <= @__today_0)

最后，您可以看到Any()LINQ 查询转换为SELECT EXISTS (SELECT 1 FROM ... WHERE ...)SQL 查询，这与您在问题中显示的查询不同。

所以看起来你可能在你的代码中做了一些非常根本性的错误。

推荐阅读