12

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

CJBS
  • 15,147
  • 6
  • 86
  • 135
Carlos
  • 223
  • 1
  • 2
  • 5

1 Answers1

18

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?

Community
  • 1
  • 1
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • 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. – Carlos Feb 23 '12 at 05:03
  • 10
    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). – Søren Boisen May 08 '15 at 19:33
  • @SørenBoisen - In my migration during table creation, I just add `defaultValueSql: "GETUTCDATE()"` – Suamere Mar 14 '17 at 19:42
  • Full Example: `CreatedDate = c.DateTime(nullable: false, precision: 7, storeType: "datetime2", defaultValueSql: "GETUTCDATE()"),` – Suamere Mar 14 '17 at 19:43