Perform multiple LINQ updates to avoid uniquekey exception

Question

I have a database with a Unique key on the the columns ParentRef and SortIndex.

In LINQ, I want to switch two SortIndex values. I want to do this in one transaction so there can be multiple users at once. How do I, with LINQ, switch the values in one go, so my Unique key does not be violated?

        var dc = new MyDataContext();

        using (TransactionScope trans = new TransactionScope())
        {
            var pageToBeMoved = dc.Pages.Where(p => p.ID == id).Single();
            var pageToBeSwitched = (from p in dc.Pages
                                    where p.ParentRef == pageToBeMoved.ParentRef
                                    where p.SortIndex > pageToBeMoved.SortIndex
                                    orderby p.SortIndex ascending
                                    select p).First();

            int tempSortIndex = pageToBeMoved.SortIndex;

            pageToBeMoved.SortIndex = pageToBeSwitched.SortIndex;
            pageToBeSwitched.SortIndex = tempSortIndex;

            dc.SubmitChanges();

            trans.Complete();
        }

score 0 · Accepted Answer · answered Sep 20 '09 at 09:06

I think that to switch unique key values, you might need to use a third temporary value during the switch, that is:

create new value
set page2.SortIndex to new value
set page1.SortIndex to old page2.SortIndex
set page2.SortIndex to old page1.SortIndex

... otherwise you are likely to hit a unique key violation during the switch.

Something along these lines:

    var dc = new MyDataContext();

    using (TransactionScope trans = new TransactionScope())
    {
        var pageToBeMoved = dc.Pages.Where(p => p.ID == id).Single();
        var pageToBeSwitched = (from p in dc.Pages
                                where p.ParentRef == pageToBeMoved.ParentRef
                                where p.SortIndex > pageToBeMoved.SortIndex
                                orderby p.SortIndex ascending
                                select p).First();

        int oldMSortIndex = pageToBeMoved.SortIndex;
        int oldSSortIndex = pageToBeSwitched.SortIndex;
        // note: here you need to use some value that you know will not already 
        // be in the table ... maybe a max + 1 or something like that
        int tempSortIndex = someunusedvalue;

        pageToBeMoved.SortIndex = tempSortIndex;
        dc.SubmitChanges();
        pageToBeSwitched.SortIndex = oldMSortIndex;
        dc.SubmitChanges();
        pageToBeMoved.SortIndex = oldSSortIndex;
        dc.SubmitChanges();
    }

Thanks for reply, I have already tried setting the first one with 0. But that does not work either. I'm considering removing my unique key and rely on my code works with the transaction. — Lasse Espeholt, Sep 20 '09 at 19:59
Yes I do. Exactly as with my original code. I guess it might have something to do with transactions. — Lasse Espeholt, Sep 23 '09 at 15:28
ah yes, that could be it. maybe the last stage has to be in a separate trans — codeulike, Sep 23 '09 at 17:07
I'm not sure that will work because there could be a query between the transactions. But maybe it can be done in a clever way... — Lasse Espeholt, Sep 23 '09 at 19:27
See also, here: http://stackoverflow.com/questions/644/swap-unique-indexed-column-values-in-database ... delete both rows and re-insert them is one way around this ... a bit drastic but could be done within a transaction I guess — codeulike, Sep 23 '09 at 20:06

Perform multiple LINQ updates to avoid uniquekey exception

1 Answers1