Entity Framework Mapping DateTimeOffset to SQL Server DateTime

Question

Is there a way to map a DateTimeOffset property to a SQL Server datetime column, with the assumption that you can't change either side, meaning that the property and column have to stay those date types?

I know the easiest is to make them match but want to know if there's a way to work around this. I was looking into custom mappings but it seemed like I had to map all of the columns myself and not just the DateTimeOffset property.

I tried:

modelBuilder.Entity<Customer>().Property(c => c.LastModifiedOn).HasColumnType("datetime");

But that threw the Member Mapping specified is not valid error.

I was hoping to be able to put the UtcDateTime DateTimeOffset property value in the DB and when reading have the DateTimeOffset be in UTC (i.e. have an Offset of zero).

Community · Accepted Answer · 2017-05-23 10:29:56Z

19

No. DateTimeOffset in .NET class will map to DateTimeOffset SQL type. You cannot change this behavior directly because EF doesn't provide simple type conversions / mappings. If you want to store it as DateTime you must hack it.

First define Customer class with trick to expose private property to mapping referenced by @cincura.net in this post:

public class Customer
{
    public static class CustomerExpressions
    {
        public static readonly Expression<Func<Customer, DateTime>> LastModifiedOn = c => c.LastModifiedOnInternal;
    }

    // Other properties

    public DateTimeOffset LastModifiedOn
    {
        get { return new DateTimeOffset(LastModifiedOnInternal); }
        set { LastModifiedOnInternal = value.DateTime; }
    }

    private DateTime LastModifiedOnInternal { get; set; }
}

Now you have two properties - one is private and holds DataTime which you want to persist to database and one is public exposing DateTimeOffset for your application. Define it in your context:

public class Context : DbContext
{
    public DbSet<Customer> Customers { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>().Ignore(c => c.LastModifiedOn);
        modelBuilder.Entity<Customer>().Property(Customer.CustomerExpressions.LastModifiedOn).HasColumnName("LastModifiedOn");
    }
}

Anyway why you don't use DateTime directly and store it in UTC?

edited May 23, 2017 at 10:29

CommunityBot

11 silver badge

answered Feb 22, 2012 at 20:24

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Carlos Over a year ago

Thanks for the response. We have to work with multiple time zones and doing the arithmetic with DateTimeOffset is easier than having to convert everything to UTC prior to do any arithmetic with the times.

Søren Boisen Over a year ago

One reason for avoiding DateTime with EF is that values don't roundtrip: all values are loaded with DateTimeKind.Unspecified. With no way to tell EF that it should default to UTC. Sure there are hacks to fix it up, but they are ugly and some may hurt performance (using ObjectContext events).

Suamere Over a year ago

@SørenBoisen - In my migration during table creation, I just add defaultValueSql: "GETUTCDATE()"

Suamere Over a year ago

Full Example: CreatedDate = c.DateTime(nullable: false, precision: 7, storeType: "datetime2", defaultValueSql: "GETUTCDATE()"),

Collectives™ on Stack Overflow

Entity Framework Mapping DateTimeOffset to SQL Server DateTime

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related