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.