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.