Support for index ordering (ASC/DESC)

[natemcmaster]

After discussion on #11846, we decided that the way to handle this is to allow multiple named indexes to be created over columns, while still preserving the current semantics (i.e. addressing by ordered property set) for unnamed indexes.

Original issue:
Add support for column sort order to migrations, metadata, and reverse engineering.

• Add API to specific ascending/descending columns in an index (
  CREATE INDEX IX_1 ON Table ( Col1 DESC, Col2 ASC) )
• Add to reverse engineering database model (prototype here)
• Adapt to older version of SQLite (sort order is not supported < 3.8.9)

[jjxtra]

Is this going to happen anytime soon? Using EfCore.Shaman to workaround this issue and others at the moment...

[ajcvickers]

@jjxtra This issue is in the Backlog milestone. This means that it is not going to happen for the 2.1 release. We will re-assess the backlog following the 2.1 release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[roji]

Note that this is being done in Npgsql as part of npgsql/efcore.pg#705. It would ideally be better to have implemented here as well so that Npgsql can align (and reuse the same SortOrder enum, possibly).

[bhugot]

Hello any news ont this feature

[ajcvickers]

@bhugot This issue is in the Backlog milestone. This means that it is not currently planned for any upcoming release. We will re-assess the backlog for 5.0 and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[roji]

Another variant of this issue is for having multiple indexes on the same column, but with different collations (supported by PostgreSQL and Sqlite, but not SQL Server and MySQL).

[lajones]

Clearing Milestone to reconsider this, or part of this, for 5.0 per this comment.

[clement911]

I hope this makes it for the next release.

Our entity has a primary key TenantId bigint, IsPriority bit
Ideally, we want to be able specify DESC on the IsPriority, so that values of 1 are naturally ordered before values of 0 when we select the rows of a given tenant.

As a work around, we have had to changed the name of the property to IsNotPriority, so that the priority rows have a 0 and are ordered first in the index. It's a shame to have to improperly name things because of limitations of EF.

[roji]

Splitting collation support out to #27209