DateTimeOffset to SQL Server DateTime Mapping in Entity Framework

ef-code-first entity-framework mapping


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


2/22/2012 8:27:16 PM

Accepted Answer

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

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

5/23/2017 10:29:56 AM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow