EntityFramework.OrderBy

See Milestones for release notes.

Applies default ordering to EntityFramework queries based on fluent configuration. This ensures consistent query results and prevents non-deterministic ordering issues.

NuGet package

https://nuget.org/packages/EfOrderBy/

Features

• Automatic ordering: Queries without explicit OrderBy automatically use configured default ordering
• Include() support: Nested collections in .Include() expressions are automatically ordered
• Deterministic paging: Ordering is applied before Skip/Take/First, so pages and single results are stable
• Inheritance support: Ordering configured on a base entity type is automatically inherited by derived types (TPH)
• Fluent configuration: Configure default ordering using the familiar EF Core fluent API
• Multi-column ordering: Chain multiple ordering clauses with ThenBy and ThenByDescending
• JSON column support: Order by a property inside a ToJson() mapped column, at any nesting depth
• Automatic indexes: Database indexes are automatically created for ordering columns
• Validation mode: Optionally require all entities to have default ordering configured
• Redundant ordering detection: Optionally throw when a query explicitly applies the same ordering as the configured default, per context or process wide

Usage

1. Enable the interceptor

Configure the default ordering interceptor in the DbContext:

protectedoverridevoidOnConfiguring(DbContextOptionsBuilderbuilder)=>builder.UseDefaultOrderBy();

^{snippet source | anchor}

2. Configure entity ordering

Use the fluent API to configure default ordering for entities:

protectedoverridevoidOnModelCreating(ModelBuilderbuilder){builder.Entity<Employee>().OrderBy(_ =>_.HireDate).ThenByDescending(_ =>_.Salary);builder.Entity<Department>().OrderBy(_ =>_.DisplayOrder);}

^{snippet source | anchor}

3. Query without explicit OrderBy

Queries without explicit ordering automatically use the configured default:

// Automatically ordered by HireDate, then Salary descendingvaremployees=awaitcontext.Employees.ToListAsync();// Explicit ordering takes precedencevaremployeesByName=awaitcontext.Employees.OrderBy(_ =>_.Name).ToListAsync();

^{snippet source | anchor}

Paging and Single Results

The default ordering is applied before any operator that chooses which rows come back, so Skip, Take, First, FirstOrDefault, Last, LastOrDefault and ElementAt all select from an ordered sequence:

// The ordering is applied before the page is taken, so the page is// taken from an ordered sequence rather than sorted after the factvarsecondPage=awaitcontext.Employees.Skip(20).Take(20).ToListAsync();// Ordered by HireDate, then Salary descending, so this is the// earliest hire rather than an arbitrary rowvarfirst=awaitcontext.Employees.FirstAsync();

^{snippet source | anchor}

Without this, a page would be an arbitrary set of rows that happens to be sorted, and pages could both skip and repeat rows.

Aggregates such as Count and Any, and Single, are left alone, since ordering them is pointless work.

Include() Support

Nested collections in .Include() expressions are automatically ordered:

// Departments ordered by DisplayOrder// Employees ordered by HireDate, then Salary descendingvardepartments=awaitcontext.Departments.Include(_ =>_.Employees).ToListAsync();

^{snippet source | anchor}

Inheritance Support

When using TPH (Table Per Hierarchy) inheritance, ordering configured on a base entity type is automatically inherited by derived types. This eliminates the need to duplicate .OrderBy() on every derived type.

