How to maintain an ordered list in Entity Framework?

Question

Changing order of elements in a simple list, doesn't stick in Entity Framework. The reason is pretty simple as the ordering information is never stored in the database.

Has anyone come across a generic implementation of ordered list which would work along with Entity Framework?

The requirement is that the user is allowed to reorder list of selected items, and the ordering of items need to be preserved.

Answer 1

to find a solution for this challenge I faced to an article by the following link:

User-defined Order in SQL

this article analyzed different approaches for generating order index value during changing the order of the list. I found the algorithm mentioned in this article so performant by minimum limitation. this algorithm called True Fractions and it generates order index like the following figure:

I have prepared a code sample that I implement this approach by EF Core and InMemory database.

.NET Fiddle Code Sample

Answer 2

Overview

Although there doesn't seem to be any 'magic' to implement this, there is a pattern that we have used to solve this problem, especially when dealing with hierarchies of objects. It boils down to three key things:

1. Build an Entity model separate from your Domain model. This has the benefit of providing a good separation of concerns, effectively allowing your domain model to be designed and changed without getting bogged down by persistence details.
2. Use AutoMapper to overcome the hassle of mapping between the Entity and Domain models.
3. Implement custom value resolvers to map the list in both directions.

The Magic

Because models often include hierarchical and cyclical references between objects, the following Map<>() method can be used to avoid StackOverflow errors during the custom mapping

private class ResolveToDomain : IValueResolver
{
    ResolutionResult Resolve(ResolutionResult rr)
    {
        //...
        ((MappingEngine) rr.Context.Engine).Map<Product, ProductEntity>(rr.Context, subProduct)
        //...
    }
}

The Code

Domain Model. Note that the Subproducts list order is important.

class Product
{
    public Product ParentProduct { get; set; }
    public string Name { get; set; }
    public IList<Product> Subproducts { get; set; } 
}

Entity Model

class ProductEntity
{
    public int Id { get; set; }
    public ProductEntity ParentProduct { get; set; }
    public string Name { get; set; }
    public IList<ProductSubproductEntity> Subproducts { get; set; } 
}

class ProductSubproductEntity
{
    public int ProductId { get; set; }
    public ProductEntity Product { get; set; }
    public int Order { get; set; }
    public ProductEntity Subproduct { get; set; }
}

Entity Framework Context

class Context : DbContext
{
    public DbSet<ProductEntity> Products { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<ProductEntity>()
            .HasOptional(e => e.ParentProduct);

        modelBuilder.Entity<ProductSubproductEntity>()
            .HasKey(e => new {e.ProductId, e.Order})
            .HasRequired(e => e.Product)
            .WithMany(e => e.Subproducts)
            .HasForeignKey(e => e.ProductId);

        base.OnModelCreating(modelBuilder);
    }
}

AutoMapper configuration

class Mappings : Profile
{
    protected override void Configure()
    {
        Mapper.CreateMap<Product, ProductEntity>()
            .ForMember(m => m.Subproducts, a => a.ResolveUsing<ProductSubproductResolver>());

        Mapper.CreateMap<ProductEntity, Product>()
            .ForMember(m => m.Subproducts, a => a.ResolveUsing<ProductSubproductEntityResolver>());

        base.Configure();
    }
}

class ProductSubproductResolver : IValueResolver
{
    public ResolutionResult Resolve(ResolutionResult rr)
    {
        var result = new List<ProductSubproductEntity>();
        var subproductsSource = ((Product) rr.Context.SourceValue).Subproducts;

        if (subproductsSource == null) return rr.New(null);

        for (int i = 0; i < subproductsSource.Count; i++)
        {
            var subProduct = subproductsSource[i];

            result.Add(new ProductSubproductEntity()
            {
                Product = (ProductEntity)rr.Context.DestinationValue,
                Order = i,
                Subproduct = ((MappingEngine) rr.Context.Engine).Map<Product, ProductEntity>(rr.Context, subProduct)
            });
        }
        return rr.New(result);
    }
}

class ProductSubproductEntityResolver: IValueResolver
{
    public ResolutionResult Resolve(ResolutionResult rr)
    {
        var subproductEntitiesSource = ((ProductEntity) rr.Context.SourceValue).Subproducts;

        if (subproductEntitiesSource == null) return rr.New(null);

        var result = subproductEntitiesSource.OrderBy(p => p.Order).Select(p => 
            ((MappingEngine) rr.Context.Engine).Map<ProductEntity, Product>(rr.Context, p.Subproduct))
            .ToList();
        return rr.New(result);
    }
}

Usage

private static IList<Product> CreateDomainProducts()
{
    var result = new List<Product>();

    var mainProduct1 = new Product()
    {
        Name = "T-Shirt"
    };
    var subProduct1 = new Product()
    {
        ParentProduct = mainProduct1,
        Name = "T-Shirt (Medium)",
    };
    var subProduct2 = new Product()
    {
        ParentProduct = mainProduct1,
        Name = "T-Shirt (Large)",
    };
    mainProduct1.Subproducts = new []
    {
        subProduct1,
        subProduct2
    };

    var mainProduct2 = new Product()
    {
        Name = "Shorts"
    };

    result.Add(mainProduct1);
    result.Add(mainProduct2);


    return result;
}

static void Main(string[] args)
{
    Mapper.Initialize(a => a.AddProfile<Mappings>());
    Database.SetInitializer(new DropCreateDatabaseAlways<Context>());

    var products = CreateDomainProducts();
    var productEntities = Mapper.Map<IList<ProductEntity>>(products);

    using (var ctx = new Context())
    {
        ctx.Products.AddRange(productEntities);
        ctx.SaveChanges();
    }
    // Simulating a disconnected scenario...
    using (var ctx = new Context())
    {
        var productEntity = ctx.Products
            .Include(p => p.Subproducts)
            .Include(p => p.Subproducts.Select(p2 => p2.Subproduct))
            .OrderBy(p=>p.Name)
            .ToList();

        var productsResult = Mapper.Map<IList<Product>>(productEntity);

        // Should be 'T-Shirt (Medium)'
        Console.WriteLine(productsResult[1].Subproducts[0].Name);
        // Should be 'T-Shirt (Large)'
        Console.WriteLine(productsResult[1].Subproducts[1].Name);
    }
}

Voila. Hope that helps!

Answer 3

No magic here. If you want to persist a specific order of items in a list (other than a reproducible order by e.g. name) you must store a sequence number in the database.

Answer 4

There wont be an implementation of this for reordering on the database. The data in the database is physically ordered by default by the clustered index which is in essence ordering by the primary key.

Why do you want to do this? EF encourages all ordering to be done via LINQ queries.

If you are looking to optimize lookups you can create additional non-clustered indexes on the database by modifying the code generated for Migrations :

CreateTable(
                "dbo.People",
                c => new
                    {
                        ID = c.Int(nullable: false, identity: true), 
                        Name = c.String()
                    })
                .PrimaryKey(t => t.ID)
                .Index(t => t.Name); // Create an index

Note that this will not impact the physical ordering in the database but will speed lookups, although this need to be balanced by slower writes/updates.