.Net Core 3 Mysql Pomelo DateTime Incompatibility

Question

I want to get any data between two dates with .Net Core 3.1.3 Pomelo.EntityFrameworkCore.MySql v3.1.1

When I try with C# datetime no data returns:

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

But I try Mysql datetime returns some data:

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

My C# codes:

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

What i can do on .Net Core codes?

Thanks.

Answer 1

Take a look at the following code, that demonstrates how to use System.DateTime in EF Core, and works without issues:

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);
            }
        }
    }
}

It generates the following SQL statementes:

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

As you can see, even though we explicitly set the current culture to de-DE (which uses the dd.MM.yyyy HH:mm:ss date format), the System.DateTime value is still correctly formated using the yyyy-MM-dd HH:mm:ss format:

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

However, because you would normally use variables to filter the date, those variables would be translated to parameters, so in practice your queries will not contain any DATETIME literals at all, but only parameter references. This can be seen in the sample code using the today variable, that translates to the following SQL fragment:

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

Finally, you can see that an Any() LINQ query translates to a SELECT EXISTS (SELECT 1 FROM ... WHERE ...) SQL query, which is different from the one you have shown in your question.

So it looks like you might be doing something very fundamentally wrong in your code.

Answer 2

As I already mentioned in the comments, your query is likely not what you want:

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

This will requiere o.StartDate to be lower-equal to model.StartDate and o.StartDate to be bigger-equal to model.StartDate. This effectively means that o.StartDate is required to be equal to model.StartDate. The same applies to EndDate too. So the effective query that you are running is this:

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

That’s very likely not what you want. Instead, if you want to check for items between your dates, you should do it like this:

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

So now you look for items where o.StartDate is on or after model.StartDate and where o.EndDate is on or before model.EndDate. This makes model.StartDate and model.EndDate a range in which the availability needs to fit.