How to Map DateOnly and TimeOnly Types to SQL

In this post, we are going to learn how to map DateOnly and TimeOnly types to SQL with migration.

Let’s dive in.

DateOnly and TimeOnly Types in .NET

Prior to .NET 6, we primarily had DateTime and DateTimeOffset struct types for handling date and time. As their names imply, such data types present converged date and time values. But in many scenarios, we want to deal with dates only, without caring about time at all, and vice versa.

For example, when we talk about someone’s birthday, we certainly don’t bother about the time of the day. The same goes for the working hours of an employee – it’s pointless to present such data with a date part since a specific date would be irrelevant.

One possible workaround for such cases might be truncating the time part or date part from DateTime or DateTimeOffset values. In that case, we also need special handling to ensure that the value of the remaining part remains unaffected. Even then, we might end up with erroneous results because DateTime/DateTimeOffset also carries timezone information and is not designed to handle absolute Date and Time.

In the end, such workarounds are somewhat like using double data type to hold integer values. Not just that, these types connote the use of both date and time components together, and using them with only partial values can be misleading.

.NET 6 addresses this concern by introducing DateOnly and TimeOnly structs. This enables us to design our data models just the way we mean to:

public class Employee
{
    public int Id { get; set; }

    public string Name { get; set; } = null!;

    public DateOnly BirthDate { get; set; }

    public TimeOnly WorkStartTime { get; set; }

    public TimeOnly WorkEndTime { get; set; }
}

In this Employee model, the data types of BirthDate, WorkStartTime, and  WorkEndTime are clearly consistent with their purpose.

The SQLite data provider ( Microsoft.Data.Sqlite 6.0) also supports these new types out of the box. As a result, we can seamlessly use such entity models in EF Core with SQLite adapters.

Map DateOnly and TimeOnly Types With SQL Server

Unfortunately, EF Core 7 (and earlier versions) with the SQL Server provider doesn’t play well with these new types.

To see the problem first-hand, let’s prepare a database context including our Employee entity set:

public class AppDbContext : DbContext
{
    public DbSet<Employee> Employees => Set<Employee>();

    protected override void OnConfiguring(DbContextOptionsBuilder builder)
    {
        if (builder.IsConfigured)
           return;
        var configuration = new ConfigurationBuilder()
                            .AddJsonFile("appsettings.json")
                            .Build();
        builder.UseSqlServer(configuration.GetConnectionString("Default"));
    }
}

This is a typical EF Core code-first approach to setting up a DbContext. Inside the OnConfiguring() method we configure the SQL Server database provider with the desired ConnectionString. 

Now we try to scaffold the initial migration file using Package Manager Console, like so:

PM> add-migration Initial_Create

But we encounter an exception:

System.InvalidOperationException: The property 'Employee.BirthDate' could not be mapped because it is of type 'DateOnly', which is not a supported primitive type or a valid entity type. Either explicitly map this property, or ignore it using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'

Clearly, EF Core 7 does not support the mapping of DateOnly and TimeOnly types to SQL Server columns out of the box.

The good news is, both of these data types are supported in EF Core 8.

But is there any way to achieve a working solution with EF Core 7? Yes, there is!

EF Core 7 Support With Value Converter

Our solution ties to one of the great features of EF Core – support for plugging in a custom value converter that handles conversion from/to stored values. This means that if we can convert value objects of unsupported data types to those of supported data types, we can resolve our problem. In our case, DateTime and TimeSpan are the supported default counterparts of DateOnly and TimeOnly types respectively.

Let’s first implement the converter for DateOnly:

public class DateOnlyConverter : ValueConverter<DateOnly, DateTime>
{
    public DateOnlyConverter() : base(
        dateOnly => dateOnly.ToDateTime(TimeOnly.MinValue), 
        dateTime => DateOnly.FromDateTime(dateTime))
    { }
}

We simply extend the generic ValueConverter<> and supply the necessary conversion functions as constructor arguments. The DateOnly struct ships with some From*/To* routines that come in handy for such back-and-forth conversions.

Next, we need the converter for TimeOnly:

public class TimeOnlyConverter : ValueConverter<TimeOnly, TimeSpan>
{
    public TimeOnlyConverter() : base(
        timeOnly => timeOnly.ToTimeSpan(), 
        timeSpan => TimeOnly.FromTimeSpan(timeSpan))
    { }
}

Similar to DateOnly, TimeOnly also provides necessary routines to implement the conversion from/to TimeSpan.

Register Custom Converters

It’s time to register our custom converters with the context model builder:

public class AppDbContext : DbContext
{
    // omitted for brevity
    protected override void ConfigureConventions(ModelConfigurationBuilder builder)
    {
        base.ConfigureConventions(builder);

        builder.Properties<DateOnly>()
            .HaveConversion<DateOnlyConverter>();

        builder.Properties<TimeOnly>()
            .HaveConversion<TimeOnlyConverter>();
    }
}

By overriding the ConfigureConventions() method, we get access to the configuration builder. Subsequently, we register DateOnlyConverter for all DateOnly properties by using the HaveConversion() configurator method. Similarly, we register TimeOnlyConverter for all TimeOnly properties.

Now we are able to scaffold the migration file:

public partial class InitialCreate : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Employees",
            columns: table => new
            {
                Id = table.Column<int>(type: "int", nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                Name = table.Column<string>(type: "nvarchar(max)", nullable: false),
                BirthDate = table.Column<DateTime>(type: "datetime2", nullable: false),
                WorkStartTime = table.Column<TimeSpan>(type: "time", nullable: false),
                WorkEndTime = table.Column<TimeSpan>(type: "time", nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Employees", x => x.Id);
            });
    }
}

