Best way to implement "ReOrderable Collection" and Persist it to database

Question

Hi,

My domain object :

public class MyDomainObject
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public int DisplayOrder { get; set; }
}

Assuming sample data :

var list = new List<MyDomainObject>()
               {
                   new MyDomainObject {Name = "Element1", DisplayOrder = 0},
                   new MyDomainObject {Name = "Element2", DisplayOrder = 1},
                   new MyDomainObject {Name = "Element3", DisplayOrder = 2},
                   new MyDomainObject {Name = "Element4", DisplayOrder = 3},
               };

Now i change the DisplayOrder of the "Element3" from 2 to 1. My list should looks like that :

• Element1 (DisplayOrder = 0)
• Element3 (DisplayOrder = 1)
• Element2 (DisplayOrder = 2)
• Element4 (DisplayOrder = 3)

Now i remove "Element3"

• Element1 (DisplayOrder = 0)
• Element2 (DisplayOrder = 1)
• Element4 (DisplayOrder = 2)

So what's the best way to persist this mechanism to database ?

Basically i need a "ReOrderableCollection" which will be populated from database with an OrderBy "DisplayOrder" where Collection Index Match "DisplayOrder", and persist back items by assigning DisplayOrder from Collection Index.

Answer 1

For what I can see it seems that DisplayOrder has the same value of the index property of the collection. So I will try to use that instead of a DisplayOrder property. On the DB I will use the DisplayOrder column to read and save the items but not on the domain objects. HTH ema

Answer 2

From your examples it seems that you always want the sequence to be without gaps, starting from zero. But this means that removing the first element will require updating the row in the database for every single item in your list. It's simple and it will work (and these are good things) but it's not always ideal. Since you asked for "the best way" without really specifying what you mean by that, allow me to suggest an alternative method:

What really matters with a DisplayOrder is not the actual values but their relative order. If you want to improve performance with the database, you could consider relaxing the requirement that there should be no gaps and then try to find the smallest number of changes to the DisplayOrders to ensure that the correct order is stored, even if gaps are present in the resulting sequence. If you do this then adding, removing or moving a single item will typically only require updating one row in the database, with the exception that occasionally other items will have to be moved to create a gap where an item must be inserted between two others that have consecutive DisplayOrders.

You can also minimize the number of times that a gap is not available by starting with DisplayOrder 100, 200, 300 and later allowing for example an insertion with DisplayOrder 150 in between (or perhaps use a real/float type instead of an integer).

Another advantage of this method is if you use a database data comparison tool to observe changes between the current version of the database and older versions it will be easier to see what modifications have been made to the display order. You will only see changes in the display order of items that have actually been moved by the user, rather than half the list change each time an item is removed. It will also reduce the size of backups if you use an incremental backup strategy.

I'd say though that these advantages are not significant advantages over the naive method for most cases. It depends on your system whether it is worth implementing this system or just keeping it simple. If in doubt, keep it simple. For systems with small lists, few modifications and where you don't care about the change history, overwriting the entire list with new DisplayOrders each time will probably be just fine.

Answer 3

I answered a previous/similar question about re-ordering here: http://stackoverflow.com/questions/1581467/how-to-design-table-that-can-be-re-sequenced/1581868#1581868

This does a good job of resaving the Order with no gaps. Depending on the size the lists resaving the Order may be a perfectly viable option, for long lists Mark Byers' idea looks pretty good.

Answer 4

Now I'm assuming that you do want to always reorganize your list so that the DisplayOrder starts at 0 and increases without gaps, and you want this to happen automatically. You could implement your own collection type and an interface IDisplayOrderable and have the members of your type that change the list also automaticaly update the DisplayOrder of the items in the collection. As opposed to my other answer which was about an alternative way to store the data in the datase, this answer shows how to write a client class that could make it easier to automatically synchronize the DisplayOrder in your objects with your list indexes so that when you are ready to submit the changes to the database, the DisplayOrder field is already set correctly for you.

I think the answer is best given as some source code:

using System;
using System.Collections.Generic;
using System.Linq;

interface IDisplayOrderable
{
    int DisplayOrder { get; set; }
}

class ReorderableList<T> : IList<T> where T : IDisplayOrderable
{
    List<T> list = new List<T>();

    private void updateDisplayOrders()
    {
        int displayOrder = 0;
        foreach (T t in list)
        {
            t.DisplayOrder = displayOrder++;
        }
    }

    public ReorderableList() { }

    public ReorderableList(IEnumerable<T> items)
    {
        list = new List<T>(items.OrderBy(item => item.DisplayOrder));
    }

    public void Insert(int index, T item)
    {
        list.Insert(index, item);
        updateDisplayOrders();
    }

    public void Add(T item)
    {
        list.Add(item);
        updateDisplayOrders();
    }

    public bool Remove(T item)
    {
        bool result = list.Remove(item);
        if (result)
            updateDisplayOrders();
        return result;
    }

