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


A TimeSpan Code First attribute has to be mapped to SQL server. It seems to be being created by Code First as a Time(7) in SQL. TimeSpan in, however. I need to keep data for events longer than 24 hours since the Internet can support longer time frames. How should this be handled using Code First?

12/14/2011 11:10:23 AM

Accepted Answer

As per my the earlier 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 just generated a second column and saved it in the database, converting the TimeSpan to ticks. I subsequently decided not to save the TimeSpan.

public Int64 ValidityPeriodTicks { get; set; }

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

This is much simpler to perform with EF Core as you can use Value Translations. In version 2.1, you may transparently map timespans to database ticks by using value conversions and the TimeSpanToTicksConverter. Therefore, EF Core is definitely worth looking at (provided other features satisfy requirements) - you can use it in Framework 4.7 projects, so there's no need to move. Core Net.

4/22/2018 12:46:32 PM

Popular Answer

As far as I'm aware, SQL Server does not have a data type that is comparable to TimeSpan from.NET. Although you pointed out that Time only accepts values up to 24 hours, it is the closest match.

This is explained in the following MSDN paper. Since there isn't a solution there, I'm presuming that it isn't now feasible.

Related Questions


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