制作独特的索引或约束以检查重叠的日期时间列

Question

我正在使用 EF 6.2.0， EF Code-First， .NET 4.7， Azure SQL Database。

我有一个ef代码 - 第一模型，我需要在该模型上执行约束，因此新行是不是允许在其他ValidFromUtc和ValidToUtc DateTime列中重叠范围。我希望在数据库中执行约束，并通过EF进行维护。

在我的EF模型上，我具有以下属性和DataAnnotations：

[Required, Key]
public Guid Id { get; set; }
[Required, Index("UniqueIndexName", IsUnique = true, Order = 1)]
public Guid OwnerAccountId { get; set; }
[Required, Index("UniqueIndexName", IsUnique = true, Order = 2)]
public Guid PartnerAccountId { get; set; }
// I need a range constraint between ValidFromUtc and ValidToUtc to be enforced somehow.
[Required, Index("UniqueIndexName", IsUnique = true, Order = 3)]
public DateTime ValidFromUtc { get; set; }
[Required, Index("UniqueIndexName", IsUnique = true, Order = 4)]
public DateTime ValidToUtc { get; set; }
// Etc. etc.

我需要的一种方法是强制执行给定的OwnerAccountId的行，如果其对ValidFromUtc或ValidToUtc的值与任何现有行重叠，则无法插入或更新PartnerAccountId。

。

是否可以使用(按优先顺序(实现上述约束：

• dataNatnotations？
• 流利的API？
• 计算的列？
• 插入/更新触发器？
• 存储过程？

如果是这样，该怎么办？哪种方法更好？为什么？

我现在能想到的唯一解决方案是创建插入/更新触发器。

任何指针都非常感谢！谢谢！

Answer 1

不幸的是，我认为触发是唯一的选择：

CREATE TABLE [dbo].[UniqueDates](
    [OwnerAccountId] [int] NOT NULL,
    [ParentAccountId] [int] NOT NULL,
    [ValidFromUtc] [datetimeoffset](7) NOT NULL,
    [ValidToUtc] [datetimeoffset](7) NOT NULL,
 CONSTRAINT [PK_UniqueDates] PRIMARY KEY CLUSTERED 
(
    [OwnerAccountId] ASC,
    [ParentAccountId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[UniqueDates] ([OwnerAccountId], [ParentAccountId], [ValidFromUtc], [ValidToUtc]) VALUES (1, 1, CAST(N'2018-01-01T00:00:00.0000000+00:00' AS DateTimeOffset), CAST(N'2018-01-03T00:00:00.0000000+00:00' AS DateTimeOffset))
GO
INSERT [dbo].[UniqueDates] ([OwnerAccountId], [ParentAccountId], [ValidFromUtc], [ValidToUtc]) VALUES (1, 2, CAST(N'2018-01-04T00:00:00.0000000+00:00' AS DateTimeOffset), CAST(N'2018-01-05T00:00:00.0000000+00:00' AS DateTimeOffset))
GO
INSERT [dbo].[UniqueDates] ([OwnerAccountId], [ParentAccountId], [ValidFromUtc], [ValidToUtc]) VALUES (2, 1, CAST(N'2018-01-06T00:00:00.0000000+00:00' AS DateTimeOffset), CAST(N'2018-01-08T00:00:00.0000000+00:00' AS DateTimeOffset))
GO
create trigger [trg_i_Test] on [UniqueDates]
INSTEAD OF INSERT
AS
BEGIN
   if exists (SELECT null
  FROM [TEST].[dbo].[UniqueDates] [from]
  Inner join [TEST].[dbo].[UniqueDates] [to] on ([from].ValidFromUtc between [to].ValidFromUtc and [to].ValidtoUtc) 
      and ([from].OwnerAccountId<>[to].OwnerAccountID or [from].ParentAccountId<>[to].ParentAccountID)
      )
   BEGIN
      RAISERROR ('These dates break the rules.' ,16,1)
      ROLLBACK TRANSACTION
   END 
   ELSE    
      BEGIN
         INSERT INTO [UniqueDates]
         SELECT *
         FROM inserted
   END    
END
GO
INSERT [dbo].[UniqueDates] ([OwnerAccountId], [ParentAccountId], [ValidFromUtc], [ValidToUtc]) VALUES (2, 4, CAST(N'2018-01-07T00:00:00.0000000+00:00' AS DateTimeOffset), CAST(N'2018-01-09T00:00:00.0000000+00:00' AS DateTimeOffset))
GO

Answer 2

如果您想要所有内容来自EF，则必须使用EF迁移。(注意：我根本不喜欢迁移(。这允许对DB(创建和迁移等(进行完全控制，这也意味着您需要在C＃代码(必需字段，字段类型等(中执行很多数据库特定的内容。然后，在种子例程中，您会编写一些代码，例如：

context.Database.ExecuteSQLCommand("CREATE CONSTRAINT /* etc etc */");

(如果这是两个原始的一点，您可以创建自己的属性以放在EF模型上，然后使用反射来动态创建自定义约束(。