Entity Framework MariaDb Date as String problem

Question

I seem to have a problem with EF core and my MariaDb database. First of all, I'm unable to do the obvious move to change the database a bit, so that option is off the table.

I have a table "Contacts" where a contact date is being kept. This contact date is being kept in the form of a string value, containing only the date, as such: 2020-08-30 for 30th august of 2020. I have an EF core mapping for this field as such:

entity.Property(e => e.ContactDate)
                    .IsRequired()
                    .HasColumnName("contactDate")
                    .HasColumnType("varchar(255)")

where e.ContactDate is an DateTime property.

When I use the property in my code, the datetime works as expected and contains the date saved in the databank. hooray!

The problem however arises when I want to query on that datetime. Given this query:

SELECT `c7`.`contactDate`
    FROM `contacts` AS `c7`
    WHERE (`f`.`uuid` = `c7`.`fileUuid`) AND (`c7`.`numberOfContacts` > 0)
    ORDER BY `c7`.`contactDate`
    LIMIT 1) <= @__endDate_1) AND (`f`.`closingDate` IS NULL OR ((`f`.`closingDate` >= @__startDate_2)

The properties startDate and endDate are inserted into the query as parameters. For example, @__endDate_1='2019-12-31 00:00:00'. And here lies the problem: MySql is going to string compare the contactDate db field with the string value of that endDate. Because one has a time field and the other one does not, I run into problems if I want to do a greather than function. (SELECT "2020-02-04" >= "2020-02-04 00:00:00" returns 0).

Is there a way that i can:

• Or change EF that he casts the contactDate to a value with a timestamp in every query
• Or change EF that he casts the DateTime value to a Date Value without any time values?

What do you guys feel about a possible third solution where I introduce a new "Date" class as a DateTime to Date string wrapper for EF?

Thanks!

Answer 1

With

.HasColumnType("varchar(255)")

you are activating the EF Core built-in DateTime to string value converter. It's not documented, but it converts the DateTime values (when binding command parameters) to string using the following format (or similar)

"yyyy\\-MM\\-dd HH\\:mm\\:ss.FFFFFFF"

while what you need is a format like this

"yyyy\\-MM\\-dd"

It can be achieved by configuring a custom value converter:

entity.Property(e => e.ContactDate)
    .HasConversion(
        value => value.ToString("yyyy\\-MM\\-dd"),
        dbValue => DateTime.Parse(dbValue, CultureInfo.InvariantCulture)
    )
    // the rest is the same
    .IsRequired()
    .HasColumnName("contactDate")
    .HasColumnType("varchar(255)"); // or .IsUnicode(false).HasMaxLength(255)

And that's it. Now the parameter in question will be @__endDate_1='2019-12-31', hence this achieves the bullet #2.

Bullet #1 is not possible.

What about creating special Date type, it's possible, but not easy, since it requires a lot of infrastructure plumbing code. There are some packages which do that - NodaTime, NetTopology etc. You can look at their implementation if you are interested, but IMHO supporting (easy) custom value types is not EF Core priority (currently), so until they get better support, I would stay with value converters.

Answer 2

The answer of @IvanStoev does work as requested and as expected.

Here is a fully working sample console program, that proves it works:

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

namespace IssueConsoleTemplate
{
    public class Contact
    {
        public int ContactId { get; set; }
        public DateTime ContactDate { get; set; }
    }

    public class Context : DbContext
    {
        public virtual DbSet<Contact> Contacts { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseMySql("server=127.0.0.1;port=3306;user=root;password=;database=So63655418",
                    b => b
                        .ServerVersion(new 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<Contact>(
                entity =>
                {
                    entity.Property(e => e.ContactDate)
                        .HasColumnType("varchar(255)")
                        .HasConversion(
                            v => v.ToString(@"yyyy\-MM\-dd"),
                            v => DateTime.Parse(v, CultureInfo.InvariantCulture));

                    entity.HasData(
                        new Contact
                        {
                            ContactId = 1,
                            ContactDate = new DateTime(2020, 9, 1, 14, 42, 59, 123), // <-- time will be dropped
                        });
                });
        }
    }

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

            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();
            
            var dateParameter = new DateTime(2020, 9, 1, 21, 11, 1, 456); // <-- time will be dropped
            var contactWithExactDate = context.Contacts.SingleOrDefault(c => c.ContactDate == dateParameter);
            
            Debug.Assert(contactWithExactDate != null);
            Debug.Assert(contactWithExactDate.ContactDate == new DateTime(2020, 9, 1));
        }
    }
}

It generates the following SQL statements:

warn: Microsoft.EntityFrameworkCore.Model.Validation[10400]
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data, this mode should only be enabled during development.

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.8 initialized 'Context' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: ServerVersion 8.0.20 MySql SensitiveDataLoggingEnabled DetailedErrorsEnabled

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

      DROP DATABASE `So63655418`;

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

      CREATE DATABASE `So63655418`;

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

      CREATE TABLE `Contacts` (
          `ContactId` int NOT NULL AUTO_INCREMENT,
          `ContactDate` varchar(255) NOT NULL,
          CONSTRAINT `PK_Contacts` PRIMARY KEY (`ContactId`)
      );

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

      INSERT INTO `Contacts` (`ContactId`, `ContactDate`)
      VALUES (1, '2020-09-01');

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (8ms) [Parameters=[@__dateParameter_0='2020-09-01' (Nullable = false) (Size = 255)], CommandType='Text', CommandTimeout='30']

      SELECT `c`.`ContactId`, `c`.`ContactDate`
      FROM `Contacts` AS `c`
      WHERE `c`.`ContactDate` = @__dateParameter_0
      LIMIT 2

The program executes without assertion and the logged SQL statements show the used parameter correctly as @__dateParameter_0='2020-09-01', even though we explicitly used a time value in the C# code.

Answer 3

When I was in the long wait for answers, I went ahead and tried the Date type approach, with it specific conversions. As a miracle, suddenly EF converted all the stored string date's into datetime objects in the query. It was marvelous and it did the job!

This is the code for my date object:

public struct Date: IComparable<Date>, IComparable<DateTime>, IComparable, IEquatable<Date>
    {
        
        /// <summary>
        /// Turns a DateTime into a Date
        /// </summary>
        /// <param name="dateTime"></param>
        public Date(DateTime dateTime)
        {
            _date = dateTime.Date;
        }

        private readonly DateTime _date;

        public static implicit operator Date(DateTime value) => new Date(value);
        public static implicit operator DateTime(Date value) => value._date;

        public int CompareTo(Date other) => _date.CompareTo(other._date);
        public int CompareTo(DateTime other) => _date.CompareTo(new Date(other));
        public int CompareTo(object obj) => _date.CompareTo(obj);
        public bool Equals(Date other) => _date.Equals(other._date);
        public override bool Equals(object obj) => obj is Date other && Equals(other);
        public override int GetHashCode() => _date.GetHashCode();
        
    }

And the conversion for EF was like this:

public static PropertyBuilder<Date?> HasDateConversion(this PropertyBuilder<Date?> propertyBuilder) =>
            propertyBuilder
                .HasConversion(date => 
                        date.HasValue
                        ? ParseDate(date.Value)
                        : null,
                    str =>
                        string.IsNullOrWhiteSpace(str) 
                            ? null
                            : (Date?)ParseString(str));

This resulted in all the date's selected to be selected as such:

SELECT * FROM `contacts` AS `c`
WHERE CAST(`c`.`contactDate` AS datetime(6)) < '2020-01-03 05:01:03.000000'
LIMIT 1

Thus my issue was fixed :) hooray!

Answer 4

I would have left the format in the database as you wrote it. But I would have changed the query. In this case a cast of the date format like it was show in this thread:

MySQL compare DATE string with string from DATETIME field

SELECT * FROM contacts WHERE DATE(startTime) > '2020-08-30'

... or in the official documentation of MariaDB: DATETIME Syntax