    public IEnumerator<T> GetEnumerator()
    {
        return list.GetEnumerator();
    }

    // TODO: Other members and methods required to implement IList<T>...    
}

class Item : IDisplayOrderable
{
    public string Name { get; set; }
    public int DisplayOrder { get; set; }
}

class Program
{
    static void Main()
    {
        Item foo = new Item { Name = "foo", DisplayOrder = 0 };
        Item bar = new Item { Name = "bar", DisplayOrder = 1 };
        Item baz = new Item { Name = "baz", DisplayOrder = 2 };

        // Pretend this came from the database.
        IEnumerable<Item> query = new Item[] { bar, foo };

        // The constructor automatically reorder the elements.
        ReorderableList<Item> items = new ReorderableList<Item>(query);
        items.Add(baz);
        items.Remove(foo);
        items.Insert(1, foo);

        foreach (Item item in items)
            Console.WriteLine("{0} : {1}", item.Name, item.DisplayOrder);
    }
}

Output:

bar : 0
foo : 1
baz : 2

Perhaps this was the sort of answer you were looking for?

Answer 5

I maybe founded a solution by creating a custom List which take an Lamba Expression in constructor parameter in order the list to be able to self update items property "DisplayOrder".

The sample class

public class MyItem
{
    public string Name { get; set; }
    public int DisplayOrder { get; set; }
}

The sample program

public class Program
{
    static void Main(string[] args)
    {
        var list = new DisplayOrderableList<MyItem>(p => p.DisplayOrder)
                       {
                           new MyItem{ Name = "Item 1"},
                           new MyItem{ Name = "Item 2"},
                           new MyItem{ Name = "Item 3"},
                       };

        var item = list.Where(p => p.Name == "Item 2").FirstOrDefault();

        list.MoveUp(item);

        list.ForEach(p => Console.WriteLine("{0}-{1}", p.Name, p.DisplayOrder));
        Console.WriteLine();

        list.MoveDown(item);

        list.ForEach(p => Console.WriteLine("{0}-{1}", p.Name, p.DisplayOrder));
        Console.WriteLine();

        Console.ReadLine();
    }
}

The custom implementation of DisplayOrderableList

public class DisplayOrderableList<T> : List<T>
{
    #region Private Fields

    private PropertyInfo _property;

    #endregion

    #region Constructors

    public DisplayOrderableList(Expression<Func<T, int>> expression)
    {
        ValidateExpression(expression);
    }

    #endregion

    #region Public Methods

    public void MoveUp(T item)
    {
        if (!Contains(item))
            throw new ArgumentNullException("item", "item doesn't exists in collection");

        var idx = IndexOf(item);

        RemoveAt(idx);
        if (idx > 0)
            Insert(idx - 1, item);
        else
            Insert(0, item);

        UpdateDisplayOrder();
    }

    public void MoveDown(T item)
    {
        if (!Contains(item))
            throw new ArgumentNullException("item", "item doesn't exists in collection");

        var idx = IndexOf(item);

        RemoveAt(idx);
        if (idx + 1 > Count)
            Add(item);
        else
            Insert(idx + 1, item);

        UpdateDisplayOrder();
    }

    #endregion


    #region Private Methods

    private void UpdateDisplayOrder()
    {
        foreach (var item in this)
        {
            _property.SetValue(item, IndexOf(item), null);
        }
    }

    #endregion

    #region Expression Methods

    private void ValidateExpression(Expression<Func<T, int>> expression)
    {
        var lamba = ToLambaExpression(expression);

        var propInfo = ToPropertyInfo(lamba);

        if (!propInfo.CanWrite)
        {
            throw new ArgumentException(String.Format("Property {0} as no setters", propInfo.Name));
        }

        _property = propInfo;
    }

    private static LambdaExpression ToLambaExpression(Expression expression)
    {
        var lambda = expression as LambdaExpression;
        if (lambda == null)
        {
            throw new ArgumentException("Invalid Expression");
        }
        var convert = lambda.Body as UnaryExpression;
        if (convert != null && convert.NodeType == ExpressionType.Convert)
        {
            lambda = Expression.Lambda(convert.Operand, lambda.Parameters.ToArray());
        }
        return lambda;
    }

    private static PropertyInfo ToPropertyInfo(LambdaExpression expression)
    {
        if (expression == null)
        {
            throw new ArgumentNullException("expression", "Expression cannot be null.");
        }

        var prop = expression.Body as MemberExpression;
        if (prop == null)
        {
            throw new ArgumentException("Invalid expression");
        }

        var propInfo = prop.Member as PropertyInfo;
        if (propInfo == null)
        {
            throw new ArgumentException("Invalid property");
        }

        return propInfo;
    }

    #endregion
}

This now get the following output :

Item 2-0
Item 1-1
Item 3-2

Item 1-0
Item 2-1
Item 3-2

It's a proof of concept and should be enhanced but it's a beggining.

What do you think about this ?