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.

Falsetto

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.

Falsetto

This approach may lead to suboptimal queries when somebody don't know or forgets that ValidityPeriod is NotMapped property. If its used in a LINQ where query EF won't complain, it will just pull the collection and iterate over it. For this reason one may use old fashion Get/Set accessors to make it clear that they shouldn't be used in queries. – Krissy

Agreed, I have adjusted my code in my solution so the implementation is clearer. – Falsetto

Use extension methods to get or set the value, and there should be less misunderstandings. – Garrow

According to StanislawSwierc's comment, how about: instead of a NotMapped property, create a "GetValidityPeriod(): TimeSpan" method and a "SetValidityPeriod(period: TimeSpan): void" method. This way, it would clearly be unmapped and obvious it shouldn't be used in Linq queries. – Madelainemadeleine

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.

Heterolecithal

Yeah I never found away apart from my answer. EF just seems to make the odd decision to map it to the SQL Server Time data type which obviously is a mismatch – Falsetto

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");

Beltz

Ok. It's the whatever sql type you want to use I care about. What is the standard way to map .Net TimeSpan to a SQL server field. – Falsetto

I thought it was clear enough. The string parameter of the HasColumnType method must contain your SQL type as you would declare it in a sql script or table designer, like nvarchar(50) or bit or anything that you need and is compatible to the .Net data type you're using. For what is the exact type to use in your specific case, just experiment. – Beltz

I knew how to change the column type, that isn't the issue. It seems like a reasonably common problem mapping .Net TimeSpan to SQL. I wanted to know specifically about what the best way to handle storing .Net TimeSpan to SQL server. – Falsetto

Then your question isn't related to Code First at all, it's a more general issue. It's more like "What is the correct SQL type to associate to .Net Timespan". I gave that kind of answer because you asked about how to do it in Code First, and I gave you a Code First sample. – Beltz