DateTimeOffset to SQL Server DateTime Mapping in Entity Framework

ef-code-first entity-framework mapping


Is it possible to map aDateTimeOffset an attribute to a SQL Serverdatetime column, assuming that you cannot alter either side, i.e., that the property and column must continue to use the same date types?

I'm aware that making them match is the simplest solution, but I'm wondering whether there's a method to go around this. When I looked at custom mappings, it seemed that I would have to map every column by hand, not just the ones I wanted.DateTimeOffset property.

I attempted to

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

However, that made theMember Mapping specified is not valid error.

I hoped to have the ability to place theUtcDateTime DateTimeOffset property value stored in the database and having theDateTimeOffset to be UTC (i.e. have an Offset of zero).


2/22/2012 8:27:16 PM

Accepted Answer

No. DateTimeOffset .NET class will correspond toDateTimeOffset type SQL. Because EF doesn't provide straightforward type conversions or mappings, you cannot directly alter this behavior. If you'd want to keep it asDateTime You have to break it.

In the first place, create the Customer class using the following approach to expose private properties to the mapping used by in these words:

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

You now own two properties, one of which is private and holdsDataTime which you wish to expose to the world in a database.DateTimeOffset for your submission. Set it out in your own terms:

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

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
        modelBuilder.Entity<Customer>().Ignore(c => c.LastModifiedOn);

However, why don't you utilizeDateTime immediately and keep 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