CODEWITHSUNDEEP
c#sql-serverentity-frameworkdatetime
Russ
Russ

Reputation: 12530

Can I convert a SQL DataType of DateTime to a DateTimeOffSet in EF?

I have a large database where we do a lot of TimeZone conversions. I am slowly converting the database to use DateTimeOffSet datatypes, but that's something I can't do accross the board. Too much change.

I can however change the code, and since I know that all my dates are stored in UTC in the database, I would like to just use the DateTimeOffSet object in .NET.

How can I get EF to do the conversion on the fly for me?

I have tried this: 

modelBuilder.Properties<DateTimeOffset>()
                .Configure( c => c.HasColumnType( "datetime" ) );

But I'm getting the error

(37,12) : error 2019: Member Mapping specified is not valid. The type 'Edm.DateTimeOffset[Nullable=True,DefaultValue=,Precision=]' of member 'ModifyDate' in type '{ObjectType}' is not compatible with 'SqlServer.datetime[Nullable=True,DefaultValue=,Precision=3]' of member 'ModifyDate' in type 'CodeFirstDatabaseSchema.{ObjectType}'.

Upvotes: 3

Views: 2485

Answers (1)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241420

Here is one approach you might consider:

First, define this following attribute:

[AttributeUsage(AttributeTargets.Property)]
public class DateTimeKindAttribute : Attribute
{
    private readonly DateTimeKind _kind;

    public DateTimeKindAttribute(DateTimeKind kind)
    {
        _kind = kind;
    }

    public DateTimeKind Kind
    {
        get { return _kind; }
    }

    public static void Apply(object entity)
    {
        if (entity == null)
            return;

        var properties = entity.GetType().GetProperties()
            .Where(x => x.PropertyType == typeof(DateTime) || x.PropertyType == typeof(DateTime?));

        foreach (var property in properties)
        {
            var attr = property.GetCustomAttribute<DateTimeKindAttribute>();
            if (attr == null)
                continue;

            var dt = property.PropertyType == typeof(DateTime?)
                ? (DateTime?) property.GetValue(entity)
                : (DateTime) property.GetValue(entity);

            if (dt == null)
                continue;

            property.SetValue(entity, DateTime.SpecifyKind(dt.Value, attr.Kind));
        }
    }
}

Now hook that attribute up to your EF context:

public class MyContext : DbContext
{
    public DbSet<Foo> Foos { get; set; }

    public MyContext()
    {
        ((IObjectContextAdapter)this).ObjectContext.ObjectMaterialized +=
            (sender, e) => DateTimeKindAttribute.Apply(e.Entity);
    }
}

Now on any DateTime or DateTime? properties, you can apply this attribute:

public class Foo
{
    public int Id { get; set; }

    [DateTimeKind(DateTimeKind.Utc)]
    public DateTime Bar { get; set; }
}

With this in place, whenever Entity Framework loads an entity from the database, it will set the DateTimeKind that you specify, such as UTC.

Now, you said you want to start switching over to DateTimeOffset types. You can take advantage of the fact that DateTime has a one-way implicit conversion to DateTimeOffset that will take the .Kind into account. In other words, you can do this:

DateTimeOffset BarDTO = foo.Bar;

Even though foo.Bar is a DateTime, this will work. Because the kind is set to UTC, the offset will be set to zero in the DateTimeOffset.

Of course, you could always do this in your model with something like this:

[NotMapped]
public DateTimeOffset BarDTO
{
    get { return Bar; }
    set { Bar = value.UtcDateTime; }
}

I'm sure you can come up with variations on this to suit your needs. The main thing is that whatever property is mapped to the field, the type must match.

Upvotes: 6

Related Questions