How can I convert a TimeSpan of more than 24 hours to SQL Server Code First?

code-first ef-code-first entity-framework sql-server

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.

1
40
12/14/2011 11:10:23 AM

Accepted Answer

As per my previous question on how to store TimeSpan in SQL I was advised to store it as seconds or tickets 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.

35
4/22/2018 12:46:32 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow