Moving records up and down with Linq to SQL

Question

I need to implement a function for moving records up and down (sorting) and saving the sortorder with Linq to SQL. I am using SQL Server 2000 but I might be able to upgrade if there is a solution for it with a newer version of SQL Server. I would love to hear any thoughts you might have on how to do it.

Answer 1

Just add an interger column Index to the table and modify this index based on the user input - moving up is just decrementing the index value of the selected record and incrementing the index value of the preceding record.

public void MoveUp(Guid id)
{
    Item item = Context.Items.Single(i => i.Id == id);

    if (item.Index > 0)
    {
        Item predecessor = Context.Items.Single(i => i.Index == item.Index - 1);

        item.Index -= 1;
        predecessor.Index += 1;

        Context.SaveChanges();
    }
}

Do the opposite for moving down and you are done. If you need this for multiple tables, just create a generic version using an interface.

Answer 2

Thanks Daniel! From looking at your example I came up with this for sorting products within a category.

public void MoveUp(int categoryId, int productId, int originalIndex, int newIndex)
{
    if (newIndex == originalIndex) return;

    var product = _context.CategoryProducts.Single(x => x.CategoryId == categoryId && x.ProductId == productId);
    product.SortOrder = newIndex;

    _context.CategoryProducts
     .Where(x =>
         x.CategoryId == categoryId &&
         x.ProductId != productId &&
         x.SortOrder >= newIndex &&
         x.SortOrder <= originalIndex)
     .Update(x => { x.SortOrder = x.SortOrder + 1; });

    _context.SubmitChanges();
}

public void MoveDown(int categoryId, int productId, int originalIndex, int newIndex)
{
    if (newIndex == originalIndex) return;

    var product = _context.CategoryProducts.Single(x => x.CategoryId == categoryId && x.ProductId == productId);
    product.SortOrder = newIndex;

    _context.CategoryProducts
     .Where(x =>
         x.CategoryId == categoryId &&
         x.ProductId != productId &&
         x.SortOrder >= originalIndex &&
         x.SortOrder <= newIndex)
     .Update(x => { x.SortOrder = x.SortOrder - 1; });

    _context.SubmitChanges();
}

I used the UpdatedExtension from Hooked on LINQ for the actual updating.