Storing TimeSpan with Entity Framework Codefirst - SqlDbType.Time overflow

Question

I'm trying to seed some constants into my DB:

context.Stages.AddOrUpdate(s => s.Name,
                                   new Stage()
                                   {
                                       Name = "Seven",
                                       Span = new TimeSpan(2, 0, 0),
                                       StageId = 7
                                   });
context.Stages.AddOrUpdate(s => s.Name,
                                   new Stage()
                                   {
                                       Name = "Eight",
                                       Span = new TimeSpan(1, 0, 0, 0),
                                       StageId = 8
                                   });

This is within my Seed() function for EF Codefirst Migrations. It fails at Stage Eight with the following:

System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.OverflowException: SqlDbType.Time overflow. Value '1.00:00:00' is out of range. Must be between 00:00:00.0000000 and 23:59:59.9999999.

Why would I not be able to store a timespan using EF? I really hope I don't need to do some silly time-to-ticks conversion on both ends here...

Photocell

Answer 1

In this line:

Span = new TimeSpan(1, 0, 0, 0)

You're using this constructor:

public TimeSpan(int days, int hours, int minutes, int seconds);

So you're actually creating a TimeSpan greater than 24 hours since you're passing 1 to the days parameter, while your underlying Database type is Time which only accepts values between 00:00-23:59.

Hard to tell whether you actually meant to have a TimeSpan with 1 day, or it's just a typo.

If you really want a TimeSpan greater than 24 hours, i guess you'll have to map your field to another Database type (like SmallDateTime).

If it's just a typo error, just change your line to:

Span = new TimeSpan(1, 0, 0),

Minica

Since when does is a logical timespan limited to 24 hours? Especially silly as the TimeSpan class supports multiple days. – Onerous

@Diederik, Nowhere in the answer it says that it's TimeSpan that is limited to 24 hours, it's the mapped database column (Time) that is limited. It's all about wrong mapping, not TimeSpan limitation (that is definitely supporting more than 1 day). – Minica

Exactly. The DB type mapping is just plain wrong, as the TimeSpan data doesn't ft into a db type Time. This sounds like a bug. – Onerous

Yeah, TimeSpans can hold years, and centuries -- definitely a bug in how EF maps them. -- Perhaps there is a way to set the size of the field with an attribute in EF? – Resistant

SmallDateTime is NOT a good solution. I suggest using BIGINT to store it as "Ticks" (long). – Stavanger

EF Core scaffolding automatically converts TimeSpans to Time. IMO that's a framework bug. – Uppish

@Minica Any suggestions, Since I upgraded from EF core 2.1 to 5. My application is now storing TimeSpan values with empty timevalues meaning it's 00:00:00. Any leads about this behavior? – Raab

@Onerous I agree, Time is just the wrong data type to store a TimeSpan in SQL Server. – Abet

Answer 2

    [Browsable(false)]
    [EditorBrowsable(EditorBrowsableState.Never)]
    [Obsolete("Property '" + nameof(Duration) + "' should be used instead.")]        
    public long DurationTicks { get; set; }

    [NotMapped]
    public TimeSpan Duration
    {
#pragma warning disable 618
      get { return new TimeSpan(DurationTicks); }
      set { DurationTicks = value.Ticks; }
#pragma warning restore 618
    }

Update

This is now achievable since EF Core 2.1, using Value Conversion.

builder.Entity<Stage>()
    .Property(s => s.Span)
    .HasConversion(new TimeSpanToTicksConverter()); // or TimeSpanToStringConverter

Grunion

Great answer, might be worth adding a [NotMapped] attribute to Duration – Pula

This might be helpful for those puzzled by pragma warning suppression: #968793 – Histoid

Brent, actually if you add the [NotMapped] attribute to the Duration column, the column will be dropped, which will erase all existing data in this column and DurationTicks column will contain 0. – Chop

Hi commenters, can't understand how it got omitted from my answer but yes it should naturally be there. – Grunion

@Artemious, we do actually NOT want it to be reflected to the database, we only want the ticks to be there. – Grunion

watch out HasConversion<TimeSpanToTicksConverter>(); is not the right way to register the converter! you need to pass an instance instead of the generics type like this: HasConversion(new TimeSpanToTicksConverter()); – Maiden

@cyptus: Yep. The generic one throws an error during migrations. The instance-based one doesn't. – Polarimeter

Answer 3

Doing a time-to-ticks conversion on both ends is no longer silly. Not sure when they added it, but Entity Framework will now select the appropriate built in converter if one exists (in this case TimeSpanToTicksConverter). All you need to do is add a single attribute to your entity class and Entity Framework will automagically give the column in the SQL table the same range as the TimeSpan class.

public class Stage
{
    public string Name { get; set; }

    [Column(TypeName = "bigint")]
    public TimeSpan Span { get; set; }

    public int StageId { get; set; }
}

I'm sure bigint isn't the default column type for TimeSpan for human readability and backwards compatibility, but this seems like a pretty much perfect solution.

I hope this helps anybody experiencing this issue six years later.

Documentation: https://learn.microsoft.com/en-us/ef/core/modeling/value-conversions

Eternity

Answer 4

Now EF Core has built-in ticks <=> TimeSpan conversion. All you have to do is:

builder.Property(p => p.SomeTimeSpanProperty)
    .HasConversion<long>();

No need to instantiate a new TimeSpanToTicksConverter or anything. Just specify the type long as the generic argument, and you're done.

For more info see EF Core Built-in Value Converters

Pesade

Answer 5

The problem, as previously mentioned, is the fact that EF maps the TimeSpan class to Time, which is limited to 24 hours.

If you need to store a timespan of greater than 24 hours, I would suggest one of the following two approaches:

1) Create a TimeSpan entity with int properties for the different elements of a timespan, something like:

 public class Timespan
{
    public Int64 Id { get; set; }

    public Int16 Years { get; set; }

    public int Months { get; set; }

    public Int64 Days { get; set; }

    public Int64 Hours { get; set; }

    public Int64 Minutes { get; set; }
}

Simply add a foreign reference in the applicable entity to your custom Timespan entity.

2) Do some silly time-to-ticks conversion, as explained in this blog post.

Banausic

Option 2 is much less silly than option 1 which is just a waste of disk space. 304 Bit instead of 64 Bit? Insane... :) – Schoolteacher

There is no "conversion" between TimeSpan and long, TimeSpan is already a long, it is internally stored this way, see for yourself. – Grunion