在Entity Framework中添加唯一索引可以防止DataTables显示工作 [英] Adding unique index in Entity Framework prevents DataTables display working

问题描述

这是一个非常简单的结构 - 具有外键1到多个的父子节。

It's a very simple structure - parent child with foreign key 1 to many.

正常工作，但是我想通过添加复合唯一索引来强制执行唯一性：

Was working fine but I wanted to enforce uniqueness by adding a composite unique index as so:

CreateIndex("dbo.Fixtures", new[] { "MarketId", "BookName", "CoupName" }, unique: true, name: "IX_UniqueFixture");

此后，当我输入新的记录，它产生错误：

After this when I entered new records it produced the error:

然后，我删除了新添加的索引，并删除了添加索引后添加的记录，并且显示了添加前的记录索引。

I then deleted the newly added index and deleted the records added after the index was added and it worked displaying the records that predated the addition of the index.

但是，当我再次尝试使用新记录更新数据库时，它会恢复为相同的错误：

However, when I tried again to update the database with new records it reverted to the same error:

在错误消息下方显示的记录是在添加索引之前存在的记录

The records shown underneath the error message are the records that existed prior to the addition of the index mentioned earlier.

现在，在重新使用父查找表Id列后，它的工作原理

Now after reseeding the parent lookup table Id column as so - it works

USE [aspnet-Arb-20160906102730] 

GO

DBCC CHECKIDENT（'ExchangeTypes'，RESEED，7）
GO

GO
DBCC CHECKIDENT ('ExchangeTypes', RESEED, 7) GO

，它再次允许我添加新记录

and it once again allows me to add new records

所以简而言之，我设法从数据库的混乱中恢复添加独特的组合索引，以便允许显示记录。

So in short I managed to recover from the messing up of the datatables by the addition of the unique composite index so that it allows the displaying of records.

然而，令我困惑的是为什么会发生这种情况。而且我仍然希望能够强制执行唯一性。也许我会尝试将3个字段连接到一个字段中，看看我是否可以在单个字段而不是几个字段上使用索引强制实现唯一性，并查看是否不会干扰数据显示。

However, it leaves me puzzled as to why this is occurring. And I still want to be able to enforce uniqueness. Maybe I will try to concatenate 3 fields into one field and see if I can get away with enforcing uniqueness with an index on a single field rather than several fields and see if that doesn't interfere with datatables display.

编辑：尝试使用Index on single field unique，它仍然出现相同的错误。有趣的是，如何在父查询字段上应用唯一索引不会阻止数据表工作，但在子表上尝试相同。

Tried it with Index on single field unique and it still comes up with the same error. Funny how applying a unique index on the parent lookup field doesn't prevent datatables from working but attempting the same on the child table does.

推荐答案

我从来不知道这可能是如此愚蠢的不可预测的，就像这样想，它只会自动加注自动增量并且从来没有想到它会对Datatables行显示具有这样的禁止效果，但是如果看起来像这样，它可以在初始测试中工作，除非它以某种方式断开，因为我只是通过删除和随后的添加记录来测试它。手指越过它是一个合适的工作，除非有人可以得到它的底部，并提供一个更好的解决方案。

I never knew this could be so stupidly unpredictable like this thinking it would just take care of itself with the auto increment and never imagined it would have such a prohibitive effect on Datatables rows display but any how it looks like this could work on initial testing unless it somehow breaks as I've only tested it once with the deletion and subsequent addition of records. Fingers crossed it is a suitable work around anyway unless someone can get to the bottom of it and offer a better solution.

看起来我将要运行这件的代码，每次执行删除时 - 将每个删除后的最大标识值重置为PK.ID列中的当前标识值，如下所示：

Looks like I am going to have to run this piece of code every time I perform a deletion - resetting the maximum identity value to be equal to the current identity value in the PK.ID column after every deletion as shown below:

DECLARE @maxIdentityETValue INT
DECLARE @maxIdentityFValue INT

SET @maxIdentityETValue = (SELECT MAX(Id) FROM ExchangeTypes)
DBCC CHECKIDENT('ExchangeTypes', RESEED, @maxIdentityETValue)

SET @maxIdentityFValue = (SELECT MAX(Id) FROM Fixtures)
DBCC CHECKIDENT('Fixtures', RESEED, @maxIdentityFValue)

DBCC CHECKIDENT ('ExchangeTypes', NORESEED) 
DBCC CHECKIDENT ('Fixtures', NORESEED)

---

编辑2未解决。

---

EDIT 2 NOT SOLVED.

尝试删除然后添加记录，并进一步发布到公告g它不会允许再显示任何后续的添加。 ：（

Tried deleting and then adding records and further to the deleting it would not allow any subsequent additions to be displayed anymore. :(

请参阅身份生成的ID值 - 这是导致问题的原因 - 至少如果我不删除这些数字，然后在删除之后添加记录Datatables显示新的记录，但如果我不再进一步删除记录，然后在添加记录之前，仍然阻止数据表显示记录并显示上述错误。

See the step up in Identity generated ID values - that's what's causing the problem - well at least if I dont reseed those numbers after deletions and before subsequently adding records then Datatables displays the new records. But if I dont reseed them further to deleting records and before subsequently adding records it is still preventing datatables from displaying the records and shows the above error.

Maddening - 有人请帮我！

Maddening - someone please help me!

编辑1：解决了，好的。

EDIT 1: Solved. Well kind of.

添加索引时，至少有一个唯一的索引，这是必要的要重新赋予父和子PK身份生成的ID值，以便数据表工作 - 显示记录错误。

When adding an index, well a unique index at least, it is necessary to reseed both parent and child PK Identity generated ID values in order that the datatables work - displaying the records error free.

不要问我为什么，但它只是

Don't ask me why but it just works.

为了分别在索引，父项和子节点中添加唯一的强制约束，我将两个标识列重新进行了描述，我现在可以添加新的记录到数据库和数据表显示它们很好。

Further to adding my unique enforcing constraints in the indexes, parent and child respectively, I after reseeding both identity columns as descibed, I am now able to add new records to the database and datatables displays them fine.

这篇关于在Entity Framework中添加唯一索引可以防止DataTables显示工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！