使用Entity Framework（使用dbcontext.SaveChanges()）更新时如何解决唯一键约束？

Question

我是一名可以帮助翻译文本的助手。

我遇到了一个关于通过EF更新一些数据的问题。

假设我有一个数据库表：

Table T (ID int, Rank int, Name varchar)

我在Rank列上有一个唯一键约束。

例如，表中有以下数据：

我的C#对象如下：Person (name, rank)，所以在前端，用户想要交换Joe和Mark的等级。

当我通过EF进行更新时，由于唯一键的原因，会出现错误。
我怀疑这是因为dbContext.SaveChanges使用了以下类型的更新：

UPDATE Table SET rank = 5 where Name = Joe
UPDATE Table SET rank = 1 where Name = Mark

使用 SQL 查询，我可以通过以下方式执行此更新：

在 C# 代码中将用户定义的表格（排名、姓名）传递到查询中，然后执行更新操作：

  update T 
  set T.Rank = Updated.Rank
  from Table T 
  inner join @UserDefinedTable Updated on T.Name = Temp.Name

这不会触发唯一键约束

但是我想在此操作中使用EF，该怎么办？

到目前为止，我考虑了以下解决方案：

• 通过EF删除旧记录，从更新的对象中添加“新”记录

• 在数据库上删除唯一约束，并编写一个C#函数来执行唯一约束的工作

• 不要使用EF，只需像上面的示例一样使用SQL查询

注意：我上面使用的表结构和数据仅为示例

有什么想法吗？

- Pringle22

Answer 1

想法-可以将其作为两个步骤操作（封装为单个事务）

1）设置所有必须更新为负数的实体的值（Joe，-1; Mark -5）

2）设置正确的值（Joe，5，Mark 1）

---

SQL Server的等价物：

SELECT 1 AS ID, 1 AS [rank], 'Joe' AS name INTO t
UNION SELECT 2,2,'Ann'
UNION SELECT 3,5,'Mark'
UNION SELECT 4,7,'Sam';

CREATE UNIQUE INDEX uq ON t([rank]);

SELECT * FROM t;

/* Approach 1
UPDATE t SET [rank] = 5 where Name = 'Joe';
UPDATE t SET [rank] = 1 where Name = 'Mark';

Cannot insert duplicate key row in object 'dbo.t' with unique index 'uq'.
The duplicate key value is (5). Msg 2601 Level 14 State 1 Line 2
Cannot insert duplicate key row in object 'dbo.t' with unique index 'uq'.
The duplicate key value is (1).
*/

BEGIN TRAN
-- step 1

UPDATE t SET [rank] = -[rank] where Name = 'Joe';
UPDATE t SET [rank] = -[rank] where Name = 'Mark';


-- step 2
UPDATE t SET [rank] = 5 where Name = 'Joe'
UPDATE t SET [rank] = 1 where Name = 'Mark';
COMMIT;

db<>fiddle演示

- Lukasz Szozda

---

那真是个非常棒的想法，但不幸的是，在我的情况下可能不太可能实现。目前的设计是即使不需要更新也会循环遍历每个记录（是的，这个设计很糟糕，但在我这种情况下，最坏的情况只有循环遍历12-15条记录）。因此理论上，我将为每一行设置为负数。 - Pringle22

Answer 2

你在关注SQL方面，但是你也可以用纯EF做同样的事情。

下次提供EF代码会更有帮助，这样我们可以给你提供更具体的答案。

注意：在存在大量数据集的情况下，不要在EF中使用此逻辑，因为ReOrder过程会将所有记录加载到内存中。但是，在由附加过滤器子句范围限定的子列表或子列表中管理序数时，它非常有用（因此不适用于整个表！）

如果您需要在整个表中进行唯一排名逻辑，则孤立的ReOrder过程本身就是一个很好的候选项，可以作为存储过程去访问数据库。

这里有两个主要变化（用于唯一值）：

1. 排名必须始终是连续的
   • 这简化了插入和替换逻辑，但您可能需要在代码中管理添加、插入、交换和删除场景。
   • 编写代码以在排名中上下移动项目非常容易实现
   • 必须管理删除以重新计算所有项目的排名
2. 排名可以有间隔（不是所有值都是连续的）
   • 这听起来应该更容易，但要评估在列表中向上和向下移动意味着您必须考虑到间隔。

     我不会发布此变体的代码，但请注意，通常更难维护。

   • 另一方面，您无需担心主动管理删除。

当需要管理序数时，我使用以下例程。

