实体框架映射DateTimeOffset到SQL Server DateTime [英] Entity Framework Mapping DateTimeOffset to SQL Server DateTime

问题描述

有没有办法将 DateTimeOffset 属性映射到SQL Server datetime 列，假设您不能改变任何一方，意味着属性和列必须保留这些日期类型？

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?

我知道最简单的是让他们匹配，但想知道是否有办法解决这个问题。我正在寻找自定义的映射，但似乎我不得不自己映射所有的列，而不仅仅是 DateTimeOffset 属性。

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.

我试过：

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

但是，抛出指定的成员映射无效错误

我希望能够在数据库中放置 UtcDateTime DateTimeOffset 属性值阅读有 DateTimeOffset 在UTC（即偏移为零）。

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

谢谢。

推荐答案

否。 .NET类中的 DateTimeOffset 将映射到 DateTimeOffset SQL类型。您不能直接更改此行为，因为EF不提供简单的类型转换/映射。如果要将其存储为 DateTime ，则必须将其删除。

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.

首先定义客户类，并将其隐藏@ cincura.net引用的映射属性，位于这篇文章：

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

现在你有两个属性 - 一个是私有的，并保存 DataTime 您要保留到数据库，一个是公开的，为您的应用程序公开显示 DateTimeOffset 。在您的上下文中定义它：

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

无论如何，为什么不使用 DateTime 直接存储在UTC？

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

这篇关于实体框架映射DateTimeOffset到SQL Server DateTime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！