Native DateOnly and TimeOnly support in Entity Framework 8 in SQL Server

.NET 6 brought us two new datatypes: DateOnly and TimeOnly. For those types we don't have any first class support in Entity Framework - until now.

There is a recent change, that hit us with Entity Framework 8 that might ease the situation and brings native support for those types.

DateOnly and TimeOnly

I guess those types are pretty self-explanatory. Until .NET 6, if you wanted to represent someone's birthday you had to include the time information as well, as this is just how DateTime works. But maybe the date part is enough for our use case. So DateOnly would be sufficient. And the good thing is, that DateOnly has no time zone information, which makes handling those kinds of information easier.

TimeOnly is the same but we instead of the date portion we take the time portion (independent of the date). All in all DateOnly and TimeOnly are a nice way of decoupling this information from DateTime.

The problem with that was that those new types were not natively understandable/translateable by Entity Framework, especially with the SQL Server.

Mapping the types

To make this work, you had to define a custom (even though) simple converter or use a NuGet package, that did the same:

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

public class DateOnlyComparer : ValueComparer<DateOnly>
{
    public DateOnlyComparer() : base(
        (d1, d2) => d1.DayNumber == d2.DayNumber,
        d => d.GetHashCode())
    {
    }
}

That then can be used in your DbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<MyEntity>(builder =>
    {
        builder.Property(x => x.Date)
            .HasConversion<DateOnlyConverter, DateOnlyComparer>();
    });
}

The same applies to TimeOnly respectively. And this seems odd given the fact that there are data types in SQL Serve that can represent that information perfectly.

Entity Framework 8

There was a recent Pull Request merged that enables you to use DateOnly and TimeOnly without any additional mapping logic. A small disclaimer here: The issue is flagged as "consider-for-next-release" which can indicate that this feature will not make it into Entity Framework 8, but I do think that it is very likely to land in the next release.

Just for the sake of completeness: Sqlite did already receive that functionality with .NET 6.

Resources

Entity Framework and ordered indexes

In Entity Framework 7, the team has added support for ordered indexes to the fluent API. In this blog post we will look at how to use this feature and what it means for your database.

Entity Framework - Storing complex objects as JSON

From time to time, it is nice to store complex objects or lists as JSON in the database. With Entity Framework 8, this is now easily possible. But this was possible all along with Entity Framework 7.

Entity Framework 8: Raw SQL queries on unmapped types

The next iteration of Entity Framework, namely Entity Framework 8, will have a new and exciting feature:

Support raw SQL queries without defining an entity type for the result

That means less boilerplate code!

An error has occurred. This application may no longer respond until reloaded. Reload x