注意：此例程不保存更改，它只是将可能受到影响的所有记录加载到内存中，以便我们可以正确处理新的排名。

public static void ReOrderTableRecords(Context db)
{
    // By convention do not allow the DB to do the ordering. this type of query will load missing DB values into the current dbContext,  
    // but will not replace the objects that are already loaded.
    // The following query would be ordered by the original DB values:
    //      db.Table.OrderBy(x => x.Order).ToList()
    // Instead we want to order by the current modified values in the db Context. This is a very important distinction which is why I have left this comment in place.
    // So, load from the DB into memory and then order:
    //      db.Table[.Where(...optional filter by parentId...)].ToList().OrderBy(x => x.Order)
    // NOTE: in this implementation we must also ensure that we don't include the items that have been flagged for deletion. 
    var currentValues = db.Table.ToList()
                                .Where(x => db.Entry(x).State != EntityState.Deleted)
                                .OrderBy(x => x.Rank);
    int order = 1;
    foreach (var item in currentValues)
        item.Order = order++;
}

假设你可以将代码简化为一个函数，该函数将具有特定排名的新项目插入列表中，或者你想交换列表中两个项目的排名:

public static Table InsertItem(Context db, Table item, int? Rank = 1)
{
    // Rank is optional, allows override of the item.Rank
    if (Rank.HasValue)
        item.Rank = Rank;

    // Default to first item in the list as 1
    if (item.Rank <= 0)
        item.Rank = 1;

    // re-order first, this will ensure no gaps.
    // NOTE: the new item is not yet added to the collection yet
    ReOrderTableRecords(db);

    var items = db.Table.ToList()
                        .Where(x => db.Entry(x).State != EntityState.Deleted)
                        .Where(x => x.Rank >= item.Rank);
    if (items.Any())
    {
        foreach (var i in items)
            i.Rank = i.Rank + 1;
    }
    else if (item.Rank > 1)
    {
        // special case
        // either ReOrderTableRecords(db) again... after adding the item to the table
        item.Rank = db.Table.ToList()
                            .Where(x => db.Entry(x).State != EntityState.Deleted)
                            .Max(x => x.Rank) + 1;
    }

    db.Table.Add(item);
    db.SaveChanges();
    return item;
}

/// <summary> call this when Rank value is changed on a single row </summary>
public static void UpdateRank(Context db, Table item)
{
    var rank = item.Rank;
    item.Rank = -1; // move this item out of the list so it doesn't affect the ranking on reOrder
    ReOrderTableRecords(db); // ensure no gaps

    // use insert logic
    var items = db.Table.ToList()
                        .Where(x => db.Entry(x).State != EntityState.Deleted)
                        .Where(x => x.Rank >= rank);
    if (items.Any())
    {
        foreach (var i in items)
            i.Rank = i.Rank + 1;
    } 
    item.Rank = rank;

    db.SaveChanges();
}

public static void SwapItemsByIds(Context db, int item1Id, int item2Id)
{
    var item1 = db.Table.Single(x => x.Id == item1Id);
    var item2 = db.Table.Single(x => x.Id == item2Id);

    var rank = item1.Rank;
    item1.Rank = item2.Rank;
    item2.Rank = rank;

    db.SaveChanges();
}

public static void MoveUpById(Context db, int item1Id)
{
    var item1 = db.Table.Single(x => x.Id == item1Id);
    var rank = item1.Rank - 1;
    if (rank > 0) // Rank 1 is the highest
    {
        var item2 = db.Table.Single(x => x.Rank == rank);
        item2.Rank = item1.Rank;
        item1.Rank = rank;
        db.SaveChanges();
    }
}
public static void MoveDownById(Context db, int item1Id)
{
    var item1 = db.Table.Single(x => x.Id == item1Id);
    var rank = item1.Rank + 1;
    var item2 = db.Table.SingleOrDefault(x => x.Rank == rank);
    if (item2 != null) // item 1 is already the lowest rank
    {
        item2.Rank = item1.Rank;
        item1.Rank = rank;
        db.SaveChanges();
    }
}

为确保不会引入间隙，您应该在从表格中删除项目之后但在调用 SaveChanges() 之前调用 ReOrder。

或者，在每次调用 Swap/MoveUp/MoveDown 之前类似于插入操作一样调用 ReOrder。

---

请记住，允许重复的排名值会更简单，尤其是对于大量数据的列表，但您的业务需求将决定这是否是可行的解决方案。

- Chris Schaller