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

Answer 1

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?