Updated on

On EF Core 8 and later, mapping DateOnly and TimeOnly to SQL Server takes no extra code: a DateOnly property becomes a date column and a TimeOnly property becomes a time column.

We only need a value converter on older EF Core versions, or when the column already exists with a different type. Let’s see both cases, and what happens to our migrations when we remove the converters an EF Core 7 project needed.

To download the source code for this article, you can visit our GitHub repository.

Let’s dive in.

DateOnly and TimeOnly in Our Entity Model

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 the end, such workarounds are somewhat like using double data type to hold integer values.

.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. If you want the types themselves first, we cover using and converting DateOnly and TimeOnly in a separate article.

The SQLite data provider (Microsoft.Data.Sqlite) has supported these types out of the box since version 6.0. Here, we’re going to focus on SQL Server.

What Column Types Does EF Core Use for DateOnly and TimeOnly?

Since EF Core 8, the SQL Server provider maps both types on its own. A DateOnly property becomes a date column and a TimeOnly property becomes a time column, with no value converter and no extra package.

So the setup is the entity itself. We write it with the narrower types, add a migration, and the migration already holds the right column types.

Queries work the same way. EF Core translates comparisons and members such as Year, AddYears() and Hour into SQL Server date functions, so a filter on a DateOnly column runs in the database.

EF Core 7 and earlier refused to map these properties for SQL Server, which is why many tutorials, this one included until now, register a value converter for each type.

On EF Core 8 and later, that converter only earns its place when the column itself is something other than date or time.

Microsoft’s “What’s New in EF Core 8” says it plainly about its own sample model: “Notice that the DateOnly properties map to date columns, and the TimeOnly properties map to time columns.”

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

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;

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. There’s no date configuration in here at all. To follow along, we need the Microsoft.EntityFrameworkCore.SqlServer, Microsoft.Extensions.Configuration.Json and Microsoft.EntityFrameworkCore.Tools packages, plus an appsettings.json with a Default connection string, copied to the output folder.

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

PM> add-migration Initial_Create

If you prefer the .NET CLI, dotnet ef migrations add Initial_Create does the same. Our article on migrations covers the rest of that workflow.

The command runs without an exception, and the migration file already has the right column types:

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<DateOnly>(type: "date", nullable: false),
            WorkStartTime = table.Column<TimeOnly>(type: "time", nullable: false),
            WorkEndTime = table.Column<TimeOnly>(type: "time", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Employees", x => x.Id);
        });
}

BirthDate is a date column with no redundant time part, and both working hours are time columns with no redundant date part. The CLR types in the migration are DateOnly and TimeOnly too, not DateTime and TimeSpan.

Can We Filter on DateOnly and TimeOnly Columns?

Yes, and the filter runs in SQL Server. With context as an instance of our AppDbContext, let’s find the employees born in 1990 who start work before nine:

var earlyStarters = context.Employees
    .Where(e => e.BirthDate.Year == 1990 && e.WorkStartTime < new TimeOnly(9, 0));

When we look at the generated SQL with ToQueryString(), we get:

SELECT [e].[Id], [e].[BirthDate], [e].[Name], [e].[WorkEndTime], [e].[WorkStartTime]
FROM [Employees] AS [e]
WHERE DATEPART(year, [e].[BirthDate]) = 1990 AND [e].[WorkStartTime] < '09:00:00'

The Year property becomes DATEPART(), and the TimeOnly value becomes a plain time literal. AddYears(), Hour and IsBetween() translate as well, so we can filter on these columns the same way we’d filter on a DateTime.

Why Did EF Core 7 Fail to Map These Types?

Because the SQL Server provider didn’t know them yet. On EF Core 7 (and earlier versions), the same add-migration command stops with 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'.

If you see this message, you’re on EF Core 7 or older. Upgrading to a supported EF Core version is the real fix. Until then, there are two ways around it: the ErikEJ.EntityFrameworkCore.SqlServer.DateOnlyTimeOnly community package for EF Core 6 and 7, or a value converter per type, which is what this article originally showed.

If our project still carries those converters from EF Core 7, what happens when we upgrade and take them out?

What Happens to Our Migrations When We Remove the Value Converters?

Usually nothing, as long as the old configuration pinned DateOnly to a date column.

Here’s the EF Core 7 version of our ConfigureConventions() method, the one we’re removing. It registered a converter (and a comparer) per type and pinned DateOnly to a date column:

