如何将`time（0）`映射到`DateTime`与实体框架6 [英] How to map `time(0)` to `DateTime` with Entity Framework 6
问题描述
我正在使用的数据库有一天的时间存储在一个
根据MSDN ， time 数据类型定义了一个特定的一天的时间，而不是任意的时间长度。这是由时间类型，00：00：00.0000000至23：59：59.9999999的范围证明的。
一切都很好。但是，实体框架拒绝将其映射到 DateTime 属性。
将ADO.NET默认的映射 TimeSpan 远非理想（ Jon Skeet指出）因为SQL Server的时间不是任意的时间间隔，而是具有AM / PM上下文。 .NET的 TimeSpan 格式化程序没有AM / PM的概念。 time 和 TimeSpan 用于单独的问题域。从纯粹的理想主义的角度来说，我宁愿将 time 映射到 DateTime 。更糟糕的是，由于这些单独的问题域时间更糟糕的是，由于固定的起点是一天的时间，这也是有道理的。 c $ c>和 TimeSpan ，像DevExpress的TimeEdit这样的控件只会绑定到 DateTime 属性。我希望避免使用重复的属性来弄脏所有的实体，只需在 TimeSpan 和 DateTime 之间转换。
（作者在这里提出同样的问题，但要求较少：
实体框架代码中的SQL'时间'类型首先）
p>您的问题看起来非常像用于在此页面上更改DateTimes惯例的示例：自定义代码第一公约（EF6以上）
可能有这样的一个例子：
public class TimeSpanConvention：Convention
{
public TimeSpanConvention（）
{
this.Properties< TimeSpan>（）
.Configure c => c.HasColumnType（datetime））;
}
}
protected override void OnModelCreating（DbModelBuilder modelBuilder）
{
modelBuilder.Conventions.Add（new TimeSpanConvention（））;
}
编辑只需重新阅读您的问题，我看到你想要在数据库中的C＃代码和时间（0）中的DateTime，而不是相反，所以这将不起作用。不幸的是，我怀疑你将需要2个这样的解决方案的领域在映射时转换价值 - 这将使数据库属性保持不变。 / p>
关于自定义编码约定的原始想法，但是基于命名约定：
code> protected override void OnModelCreating（DbModelBuilder modelBuilder）
{
modelBuilder.Properties< DateTime>（）
.Where（p => p.Name.EndsWith（TimeOfDay ））
.Configure（p => p.HasColumnType（time（0）））;
}
编辑2
自那以后是一个无效的映射，让我们没有选择：
在模型中：
private string DBTimeOfDay {get;组;
[System.ComponentModel.DataAnnotations.Schema.NotMapped]
public DateTime TimeOfDay
{
get {return new DateTime（DBTimeOfDay.Ticks）; }
set {DBTimeOfDay = value.TimeOfDay; }
}
在Context类中：
protected override void OnModelCreating（DbModelBuilder modelBuilder）
{modelBuilder.Types（）。配置（c =>
{
var属性= c.ClrType.GetProperties（BindingFlags.NonPublic
| BindingFlags.Instance）
.Where（p => p.Name ==DBTimeOfDay）;
foreach（var p in属性）
c.Property（p）.HasColumnName（TimeOfDay）;
}）;
}
另一个选项是创建一个数据库视图，转换时间（0）到 DateTime 在其sql中，并映射到修改的存储过程进行更新。该技术已经被建议用于不支持的数据类型
编辑3！
睡觉了...这里是我的想法。
time（0）表示一天中的时间。
Entity Framework选择将其转换为 Timespan 。
Timespan 应该表示持续时间。
Timespans 很容易添加到 DateTime 结果是一个新的 DateTime
C＃没有一个 TimeOfDay 数据类型
所以在您的业务逻辑中，您必须持有时间作为 TimeSpan 从不超过24小时，或作为 DateTime ，您将日期部分设置为随后忽略的任意值。
EF与C＃一致，其中 DateTime.TimeOfDay 返回Timespan
如果您在业务逻辑，如经常性的约会等等，那么我认为对于TimeSpan来说，实际上可能有一个稍微更强的论据。如果这很复杂，那么也许你应该开始使用 Noda时间
当UI移动到UI时，问题出现。 TimeEdit 需要一个 DateTime ...我们要显示为am / pm ..
所以这就是我将它映射到一个DateTime，即在UI中。我正在使用MVC。我正在使用ViewModels。我已经从实体映射我使用AutoMapper。这很容易，它保持实体清洁。 TimeSpan 域中的属性转换为 DateTime 为UI ..
Is there any way a custom mapping rule could be injected using Entity Framework 6's new dependency pattern?
The database I am working with has times of day stored in a few time(0) columns.
According to MSDN, the time data type defines a particular “time of a day”, as opposed to an arbitrary length of time. This is evidenced by the range of the time type, 00:00:00.0000000 through 23:59:59.9999999.
All is well here. However, Entity Framework refuses to map it to a DateTime property.
The mapping to the ADO.NET default of TimeSpan is far from ideal (as Jon Skeet pointed out) because SQL Server's time is not an arbitrary span of time but has AM/PM context. .NET's TimeSpan formatter has no concept of AM/PM. time and TimeSpan are for separate problem domains. From a purely idealistic point of view, I would rather always map time to DateTime. That also makes sense because of the fixed starting point as a time of day.
Worse, because of these separate problem domains of time and TimeSpan, controls like DevExpress's TimeEdit will only bind to DateTime properties. I am hoping to avoid dirtying all my entities with duplicated properties that simply translate between TimeSpan and DateTime.
(The author here asked the same question but had fewer requirements: SQL 'time' type in Entity Framework Code First)
Your problem looks very like the example used to change the convention for DateTimes on this page: Custom Code First Conventions (EF6 onwards)
Something like this perhaps:
public class TimeSpanConvention : Convention
{
public TimeSpanConvention()
{
this.Properties<TimeSpan>()
.Configure(c => c.HasColumnType("datetime"));
}
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Add(new TimeSpanConvention());
}
EDIT Just re-read your question, and I see that you want DateTime in the C# code and time(0) in the database not the other way around so that won't work. Unfortunately, I suspect you will need 2 fields like this solution Convert value when mapping - which does keep the database property private.
What about the original idea of a custom coding convention but based on a naming convention:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Properties<DateTime>()
.Where(p => p.Name.EndsWith("TimeOfDay"))
.Configure(p => p.HasColumnType("time(0)"));
}
EDIT 2 And since that is an invalid mapping that leaves us with little choice:
In the Model:
private string DBTimeOfDay { get; set; }
[System.ComponentModel.DataAnnotations.Schema.NotMapped]
public DateTime TimeOfDay
{
get { return new DateTime(DBTimeOfDay.Ticks); }
set { DBTimeOfDay= value.TimeOfDay; }
}
And in the Context class:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{ modelBuilder.Types().Configure(c =>
{
var properties = c.ClrType.GetProperties(BindingFlags.NonPublic
| BindingFlags.Instance)
.Where(p => p.Name == "DBTimeOfDay");
foreach (var p in properties)
c.Property(p).HasColumnName("TimeOfDay");
});
}
Another option is to create a database view that converts time(0) to DateTime in its sql, and map to modified stored procedures for updates. That technique has been suggested for unsupported data types
EDIT 3 ! Having slept on this...here are my thoughts.
time(0) represents a time of day.
Entity Framework has chosen to convert it to a Timespan.
Timespans are supposed to represent durations.
Timespans are easily added to DateTimes with the result being a new DateTime
C# does not have a TimeOfDay data type
So in your business logic either you have to hold time of day' as a TimeSpan that never exceeds 24 hours or as a DateTime where you have set the date part to an arbitrary value that you then ignore.
EF is consistent with C# where DateTime.TimeOfDay returns a Timespan
If you are doing any arithmetic in the business logic, say for recurring appointments or such, then I think there may actually be a marginally stronger argument for TimeSpan. If it's complicated then maybe you should start using Noda time
The problem comes when it moves to the UI. TimeEdit needs a DateTime...We want to display it as a.m./p.m...
So that is where I would map it to a DateTime i.e. in the UI. I'm using MVC. I'm using ViewModels. I'm already mapping from Entities. I'm using AutoMapper. It's easy and it keeps the Entities clean. TimeSpan properties in the Domain converted to DateTime for UI..
这篇关于如何将`time（0）`映射到`DateTime`与实体框架6的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
