C# 使用实体框架 Codefirst 存储 TimeSpan - SqlDbType.Time 溢出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17129795/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Storing TimeSpan with Entity Framework Codefirst - SqlDbType.Time overflow
提问by SB2055
I'm trying to seed some constants into my DB:
我试图在我的数据库中植入一些常量：
context.Stages.AddOrUpdate(s => s.Name,
new Stage()
{
Name = "Seven",
Span = new TimeSpan(2, 0, 0),
StageId = 7
});
context.Stages.AddOrUpdate(s => s.Name,
new Stage()
{
Name = "Eight",
Span = new TimeSpan(1, 0, 0, 0),
StageId = 8
});
This is within my Seed() function for EF Codefirst Migrations. It fails at Stage Eight with the following:
这在我用于 EF Codefirst 迁移的 Seed() 函数中。它在第八阶段失败，原因如下：
System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.OverflowException: SqlDbType.Time overflow. Value '1.00:00:00' is out of range. Must be between 00:00:00.0000000 and 23:59:59.9999999.
System.Data.UpdateException：更新条目时出错。有关详细信息，请参阅内部异常。---> System.OverflowException: SqlDbType.Time 溢出。值“1.00:00:00”超出范围。必须介于 00:00:00.0000000 和 23:59:59.9999999 之间。
Why would I not be able to store a timespan using EF? I really hope I don't need to do some silly time-to-ticks conversion on both ends here...
为什么我不能使用 EF 存储时间跨度？我真的希望我不需要在这里两端做一些愚蠢的时间到滴答转换......
采纳答案by haim770
In this line:
在这一行：
Span = new TimeSpan(1, 0, 0, 0)
You're using this constructor:
您正在使用此构造函数：
public TimeSpan(int days, int hours, int minutes, int seconds);
So you're actually creating a TimeSpangreater than 24 hours since you're passing 1to the daysparameter, while your underlying Database type is Timewhich only accepts values between 00:00-23:59.
所以，你实际上是创建一个TimeSpan大于24小时，因为你传递1的days参数，而基础数据库类型是Time只接受00之间的值：59：00-23。
Hard to tell whether you actually meant to have a TimeSpanwith 1 day, or it's just a typo.
很难说你是否真的打算有TimeSpan1 天，或者这只是一个错字。
If you really want a TimeSpangreater than 24 hours, i guess you'll have to map your field to another Database type (like SmallDateTime).
如果您真的想要TimeSpan超过 24 小时，我想您必须将您的字段映射到另一种数据库类型（如SmallDateTime）。
If it's just a typo error, just change your line to:
如果这只是一个错字错误，只需将您的行更改为：
Span = new TimeSpan(1, 0, 0),
回答by haim770
The problem, as previously mentioned, is the fact that EF maps the TimeSpan class to Time, which is limited to 24 hours.
如前所述，问题在于 EF 将 TimeSpan 类映射到 Time，时间限制为 24 小时。
If you need to store a timespan of greater than 24 hours, I would suggest one of the following two approaches:
如果您需要存储超过 24 小时的时间跨度，我建议使用以下两种方法之一：
1)Create a TimeSpan entity with int properties for the different elements of a timespan, something like:
1）为时间跨度的不同元素创建一个具有 int 属性的 TimeSpan 实体，例如：
public class Timespan
{
public Int64 Id { get; set; }
public Int16 Years { get; set; }
public int Months { get; set; }
public Int64 Days { get; set; }
public Int64 Hours { get; set; }
public Int64 Minutes { get; set; }
}
Simply add a foreign reference in the applicable entity to your custom Timespan entity.
只需将适用实体中的外部引用添加到您的自定义 Timespan 实体即可。
2)Do some silly time-to-ticks conversion, as explained in this blog post.
2)做一些愚蠢的时间-滴答转换，如这篇博文中所述。
回答by Shimmy Weitzhandler
[Browsable(false)]
[EditorBrowsable(EditorBrowsableState.Never)]
[Obsolete("Property '" + nameof(Duration) + "' should be used instead.")]
public long DurationTicks { get; set; }
[NotMapped]
public TimeSpan Duration
{
#pragma warning disable 618
get { return new TimeSpan(DurationTicks); }
set { DurationTicks = value.Ticks; }
#pragma warning restore 618
}
Update
更新
This is now achievable since EF Core 2.1, using Value Conversion.
从 EF Core 2.1 开始，现在可以使用Value Conversion实现这一点。
builder.Entity<Stage>()
.Property(s => s.Span)
.HasConversion(new TimeSpanToTicksConverter()); // or TimeSpanToStringConverter
回答by ShadyBradyM
Doing a time-to-ticks conversion on both ends is no longer silly. Not sure when they added it, but Entity Framework will now select the appropriate built in converter if one exists (in this case TimeSpanToTicksConverter). All you need to do is add a single attribute to your entity class and Entity Framework will automagically give the column in the SQL table the same range as the TimeSpan class.
在两端进行时间到滴答转换不再是愚蠢的。不确定他们何时添加它，但实体框架现在将选择适当的内置转换器（如果存在）（在本例中为 TimeSpanToTicksConverter）。您需要做的就是向实体类添加一个属性，实体框架将自动为 SQL 表中的列提供与 TimeSpan 类相同的范围。
public class Stage
{
public string Name { get; set; }
[Column(TypeName = "bigint")]
public TimeSpan Span { get; set; }
public int StageId { get; set; }
}
I'm sure bigint isn't the default column type for TimeSpan for human readability and backwards compatibility, but this seems like a pretty much perfect solution.
我确信 bigint 不是 TimeSpan 的默认列类型，以实现人类可读性和向后兼容性，但这似乎是一个非常完美的解决方案。
I hope this helps anybody experiencing this issue six years later.
我希望这可以帮助任何人在六年后遇到这个问题。
Documentation: https://docs.microsoft.com/en-us/ef/core/modeling/value-conversions
文档：https: //docs.microsoft.com/en-us/ef/core/modeling/value-conversions