Nice, we’ve successfully mapped DateOnly/TimeOnly properties to our database columns. But we are not done yet.

Map DateOnly and TimeOnly to Proper SQL Types

From the scaffolded migration file, we see TimeOnly properties are rightly mapped to their proper SQL equivalent of “time“. But DateOnly properties are mapped as “datetime2” which is an equivalent of DateTime.  This is not what we want. Instead, we want to map it as a “date“:

protected override void ConfigureConventions(ModelConfigurationBuilder builder)
{
    base.ConfigureConventions(builder);

    builder.Properties<DateOnly>()
        .HaveConversion<DateOnlyConverter>()
        .HaveColumnType("date");

    builder.Properties<TimeOnly>()
        .HaveConversion<TimeOnlyConverter>();
}

Once again, we configure this inside the ConfigureConventions() method. A chaining call to HaveColumnType("date") from the previous configurator of DateOnly does the job!

As we re-scaffold the migration file:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateTable(
        name: "Employees",
        columns: table => new
        {
            Id = table.Column<int>(type: "int", nullable: false)
                .Annotation("SqlServer:Identity", "1, 1"),
            Name = table.Column<string>(type: "nvarchar(max)", nullable: false),
            BirthDate = table.Column<DateTime>(type: "date", nullable: false),
            WorkStartTime = table.Column<TimeSpan>(type: "time", nullable: false),
            WorkEndTime = table.Column<TimeSpan>(type: "time", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Employees", x => x.Id);
        });
}

And this time we find BirthDate correctly mapped as a “date” column.

Register Custom Comparer

Our CLR to SQL data mapping is in good shape. But we still have an issue with the underlying change-tracking mechanism of EF Core. In our custom converter, we are transforming a struct (DateOnly) with a few properties to a struct (DateTime) with a higher number of properties. This means redundancy in generic comparison between the unused properties, which actually imposes extra overhead on the change-tracking workflow of EF.

We can avoid this overhead by providing complimentary value comparers:

public class DateOnlyComparer : ValueComparer<DateOnly>
{
    public DateOnlyComparer() : base(
        (x, y) => x.DayNumber == y.DayNumber,
        dateOnly => dateOnly.GetHashCode())
    { }
}

We first come up with a value comparer for DateOnly. Such a comparer naturally expects a comparison predicate. In our case, it’s simple enough to compare the DayNumber property of two DateOnly values. We further enhance it by supplying a hash provider function that simply forwards the hash code from the DateOnly instance.

Similarly, we can implement a comparer for TimeOnly by using the Ticks property:

public class TimeOnlyComparer : ValueComparer<TimeOnly>
{
    public TimeOnlyComparer() : base(
        (x, y) => x.Ticks == y.Ticks,
        timeOnly => timeOnly.GetHashCode())
    { }
}

Let’s modify our AppDbContext class to associate these comparers with our custom converters:

protected override void ConfigureConventions(ModelConfigurationBuilder builder)
{
    base.ConfigureConventions(builder);

    builder.Properties<DateOnly>()
        .HaveConversion<DateOnlyConverter, DateOnlyComparer>()
        .HaveColumnType("date");

    builder.Properties<TimeOnly>()
        .HaveConversion<TimeOnlyConverter, TimeOnlyComparer>();
}

We just need to specify the comparer alongside the converter registration.

Testing DbContext With DateOnly and TimeOnly Values

Our AppDbContext is now correctly configured to handle DateOnly and TimeOnly values.

We insert a sample employee entry:

var employee = new Employee
{
    Name = "John Doe",
    BirthDate = new DateOnly(2023, 5, 9),
    WorkStartTime = new TimeOnly(8, 30),
    WorkEndTime = new TimeOnly(4, 30)
};

_context.Employees.Add(employee);
_context.SaveChanges();

And then explore the stored record in the database:

We can see that the BirthDate column has no redundant time part and WorkStartTime/WorkEndTime columns have no redundant date part.

Performance Impact of DateOnly and TimeOnly Conversions

When we use a custom value converter, EF Core needs to invoke the converter to convert the property value to the appropriate database representation when writing to the database, and vice versa when reading from the database. This additional conversion step may introduce some overhead and potentially impact performance. However, such converters typically do not involve computationally expensive operations and thus have negligible impact in general. 

Our DateOnlyConverter is based on two built-in functions – DateOnly.FromDateTime() and ToDateTime(). The DateOnly.FromDateTime() method is optimized to create a DateOnly value from a DateTime by extracting only the date portion and ignoring the time part. On the other hand, ToDateTime() assigns a specific time (e.g., midnight) to the DateOnly value to create a DateTime representation.

Such operations are fairly simple and straightforward. The same applies to TimeOnlyConverter. That means, unless we deal with an exceptionally large number of conversions or perform them in a high-throughput scenario, the impact should be minimal and is unlikely to be a performance bottleneck.

However, in general, it’s a good practice to utilize performance profiling when we use custom value converters.

Conclusion

In this article, we have learned how to map DateOnly and TimeOnly types to SQL Server database using EF Core 7. Though EF Core 7 does not support such mapping out of the box, we are able to do it with a few customizations. Thankfully, support for these data types within the SQL Server provider has arrived as of EF Core 8.