publicclassBaseEntity{publicintId{get;set;}publicstringName{get;set;}="";publicintSortOrder{get;set;}}publicclassDerivedEntityA:BaseEntity{publicstringExtraA{get;set;}="";}publicclassDerivedEntityB:BaseEntity{publicstringExtraB{get;set;}="";}publicclassInheritanceDbContext:DbContext{protectedoverridevoidOnConfiguring(DbContextOptionsBuilderbuilder){builder.UseSqlServer("connection-string").UseDefaultOrderBy();}protectedoverridevoidOnModelCreating(ModelBuilderbuilder){// Configure ordering on the base entity// DerivedEntityA and DerivedEntityB automatically inherit this orderingbuilder.Entity<BaseEntity>().OrderBy(_ =>_.SortOrder);// Optionally, a derived type can override with its own orderingbuilder.Entity<DerivedEntityB>().OrderByDescending(_ =>_.Name);}publicDbSet<BaseEntity>BaseEntities=>Set<BaseEntity>();publicDbSet<DerivedEntityA>DerivedEntitiesA=>Set<DerivedEntityA>();publicDbSet<DerivedEntityB>DerivedEntitiesB=>Set<DerivedEntityB>();}

^{snippet source | anchor}

Behavior:

• context.DerivedEntitiesA.ToListAsync() is ordered by SortOrder (inherited from BaseEntity)
• context.DerivedEntitiesB.ToListAsync() is ordered by Name descending (its own explicit configuration)
• Derived types with their own .OrderBy() take precedence over the base type's ordering
• Inherited orderings do not create duplicate database indexes (the base type's index covers the same columns)

Multi-Column Ordering

Chain multiple ordering clauses using ThenBy and ThenByDescending:

builder.Entity<Product>().OrderBy(_ =>_.Category).ThenBy(_ =>_.Name).ThenByDescending(_ =>_.Price);

^{snippet source | anchor}

JSON Columns

Default ordering can target a property inside a column mapped with ToJson(). Pass the full property path, and EF Core translates it to a read of the JSON document:

protectedoverridevoidOnModelCreating(ModelBuilderbuilder){builder.Entity<Order>().OwnsOne(_ =>_.Metadata, _ =>_.ToJson());// Orders by a property of the JSON document rather than a columnbuilder.Entity<Order>().OrderByDescending(_ =>_.Metadata.Priority).ThenBy(_ =>_.Reference);}

^{snippet source | anchor}

The above produces:

ORDER BY CAST(JSON_VALUE([o].[Metadata], '$.Priority') ASint) DESC, [o].[Reference]

Paths of any depth are supported, so an owned type nested inside another owned type works the same way:

builder.Entity<Article>().OrderByDescending(_ =>_.Info.Audit.Modified);// ORDER BY CAST(JSON_VALUE([a].[Info], '$.Audit.Modified') AS datetime2) DESC

JSON properties can be mixed freely with ordinary columns in the same ordering chain.

Everything else behaves as it does for a column: the ordering is applied before Skip/Take/First, explicit ordering in a query still takes precedence, and redundant ordering detection recognises the path.

Indexes for JSON properties

A JSON property is not a column of the entity's table, so there is nothing to name in an index. Automatic index creation is skipped for any ordering that reaches into JSON — the ordering itself still applies. As with string columns, a composite ordering is skipped whole when any one of its clauses reaches into JSON.

To index a JSON property, map a computed column over it and index that using the provider's own tooling.

JSON collections

A collection mapped with ToJson() is read out of its parent's JSON document rather than joined, and EF Core rejects an ordered Include over one on a tracking query. Applying default ordering to such a collection would break queries that work today, so JSON collections are left in document order:

builder.Entity<Product>().OwnsMany(_ =>_.Tags, _ =>_.ToJson());// Tags come back in the order they are stored in the JSON arrayvarproducts=awaitcontext.Products.Include(_ =>_.Tags).ToListAsync();

To control the order of a JSON collection, store it ordered, or sort it after materialization.

Automatic Index Creation

When configuring default ordering, a database index is automatically created for the ordering columns. This improves query performance since the database can use the index when sorting.

builder.Entity<Product>().OrderBy(_ =>_.Category).ThenBy(_ =>_.Name).ThenByDescending(_ =>_.Price);// Automatically creates index: IX_Product_DefaultOrder (Category, Name, Price)

The index:

• Is named IX_{EntityName}_DefaultOrder
• Contains all columns in the ordering chain as a composite index
• Is automatically updated when using ThenBy/ThenByDescending

This eliminates the need to manually create indexes that match the ordering configuration.

Custom Index Names

The auto-generated index name must not exceed 128 characters (SQL Server limit). If an entity name is too long, use WithIndexName to specify a custom index name:

builder.Entity<EntityWithVeryLongNameThatWouldExceedTheLimit>().OrderBy(_ =>_.Name).WithIndexName("IX_LongEntity_Order");

If the auto-generated name exceeds 128 characters, an Exception is thrown with a message suggesting to use WithIndexName().

String Column Indexes

Some database providers silently cap string column lengths when an index is added. For example, SQL Server limits index keys to 900 bytes, so EF Core's SQL Server provider automatically reduces nvarchar columns to 450 characters when indexed.

To prevent this unexpected column modification, automatic index creation is skipped for string properties that have no MaxLength configured or a MaxLength exceeding the provider's limit. The limit is determined by querying the provider's RelationalTypeMappingSource, so it automatically adapts to any database engine.

To include a string column in the automatic index, configure a MaxLength within the provider's limit:

builder.Entity<Product>().Property(_ =>_.Category).HasMaxLength(450);builder.Entity<Product>().OrderBy(_ =>_.Category);// Index is created because Category has MaxLength ≤ 450

If the MaxLength is not configured or exceeds the limit, the ordering still works — only the automatic index is skipped:

builder.Entity<Product>().OrderBy(_ =>_.Category);// No index created (Category has no MaxLength), but ordering is still applied to queries

For composite indexes, if any string column exceeds the limit, the entire index is skipped.

Providers without a string index size limit (e.g. PostgreSQL, SQLite) always create the index regardless of MaxLength.

Disabling Index Creation

To opt out of automatic index creation (for example, if indexes are managed separately):

protectedoverridevoidOnConfiguring(DbContextOptionsBuilderbuilder)=>builder.UseDefaultOrderBy(createIndexes:false);

^{snippet source | anchor}

When index creation is disabled, calling WithIndexName() throws an Exception.

Require Ordering for All Entities

Enable validation mode to ensure all entities have default ordering configured:

protectedoverridevoidOnConfiguring(DbContextOptionsBuilderbuilder)=>builder.UseDefaultOrderBy(requireOrderingForAllEntities:true);

^{snippet source | anchor}

This throws an exception during the first query if any entity type lacks default ordering configuration:

Default ordering is required for all entity types but the following entities
do not have ordering configured: Product, Customer.
Use modelBuilder.Entity<T>().OrderBy() to configure default ordering.

Validation occurs once per DbContext type for performance.

Detect Redundant Ordering

Explicit ordering in a query takes precedence over the configured default. When that explicit ordering is an exact match for the default, it is redundant. This is usually a leftover from before the default ordering was configured.

Enable detection to throw when a query does this. This is primarily useful in tests:

protectedoverridevoidOnConfiguring(DbContextOptionsBuilderbuilder)=>builder.UseDefaultOrderBy(throwOnRedundantOrderBy:true);

^{snippet source | anchor}

Given this configuration:

builder.Entity<Employee>().OrderBy(_ =>_.HireDate).ThenByDescending(_ =>_.Salary);

The following query throws, since it applies exactly the default ordering:

varemployees=awaitcontext.Employees.OrderBy(_ =>_.HireDate).ThenByDescending(_ =>_.Salary).ToListAsync();

The query explicitly orders 'Employee' by OrderBy(HireDate).ThenByDescending(Salary),
which exactly matches the default ordering configured for that entity.
Remove the ordering from the query since it is applied automatically,
or change it to a different ordering.

The match must be exact: the same properties, in the same sequence, with the same directions. Anything else is treated as an intentional override and does not throw:

// Only part of the default ordering.OrderBy(_ =>_.HireDate)// Different direction.OrderByDescending(_ =>_.HireDate).ThenByDescending(_ =>_.Salary)// Additional ordering column.OrderBy(_ =>_.HireDate).ThenByDescending(_ =>_.Salary).ThenBy(_ =>_.Name)

Ordering of nested collections in Include() is checked the same way:

// Throws, since it matches the default ordering for Employeevardepartments=awaitcontext.Departments.Include(_ =>_.Employees.OrderBy(_ =>_.HireDate).ThenByDescending(_ =>_.Salary)).ToListAsync();

Detection happens during query compilation, so it applies to every distinct query the application executes, whether or not the query hits the database.

Enabling for a whole test project

Setting it on every DbContext gets tedious in a test project that has several. OrderBySettings.ThrowOnRedundantOrderBy is a process wide default, so a module initializer turns it on for all of them:

[ModuleInitializer]publicstaticvoidInitialize()=>OrderBySettings.ThrowOnRedundantOrderBy=true;

^{snippet source | anchor}

UseDefaultOrderBy reads this setting only when its throwOnRedundantOrderBy parameter is left unset. Passing a value always wins, so an individual context can opt out:

// Passing false explicitly overrides OrderBySettings.ThrowOnRedundantOrderByprotectedoverridevoidOnConfiguring(DbContextOptionsBuilderbuilder)=>builder.UseDefaultOrderBy(throwOnRedundantOrderBy:false);

^{snippet source | anchor}

The setting is read during query compilation rather than when the options are built, so it applies to contexts whose options were built before the initializer ran.

Configuration Errors

Calling OrderBy or OrderByDescending multiple times for the same entity type throws an Exception:

// WRONG - throws Exceptionbuilder.Entity<Employee>().OrderBy(_ =>_.HireDate);builder.Entity<Employee>().OrderBy(_ =>_.Salary);// Error// CORRECT - use ThenBy for additional columnsbuilder.Entity<Employee>().OrderBy(_ =>_.HireDate).ThenBy(_ =>_.Salary);

This prevents accidentally overwriting ordering configuration and ensures the intended ordering is applied.

Example

publicclassDepartment{publicintId{get;set;}publicstringName{get;set;}="";publicintDisplayOrder{get;set;}publicList<Employee>Employees{get;set;}=[];}publicclassEmployee{publicintId{get;set;}publicintDepartmentId{get;set;}publicDepartmentDepartment{get;set;}=null!;publicstringName{get;set;}="";publicDateTimeHireDate{get;set;}publicintSalary{get;set;}}publicclassAppDbContext:DbContext{protectedoverridevoidOnConfiguring(DbContextOptionsBuilderbuilder){builder.UseSqlServer("connection-string").UseDefaultOrderBy();}protectedoverridevoidOnModelCreating(ModelBuilderbuilder){builder.Entity<Department>().OrderBy(_ =>_.DisplayOrder);builder.Entity<Employee>().OrderBy(_ =>_.HireDate).ThenByDescending(_ =>_.Salary);}publicDbSet<Department>Departments=>Set<Department>();publicDbSet<Employee>Employees=>Set<Employee>();}

^{snippet source | anchor}

Alternative to Verify's OrderEnumerableBy

When using Verify for snapshot testing, a common pattern is to use OrderEnumerableBy to get deterministic ordering of EF entities in snapshots:

// Verify's OrderEnumerableBy sorts entities during snapshot serializationVerifierSettings.OrderEnumerableBy<Employee>(_ =>_.HireDate);VerifierSettings.OrderEnumerableBy<Department>(_ =>_.DisplayOrder);

EntityFramework.OrderBy is an alternative approach. Instead of sorting during serialization, ordering is applied at the database query level. This means queries return deterministic results without needing Verify-specific configuration.

// EntityFramework.OrderBy applies ordering at the query levelbuilder.Entity<Employee>().OrderBy(_ =>_.HireDate);builder.Entity<Department>().OrderBy(_ =>_.DisplayOrder);

Benefits over OrderEnumerableBy:

• Ordering is applied to all queries, not only during snapshot verification
• Automatic database index creation for ordering columns improves query performance
• Ordering configuration lives with the entity model rather than in test setup

Icon

Russian Dolls designed by Edit Pongrácz from The Noun Project