Update multiple sequence numbers on unique index

Question

similar to this question I have a unique index on two fields, constraining an ID field and the sequence number to be unique. Now I change the sequence numbers via drag and drop - after that these numbers are still unique.

But EF (or the SQL Server respectively) refuse the change since it proceeds record by record, therefore violating this constaint temporarily.

Is there any (EF-built-in) chance to tell SQL Server that he has not to worry about constraints during the update operation of several records, but afterwards?

I do not need any tipps/workarounds like "appending" sequence numbers at the end.

The problem is: Whenever updating multiple records on an unique index, during the record-by-record-operation the index might be violated, but afterwards (at the end of the transaction) everything will be fine. How can we do this in EF 6?

Kind regards, Mate

Code sample: 1.) Model und Context

public class Model
{
    [Key]
    public long Key { get; set; }

    [Index("MyUniqueIndex", IsUnique = true)]
    public long IndexField { get; set; }
}

public class ModelContext : DbContext
{
    public DbSet<Model> ModelDbSet { get; set; }

    public ModelContext()
        : base("name = ModelContext")
    {
        Database.Log = Console.Write;
    }
}

2.) Snippet Application

private void button1_Click(object sender, EventArgs e)
    {
        CreateRecords();
    }

    private void button2_Click(object sender, EventArgs e)
    {
        SwapIndexAndSave();
    }

    public static void CreateRecords()
    {
        using (ModelContext context = new ModelContext())
        {
            // Create two records
            // Record 1 has value 1 in the unique index field
            // Record 2 has value 2 in the unique index field
            Model newModel1 = new Model {IndexField = 1};
            context.ModelDbSet.Add(newModel1);

            Model newModel2 = new Model {IndexField = 2};
            context.ModelDbSet.Add(newModel2);

            context.SaveChanges();
        }
    }

    public static void SwapIndexAndSave()
    {
        using (ModelContext context = new ModelContext())
        {
            // Load both records
            List<Model> data = context.ModelDbSet.ToList();
            // Swap values in unique index field
            // Record 1 will get value 2 in the unique index field
            // Record 2 will get value 1 in the unique index field
            foreach (Model model in data)
            {
                model.IndexField = 3 - model.IndexField;
            }
            // SaveChanges crashes, because on updating the first record will
            // result in identically values for the unique index field on the database.
            context.SaveChanges();
        }
    }

Unfortunately, as of right now, SQL Server does not support [deferrable constraints](http://stackoverflow.com/questions/998095/deferrable-constraints-in-sql-server) of any kind. The best "work around" would be to write a single `UPDATE` statement that performs all of the changes in one go, *rather* the proceeding row-by-row. SQL works *well* working with set-based logic. — Damien_The_Unbeliever, Jun 02 '16 at 07:26

Nirav · Answer 1 · 2016-06-01T10:34:32.370

0

Why don't you try with disabling constraint only for the given transaction..

I mean, 1) disable constraint 2) Update value to database 3) enable constraint

let me know if you need more info on this

Dear Mate,

I think I am not good with explanation, you can enable/ disable a particular constraint for a table

--disable the CK_Customer_CustomerType constraint ALTER TABLE Sales.Customer NOCHECK CONSTRAINT CK_Customer_CustomerType

--do something

--enable the CK_Customer_CustomerType constraint ALTER TABLE Sales.Customer CHECK CONSTRAINT CK_Customer_CustomerType

Please check MSDN Link

edited Jun 01 '16 at 10:34

answered Jun 01 '16 at 10:16

Nirav

21
3

I would like to let you know: How do you dis-/enable constraints in that scenario? And (perhaps worse): How do you do this in the override of SaveChanges, where multiple record changes (not only those of the described kind) might happen? All unique constraints in the whole database might be disabled before and enabled afterwards... – Mate Jun 01 '16 at 10:23
--disable the CK_Customer_CustomerType constraint ALTER TABLE Sales.Customer NOCHECK CONSTRAINT CK_Customer_CustomerType --do something --enable the CK_Customer_CustomerType constraint ALTER TABLE Sales.Customer CHECK CONSTRAINT CK_Customer_CustomerType – Nirav Jun 01 '16 at 10:31
That's the problem: I do not know anything about the concerned tables. I have many change tracker entries processed during SaveChanges. So I might call ALTER TABLE for every table in the database. Considering the perfect universe, EF or the DBMS provide this feature directly by setting one property... :-( And to make the word perfect: discard/rollback changes if the turned-on constraints are violated. – Mate Jun 01 '16 at 11:04
can you provide me the structure and your sample code... I would like to run some test on it... a sample will be fine if you can provide – Nirav Jun 02 '16 at 04:19
see original question. – Mate Jun 02 '16 at 07:13
1

The problem with disabling constraints is a) it requires you to have quite a lot of permissions and b) it affects *all* users of the database, in that anyone can populate data that won't meet the constraints whilst they're disabled. So effectively, this is only an option if the system is used by a single user (at least for the period of time that they're doing this activity) – Damien_The_Unbeliever Jun 02 '16 at 07:43
:( not much luck with testing... One more thing, I would like to ask, can you use Stored Procedure ? If yes, than I can provide the solution, (even you can also do it, it's very easy) Let me know if you need help with SP... :) – Nirav Jun 02 '16 at 12:51

Update multiple sequence numbers on unique index

1 Answers1