views:

120

answers:

1

Hi,

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();
        }
A: 

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();
    }
codeulike
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.
lasseespeholt
In what way does it not work? Do you get a key violation?
codeulike
Yes I do. Exactly as with my original code. I guess it might have something to do with transactions.
lasseespeholt
ah yes, that could be it. maybe the last stage has to be in a separate trans
codeulike
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...
lasseespeholt
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