How to get around unique key constraint when updating via Entity Framework (using dbcontext.SaveChanges())

Question

I'm running into a problem when updating some data via EF.

Let's say I have a table in my database:

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

I have a unique key constraint on Rank.

For example, I have this data in the table:

My C# object is something like this: Person (name, rank), so on the front end, a user wants to switch the rank of Joe and Mark.

When I make the update via EF, I get an error because of the unique key.
I suspect it is because dbContext.SaveChanges uses a update in this style:

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

With a SQL query I can perform this update by doing this:

Pass in User Defined table (rank, name) from C# side into query and then:

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

and this does not trigger the unique key constraint

However I want to use EF for this operation, what do I do?

I've thought of these other solutions so far:

Delete old records, add "new" records from updated objects via EF
Dropping the unique constraint on database and writing a C# function to do the job of the unique constraint
Just use a SQL query like the example above instead of EF

Note: the table structure and data I used above is just an example

Any ideas?

user_5070879 · Answer

Idea - you could make it as two steps operation(wrapped as single transaction)

1) set values for all entities that have to updated to negative(Joe, -1; Mark -5)

2) set to correct values (Joe, 5, Mark 1)

SQL Server's equivalent:

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 demo

user_1690217 · Answer

You have focused a lot on the SQL side of this, but you can do the same thing in pure EF.

It will help next time to provide you EF code so we can provide you a more specific answer.

NOTE: do not use this logic in EF in scenarios where large sets of data will exist as the ReOrder process loads all records into memory, it is however useful for managing ordinality in child or sub lists that are scoped by an additional filter clause (so not for a whole table!)

The isolated ReOrder process is a good candidate on its own to go to the DB as a stored procedure if you need to do unique ranking logic across the entire table

There are two main variations here (for Unique Values):

Rank is must always be sequential/contiguous
- This simplifies insert and replace logic, but you likely have to manage add, insert, swap and delete scenarios in the code.
- Code to move items up and down in rank are very easy to implement
- MUST manage deletes to re-compute the rank for all items
Rank can have gaps (not all values are contiguous)
- This sounds like it should be easier, but to evaluate moving up and down in the list means you have to take take the gaps into account.
  
  I wont post the code for this variation but be aware it is usually more complicated to maintain.
- On the flip side you don't need to worry about actively managing deletes.

I use the following routine when the ordinal needs to be managed.

NOTE: This routine does not save the changes, it simply loads all the records that might be affected into memory so that we can correctly process the new ranking.

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++;
}

Lets say you can reduce your code to a function that Inserts a new item with a specific Rank into the list or you want to Swap the rank of two items in the list:

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;
}

///  call this when Rank value is changed on a single row 
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();
    }
}

To ensure that Gaps are not introduced, you should call ReOrder after removing items from the table, but before calling SaveChanges()

Alternatively call ReOrder before each of Swap/MoveUp/MoveDown similar to insert.

Keep in mind that it is far simpler to allow duplicate Rank values, especially for large lists of data, but your business requirements will determine if this is a viable solution.

How to get around unique key constraint when updating via Entity Framework (using dbcontext.SaveChanges())

2 Answers