How do I map TimeSpan with greater than 24 hours to SQL server Code First?

Question

I am trying to map a TimeSpan Code First property to SQL server. Code First seems to be creating it as a Time(7) in SQL. However TimeSpan in .Net can handle longer periods than 24 hours and I need to store longer than 24 hour for event length. What is the best way to handle this with Code First.

Answer 1

As per my previous question on how to store TimeSpan in SQL I was advised to store it as seconds or ticks etc. In the end I didn't map the TimeSpan column as there is no equivalent in SQL server. I simply created a 2nd field which converted the TimeSpan to ticks and stored that in the DB. I then prevented storing the TimeSpan

public Int64 ValidityPeriodTicks { get; set; }
 
[NotMapped]
public TimeSpan ValidityPeriod
{
    get { return TimeSpan.FromTicks(ValidityPeriodTicks); }
    set { ValidityPeriodTicks = value.Ticks; }
}

If you wish to do this in EF Core it is a lot cleaner as you can use Value Conversions. In 2.1 you can use value conversions and TimeSpanToTicksConverter to map timespans to ticks in the database transparently. So certainly worth considering EF Core (assuming other features meet needs) - can use it in Framework 4.7 projects so don't need to switch to .Net Core.

Answer 2

As far as I know there is no equivalent data type in SQL Server for .NET's TimeSpan. The closest match is Time, but, as you pointed out, it only supports values up to 24 hours? http://msdn.microsoft.com/en-us/library/ms186724.aspx#DateandTimeDataTypes.

The following MSDN document describes this http://msdn.microsoft.com/en-us/library/bb386909.aspx. I'm assuming that since there is no solution listed there, it's not currently possible.

Answer 3

First of all, MVC has nothing to do with this issue. It is entirely related to EF Code First and SQL Server so it's a DAL matter.

One solution could be to provide a custom column type in your entity configuration, like this:

modelBuilder
.Entity<MyClass>()
.Property(c => c.MyTimeSpan)
.HasColumnType("whatever sql type you want to use");