Using DateTime for a SQL Time Column in Entity Framework Core 3.1

Question

Is there a way to make Entity Framework Core let you use a DateTime for a SQL Time column? SQL Server allows you to set a Time column value using a DateTime, but when you try to configure Entity Framework Core to do this, it throws an exception when compiling the model saying that the Database Provider does not support the datatype.

This code throws the exception

public void Configure(EntityTypeBuilder<Category> builder)
{
    ...
    builder.Property<System.DateTime>(x => x.StartTime).HasColumnName(@"StartTime").HasColumnType(@"time").IsRequired() 
    // HasColumnType matches the type defined in SQL
}

This code works fine, but I'd rather keep HasColumnType accurate so that it doesn't modify the database to use DateTime instead of Time...

public void Configure(EntityTypeBuilder<Category> builder)
{
    ...
    builder.Property<System.DateTime>(x => x.StartTime).HasColumnName(@"StartTime").HasColumnType(@"datetime").IsRequired()
    // HasColumnType does not match the type defined in SQL
}

Is there a reason EF Core blocks the ability to do this when SQL allows it?

Use Timespan instead. A time column contains only the time-of-day, it's not a full datetimeso it shouldn't be mapped to DateTime — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 30, 2020 at 16:46
I prefer to avoid TimeSpan, it's just a terrible DataType for dealing with the Time of Day, you need a custom binder to map datetime values coming from the client... And SQL Server supports setting a Time field with a DateTime, so why would Entity Framework arbitrarily block that functionality?. — Failwyn
– Failwyn, Commented Jun 30, 2020 at 17:39
Because SQL Server doesn't support that at all. The legacy datetime type has a lot of quirks - you could add two datetime values and get a date in 4040 for example. That doesn't mean it's a good idea. That's why SQL Server doesn't allow implicit conversions between time and the newer types like datetime2 — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 30, 2020 at 17:51
As for terrible types, it's DateTime that's terrible. Time-of-Day doesn't have a date component at all, so what is a DateTime supposed to mean? How could you compare times when all you have are DateTime values? While Timespan may not be restricted to just 24 hours, it's a lot better than DateTime though. There's no need for a custom binder either - time maps to Timespan, not DateTime — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 30, 2020 at 17:52

Failwyn · Accepted Answer · 2020-07-01 15:55:00Z

3

I ended up resolving this with a Conversion

builder.Property<DateTime>("StartTime").HasColumnName(@"StartTime").HasColumnType(@"time").IsRequired().HasConversion(v => v.TimeOfDay, v => DateTime.Now.Date.Add(v));

answered Jul 1, 2020 at 15:55

Failwyn

81710 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using DateTime for a SQL Time Column in Entity Framework Core 3.1

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related