Use to_date/to_timestamp for translating string to date-format

[WhatzGames]

Recently I had some data where I had to work with dates. Sadly a column was not in any date type but simply string.

Now in my case, it was still in ISO1806- format and just casting to object, and then DateTime created a good enough query.

But when needed, giving a custom format is, as far as I know, not supported in translation.

I created a test case that seemed somewhat useful as well.

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

await using var ctx = new StudentContext(args[0]);
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

var results = await ctx.Student
         .Select(x => DateTime.ParseExact(x.DateOfBirth, "dd.MM.yyyy", null))
         .GroupBy(x => x.Year < 1960
              ? "seniors"
              : x.Year >= 2000
                  ? "youth"
                  : "adults")
         .Select(x => new
          {
              Group = x.Key,
              Amount = x.Count()
          }).ToArrayAsync();
    
Console.WriteLine(results);

public class StudentContext : DbContext
{
    private readonly string _connectionString;
    public DbSet<Student> Student { get; set; }

    public StudentContext(string connectionString)
    {
        _connectionString = connectionString;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
           .UseNpgsql(_connectionString)
          .LogTo(Console.WriteLine, LogLevel.Information)
          .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Student>()
                    .HasData(
                         new Student {Id = 1, DateOfBirth = "01.01.1989"},
                         new Student {Id = 2, DateOfBirth = "15.01.1993"},
                         new Student {Id = 3, DateOfBirth = "29.02.2005"},
                         new Student {Id = 4, DateOfBirth = "04.12.1956"});
    }
}

public class Student
{
    public long Id { get; set; }
    public string DateOfBirth { get; set; }
}

And I expected a result in the following manner:

SELECT CASE
    WHEN date_part('year', to_date("Student"."DateOfBirth", 'dd.MM.yyyy')) < 1960 THEN 'seniors'
    WHEN date_part('year', to_date("Student"."DateOfBirth", 'dd.MM.yyyy')) >= 2000 THEN 'youth'
    ELSE 'adults'
    END AS groupName, count(*) as amount FROM "Student"
GROUP BY groupName

But instead, it throws me an exception about not being able to translate the query.

Now, since I could not find a simple DateTime.Parse(string input, string format) method that one could work with. I'd suggest providing an extension:

Ef.Functions.ToDate(string input, string format);

Which then would get translated instead

[roji]

Yeah, nothing like this is currently supported; in general, translating method calls that parse strings are is frequently problematic, since the supported formats vary between .NET and PG (though as you say it's possible to add something on EF.Functions instead).

In this specific case, I think the appropriate .NET method here would be this overload of DateOnly.ParseExact. We'd have to make sure that the format string corresponds exactly between this and PG to_date, etc. Something similar may also be possible for full timestamps, but we'd need to investigate that properly.

Putting in the backlog, you're welcome to submit a PR for this if you want (but we'd need to make sure the format strings correspond first).

[WhatzGames]

Probably providing an extension on EF.Functions should be created first, this way no mapping of the format strings is needed. Providing implementations on the suitable parse-methods with their corresponding mappings can then be added later on, which would then base upon the existing implementation.