39

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...

Improve this question

5 Answers 5

Reset to default
54 
    [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
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Great answer, might be worth adding a [NotMapped] attribute to Duration
This might be helpful for those puzzled by pragma warning suppression: stackoverflow.com/questions/968293/…
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.
Hi commenters, can't understand how it got omitted from my answer but yes it should naturally be there.
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());
|
16

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

Improve this answer

Comments

15

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

Improve this answer

Comments

9

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),
Improve this answer

8 Comments

Since when does is a logical timespan limited to 24 hours? Especially silly as the TimeSpan class supports multiple days.
@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).
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.
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?
EF Core scaffolding automatically converts TimeSpans to Time. IMO that's a framework bug.
|
0

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.

Improve this answer

2 Comments

Option 2 is much less silly than option 1 which is just a waste of disk space. 304 Bit instead of 64 Bit? Insane... :)
There is no "conversion" between TimeSpan and long, TimeSpan is already a long, it is internally stored this way, see for yourself.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.