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

问题描述

这是一个非常简单的结构 - 外键为一对多的父子.

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] 

开始
DBCC CHECKIDENT ('ExchangeTypes', RESEED, 7)去

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.

在唯一字段上使用索引尝试过它，它仍然出现相同的错误.有趣的是，如何在父查找字段上应用唯一索引不会阻止数据表工作，但在子表上尝试相同的工作会阻止.

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.

推荐答案

EDIT 3 LOOKS LIKE BE SOLUTION WITH WORKAROUND

EDIT 3 LOOKS LIKE COULD BE SOLVED WITH WORKAROUND

我从不知道这会像这样愚蠢地不可预测，认为它会通过自动增量来照顾自己，并且从未想过它会对数据表行显示产生如此令人望而却步的影响，但任何看起来像这样的方法都可以适用初始测试，除非它以某种方式中断，因为我只通过删除和随后添加记录对其进行了一次测试.无论如何，这是一个合适的解决方法，除非有人能够深入了解并提供更好的解决方案.

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.

尝试删除然后添加记录，进一步删除它不允许再显示任何后续添加.:(

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

查看身份生成的 ID 值中的步骤 - 这就是导致问题的原因 - 至少如果我在删除之后和随后添加记录之前不重新设置这些数字，那么数据表会显示新记录.但是，如果我不将它们进一步重新播种以删除记录，并且在随后添加记录之前，它仍然会阻止数据表显示记录并显示上述错误.

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 - 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屋！