将TimeSpan与Entity Framework一起使用时，“Datetime2到Datetime会导致超出范围的值”

I have a table with several time(7) (allowing NULLS) columns in my SQL Server database. My application uses Entity Framework to store entities in the database.

我在SQL Server数据库中有一个包含多个时间（7）（允许NULLS）列的表。我的应用程序使用Entity Framework将实体存储在数据库中。

I use Nullable<TimeSpan> as data type corresponding to the 'allowing NULLS time(7)' in my SQL Server database. When I execute SaveChanges() after adding an entity with the TimeSpan? column, it throws an ListInternalException saying the well known

我使用Nullable 作为对应于我的SQL Server数据库中的'允许NULLS时间（7）'的数据类型。在使用TimeSpan添加实体后执行SaveChanges（）时？列，它抛出一个众所周知的ListInternalException

'The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.'

“将datetime2数据类型转换为日期时间数据类型会导致超出范围的值。”

I have already met this exception before and know that it is because the .Net datetime mi value is lower than SqlDateTime.MinValue. But here we don't have date to count from. Moreover my property and column types are Nullable and the exception persists when I set the properties to be Null.

我之前已经遇到过此异常，并且知道这是因为.Net datetime mi值低于SqlDateTime.MinValue。但在这里，我们没有可以计算的日期。此外，我的属性和列类型是Nullable，当我将属性设置为Null时，异常仍然存在。

Any suggestions how to deal with that problem?

有什么建议如何处理这个问题？

CREATE TABLE [dbo].[MyEntity]
(
    [Id] [uniqueidentifier] NOT NULL,
    [AssociationId] [uniqueidentifier] NOT NULL,
    [IsMondayDayOff] [bit] NOT NULL,
    [IsTuesdayDayOff] [bit] NOT NULL,
    [IsWednessdayDayOff] [bit] NOT NULL,
    [IsThursdayDayOff] [bit] NOT NULL,
    [IsFridayDayOff] [bit] NOT NULL,
    [IsSaturdayDayOff] [bit] NOT NULL,
    [IsSundayDayOff] [bit] NOT NULL,
    [MondayFrom] [time](7) NULL,
    [TuesdayFrom] [time](7) NULL,
    [WednesdayFrom] [time](7) NULL,
    [ThursdayFrom] [time](7) NULL,
    [FridayFrom] [time](7) NULL,
    [SaturdayFrom] [time](7) NULL,
    [SundayFrom] [time](7) NULL,
    [MondayTo] [time](7) NULL,
    [TuesdayTo] [time](7) NULL,
    [WednessdayTo] [time](7) NULL,
    [ThursdayTo] [time](7) NULL,
    [FridayTo] [time](7) NULL,
    [SaturdayTo] [time](7) NULL,
    [SundayTo] [time](7) NULL,

    CONSTRAINT [PK_MyEntity] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PK_MyEntity] WITH CHECK 
    ADD CONSTRAINT [FK_PK_MyEntity_PK_MyAssociation] 
    FOREIGN KEY([FBOId]) REFERENCES [dbo].[MyAssociation] ([Id])
GO

ALTER TABLE [dbo].[MyEntity] CHECK CONSTRAINT [FK_MyEntity_MyAssociation]
GO

My entity:

我的实体：

public class MyEntity 
{
    public Guid Id { get; set; }
    public Guid OtherTableId { get; set; }

    public bool IsMondayDayOff { get; set; }
    public TimeSpan? MondayFrom { get; set; }
    public TimeSpan? MondayTo { get; set; }

    public bool IsTuesdayDayOff { get; set; }
    public TimeSpan? TuesdayFrom { get; set; }
    public TimeSpan? TuesdayTo { get; set; }

    public bool WednesdayOff { get; set; }
    public TimeSpan? WednesdayFrom { get; set; }
    public TimeSpan? WednesdayTo { get; set; }

    public bool ThursdayOff { get; set; }
    public TimeSpan? ThursdayFrom { get; set; }
    public TimeSpan? ThursdayTo { get; set; }

    public bool FridayOff { get; set; }
    public TimeSpan? FridayFrom { get; set; }
    public TimeSpan? FridayTo { get; set; }

    public bool SaturdayOff { get; set; }
    public TimeSpan? SaturdayFrom { get; set; }
    public TimeSpan? SaturdayTo { get; set; }

    public bool SundayOff { get; set; }
    public TimeSpan? SundayFrom { get; set; }
    public TimeSpan? SundayTo { get; set; }

    public virtual AnotherEntity Association  { get; set; }
}

1 个解决方案

#1

The actual problem was not how EF handles time(7) and TimeSpan data types but MyEntity's second level association (association of an association) that had datetime2 column which was not provided when adding the entity to the database. Fond it using the SQL Server Profiler as suggested by @sepupic in the comments. Thanks.

实际问题不是EF如何处理时间（7）和TimeSpan数据类型，而是具有datetime2列的MyEntity的第二级关联（关联关联），在将实体添加到数据库时未提供该列。喜欢在评论中使用@sepupic建议的SQL Server Profiler。谢谢。