Ordered Collection Navigation Properties (aka Indexed Collections)

[bricelam]

We should support specifying an index column for collection navigation properties so that the order can be maintained transparently.

With types like this...

class Order
{
    public int Id { get; set; }
    public IList<LineItem> LineItems { get; } = new List<LineItem>();
}

class LineItem
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public Order Order { get; set; }
}

...configured something like this...

modelBuilder.Entity<Order>().HasMany(o => o.LineItems).WithOne(i => i.Order)
    .WithIndexColumn("OrderIndex");

...a table like this would be created.

CREATE TABLE LineItem (
    Id INT NOT NULL PRIMARY KEY,
    OrderId INT NOT NULL REFERENCES Order,
    OrderIndex INT NOT NULL
)

Then whenever Order.LineItems is loaded, the order would be guaranteed.

var order = db.Orders.Include(o => o.LineItems).First(o => o.Id == 1);
var firstItem = order.LineItems[0]; // Deterministic!

[ajcvickers]

See also #2919

[divega]

This in fact part of #2919, but I like the level of detail here.

[Bitz]

Has there been any progress with this? Ordering navigation properties still does not translate into the expected ordering in EFC5.0.5

var form = _db.Forms
  .Include(x => x.Sections)
  .ThenInclude(x => x.Rows.OrderBy(y => y.Order)) //Nope!
  .ThenInclude(x => x.Fields.OrderBy(y => y.Order)) //Nope here too!
  .ThenInclude(x => x.FieldType)
  .AsSplitQuery()
  .SingleOrDefaultAsync(x =>
      x.Id == formId)

If there is no intention of ever fixing this, it might be best to show a warning when an attempt to order a navigation property is made to avoid confusion.

[BrettGoreham]

+1 for this issue. double order bys doing nothing is very annoying especially for more complex objects

[Bitz]

+1 for this issue. double order bys doing nothing is very annoying especially for more complex objects

Hey, I believe this will be fixed in 6.0r5

Please see #24706

Thanks

[AndriySvyryd]

Related: #15585

[sjb-sjb]

I believe a variant of this problem is that if the included entities are of the same type (i.e. in the same table) as the primary entities. If the OrderBy specified in the include is inconsistent with the OrderBy of the primary selection, then evidently the OrderBy of the include is ignored.

This makes it more challenging to load a tree structure from a table and have the tree nodes sorted one way in the main selection (say, by name) while having the included children sorted a different way (say, by child relative position).

[AndriySvyryd]

This is also something that should be used for any collections mapped to JSON columns. By having a reasonable guarantee that the order is preserved we can issue partial updates that don't affect other items in the collections. We need to modify how the collections are populated by the change tracker, so that even if some of the items were already tracked we apply the order coming from the query results.

[stevendarby]

I don't understand how the proposed feature would work. If you add a new LineItem without having loaded any Orders or LineItems, what value would it use for OrderIndex?

[AndriySvyryd]

@stevendarby We would probably restrict this to owned collections, so there has to be always a containing Order whenever a LineItem is added.