Entity Framework MariaDb Date as String problem

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.

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.

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!

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