EF: DateTimeOffset value changed to Utc on SaveChanges()

Question

Datetime string on the client side is generated like moment().format() => 2016-09-09T01:29:25+05:30

On HTTP Post to the server, while debugging I can still see the correct offset. However, after saveChanges() is called, in database the value is stored as utc with +00:00 as offset (For eg: 2016-09-08 00:30:00.0000000 +00:00 ).

How do I keep the offset value intact?

Code:

EF table Entity model:

public partial class SomeTable
    {
        public long Id { get; set; }
        public Nullable<System.DateTimeOffset> StartTime { get; set; }
        public Nullable<System.DateTimeOffset> EndTime { get; set; }
    }

Post Model:
    public class DateInfoModel
    {
        public long Id { get; set; }
        public DateTimeOffset StartTime { get; set; }
        public DateTimeOffset EndTime { get; set; }
    }

Action Method:

[HttpPost]
[Route("UpdateInfo")]
public IHttpActionResult UpdateInfo(DateInfoModel model) {
    Int64 user_id = User.Identity.GetUserId<Int64>();
    if (!ModelState.IsValid)
    {
        return BadRequest(ModelState);
    }
    SomeTable new_rec = new SomeTable();
    new_rec.StartTime = model.StartTime;
    new_rec.EndTime = model.EndTime;
    db.SomeTable.Add(new_rec);
    db.SaveChanges();
    return Ok();
}

Client Side:

updateDateInfo = (): ng.IPromise<any> => {
        var to_send;
        to_send.StartTime = moment().format();
        to_send.EndTime = moment().format();
        return this.$http.post(webApiBaseUrl + "api/UpdateDateInfo",
            to_send)
            .then((response) => {
                if (response.status == 200) {
                    return response;
                }
                else {
                    return this.$q.reject(response);
                }
            }, (response) => {
                return this.$q.reject(response);
            })
    }

NOTE: SQL column type is also set to datetimeoffset(7)

Maybe [this](http://stackoverflow.com/questions/4648540/entity-framework-datetime-and-utc) helps — Jeroen Heier, Sep 09 '16 at 06:29

score 0 · Answer 1 · answered Oct 10 '16 at 14:35

Entity Framework reacts strangely to Nullable<> type declarations. It will not generate a nullable field unless you specify its behaviour in the DbContext such as:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    modelBuilder.Entity<MyEntity>().Property(x => x.MyNullableInt).IsOptional();
}

On the other hand, if you simply declare your model like this:

public partial class SomeTable
{
    public long Id { get; set; }
    public DateTimeOffset? StartTime { get; set; }
    public System.DateTimeOffset? EndTime { get; set; }
}

Entity framework will now create that field as nullable in the corresponding database table.

I doubt that will solve your problem but I think it is worth noting. Now regarding your issue, try specifying the precision for that DateTimeOffset in your entity config like this:

public class SomeTableConfig : EntityTypeConfiguration<SomeTable>
{
    public SomeTableConfig()
    {
        this.Property(a => a.StartTime).HasColumnType("datetimeoffset").HasPrecision(0);
        this.Property(a => a.EndTime ).HasColumnType("datetimeoffset").HasPrecision(0);
    }
}

Be sure to run your migrations and check that the database is reflecting the correct structure. Check that the Data Type of that column in the database is DateTimeOffset() instead of DateTime().

Other than that, I don't see any reason why it wouldn't maintain your TimeZone. What version of SQL Server are you using? I think DateTimeOffset was only supported since 2008

EF: DateTimeOffset value changed to Utc on SaveChanges()

1 Answers1