// The EF Core 7 workaround. Don't add this to an EF Core 8+ project.
protected override void ConfigureConventions(ModelConfigurationBuilder builder)
{
    base.ConfigureConventions(builder);

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

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

After the upgrade, we delete the whole method, along with the converter and comparer classes, and scaffold a migration:

PM> add-migration RemoveDateOnlyTimeOnlyConverters

The migration comes out empty:

public partial class RemoveDateOnlyTimeOnlyConverters : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {

    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {

    }
}

The columns were already date and time, so the schema doesn’t change. Only the model snapshot does: b.Property<DateTime>("BirthDate") becomes b.Property<DateOnly>("BirthDate"), and both TimeSpan properties become TimeOnly.

Now, what if the old configuration never called HaveColumnType("date")? Then EF Core 7 created BirthDate as a datetime2 column, and the same command warns us:

An operation was scaffolded that may result in the loss of data. Please review the migration for accuracy.

This time, the migration alters the column:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AlterColumn<DateOnly>(
        name: "BirthDate",
        table: "Employees",
        type: "date",
        nullable: false,
        oldClrType: typeof(DateTime),
        oldType: "datetime2");
}

In SQL, that’s ALTER TABLE [Employees] ALTER COLUMN [BirthDate] date NOT NULL;.

The warning is about the time part. Our converter always wrote midnight, so nothing real gets lost. But if any other code or script wrote full timestamps into that column, the ALTER drops their times. That’s worth a SELECT before we run it.

And if we can’t alter that column at all? That’s the case where the converter stays.

When Do We Still Need a Value Converter for DateOnly?

When the column isn’t date. EF Core maps DateOnly to a date column, and it won’t map it to an existing datetime2 or datetime column on its own. So a legacy column, maybe one that other applications also use, still needs a converter on EF Core 10.

If we only tell EF Core about the column type, model building fails:

System.InvalidOperationException: The 'DateOnly' property 'Employee.BirthDate' could not be mapped to the database type 'datetime2' because the database provider does not support mapping 'DateOnly' properties to 'datetime2' columns. Consider mapping to a different database type or converting the property value to a type supported by the database using a value converter. See https://aka.ms/efcore-docs-value-converters for more information. Alternately, exclude the property from the model using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.

The message already names the fix. A value converter handles the conversion from and to stored values, and DateTime is the supported counterpart of DateOnly for a datetime2 column.

So, let’s implement the converter for DateOnly:

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

We 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.

Then, we register it for every DateOnly property and keep the column type the database already has:

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

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

By overriding the ConfigureConventions() method, we get access to the configuration builder. HaveConversion() registers our converter for all DateOnly properties, and HaveColumnType("datetime2") matches the existing column. Our model stays on DateOnly, and the database keeps its datetime2 column.

TimeOnly rarely needs this, since an old TimeSpan property already lived in a time column, which EF Core now maps to TimeOnly directly. If you want the rest of the SQL Server to C# mapping, including where datetime2 differs from datetime and the decimal(18,2) trap, the SQL Server to C# type table lays it out in one place.

If we generate our model from the database instead, EF Core 8 and later scaffold date columns as DateOnly and time columns as TimeOnly on their own, which our guide to database-first scaffolding with EF Core Power Tools walks through.

Do We Need a Custom Value Comparer Too?

No. Earlier versions of this article registered a ValueComparer next to each converter, on the idea that change tracking would otherwise compare all the unused parts of the DateTime. Change tracking never compares the DateTime at all.

EF Core snapshots and compares the property’s own value, the DateOnly, and the converter only runs at the database boundary. When we ask the model for the comparer on our converted BirthDate, EF Core 10 returns its default ValueComparer<DateOnly>. Microsoft’s “Value Comparers” documentation says the same about structs like this: “The mapping for simple structs is also simple and requires no special comparers or snapshotting.”

Testing DbContext With DateOnly and TimeOnly Values

Our AppDbContext handles DateOnly and TimeOnly values with no extra configuration.

We apply the migration with update-database and 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(16, 30)
};

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

And then explore the stored record in the database:

The Employees table holds one row for John Doe with BirthDate 2023-05-09, WorkStartTime 08:30 and WorkEndTime 16:30, each column with no extra date or time part.

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

Conclusion

In this article, we’ve learned how EF Core maps DateOnly and TimeOnly to SQL Server: on EF Core 8 and later, a DateOnly lands in a date column and a TimeOnly in a time column, and queries on both run in the database.

We’ve also seen what happens when we upgrade an EF Core 7 project. Removing the converters produces an empty migration when the columns were already date and time, and an ALTER COLUMN with a data-loss warning when BirthDate was a datetime2. The one place a DateOnly converter still belongs is a column we can’t change.

Tested with .NET 10.0.10 and EF Core 10.0.13.