Toolroom Tech Blog

Devlopers Digest

OrderBy vs. ThenOrderBy

Few days ago I corrected a bug where the developer didn't use linq ordering properly, the result was very disarranged list content. So if you've ever wondered why Linq proivides us with OrderBy AND ThenOrderBy, you should read this.

Few days ago I corrected a bug where the developer didn't use linq ordering properly, the result was very disarranged list content. So if you've ever wondered why Linq proivides us with OrderBy AND ThenOrderBy, you should read this.

Imaginary sample project

Our little project uses the Entity Framework to display a simpe task list that keeps track of started and already finished tasks. In our list we want to show all started tasks before all others.

The table 'Task' owns the following columns:

  1. Id: int
  2. Title: nvarchar(50)
  3. Description: nvarchar(1000)
  4. Start: datetime2
  5. End: datetime2

OrderBy

Idea 1: Let's try it with OrderBy

Entities.Tasks.OrderByDescending(l => l.End == null)

Ok, active items are on top ... but all other tasks are also ordered by the [End]. Let's take a look on the generated sql statement:

SELECT 
[Task1].[Id] AS [Id], 
[Task1].[Title] AS [Title], 
[Task1].[Description] AS [Description], 
[Task1].[Start] AS [Start], 
[Task1].[End] AS [End]
FROM ( SELECT 
	CASE WHEN ([Extent1].[End] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C1], 
	[Extent1].[Id] AS [Id], 
	[Extent1].[Title] AS [Title], 
	[Extent1].[Description] AS [Description], 
	[Extent1].[Start] AS [Start], 
	[Extent1].[End] AS [End]
	FROM [dbo].[Task] AS [Extent1]
)  AS [Task1]
ORDER BY [Task1].[C1] DESC

Idea 2: The ultimate solution: add a second orderby!

Entities.Tasks.OrderByDescending(l => l.End == null).OrderByDescending(l => l.Start)

But the result is frustrating because of Linqtelligence: The first order is completely ignored, since we would order by [End] and then just reorder by [Start].

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[DisplayName] AS [DisplayName], 
[Extent1].[ProjectId] AS [ProjectId], 
[Extent1].[ActivityId] AS [ActivityId], 
[Extent1].[Start] AS [Start], 
[Extent1].[End] AS [End], 
[Extent1].[Fee] AS [Fee], 
[Extent1].[BillableQuota] AS [BillableQuota], 
[Extent1].[Notes] AS [Notes], 
[Extent1].[UserId] AS [UserId], 
[Extent1].[DBInsert] AS [DBInsert], 
[Extent1].[DBUpdate] AS [DBUpdate], 
[Extent1].[DBState] AS [DBState]
FROM [dbo].[LogItem] AS [Extent1]
ORDER BY [Extent1].[Start] DESC

Idea 3: Adding a second orderby does not make sense, since it would just be a secondary order. Let's try intersect, to get select the active tasks first and merge it with the finished afterwards.

Entities.Tasks.Where(l => l.End == null).OrderByDescending(l => l.End == null).Concat(Entities.Tasks.Where(l => l.End != null).OrderByDescending(l => l.Start));

Active items are again on top, but nothing is ordered by [Start]. So what happened? Linq has thrown all ordering and instead built a pretty union statement.

SELECT 
[UnionAll1].[Id] AS [C1], 
[UnionAll1].[Title] AS [C2], 
[UnionAll1].[Description] AS [C3], 
[UnionAll1].[Start] AS [C5], 
[UnionAll1].[End] AS [C6]
FROM  (SELECT 
	[Extent1].[Id] AS [Id], 
	[Extent1].[Title] AS [Title], 
	[Extent1].[Description] AS [Description], 
	[Extent1].[Start] AS [Start], 
	[Extent1].[End] AS [End]
	FROM [dbo].[Task] AS [Extent1]
	WHERE [Extent1].[End] IS NULL
UNION ALL
	SELECT 
	[Extent2].[Id] AS [Id], 
	[Extent2].[Title] AS [Title], 
	[Extent2].[Description] AS [Description], 
	[Extent2].[Start] AS [Start], 
	[Extent2].[End] AS [End]
	FROM [dbo].[Task] AS [Extent2]
	WHERE [Extent2].[End] IS NOT NULL) AS [UnionAll1]

 

ThenOrderBy

ThenOrderBy solves the issue very lovely.

SELECT 
[Task1].[Id] AS [Id], 
[Task1].[Title] AS [Title], 
[Task1].[Description] AS [Description],
[Task1].[Start] AS [Start], 
[Task1].[End] AS [End]
FROM ( SELECT 
	CASE WHEN ([Extent1].[End] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C1], 
	[Extent1].[Id] AS [Id], 
	[Extent1].[Title] AS [Title], 
	[Extent1].[Description] AS [Description], 
	[Extent1].[Start] AS [Start], 
	[Extent1].[End] AS [End]
	FROM [dbo].[Task] AS [Extent1]
)  AS [Task1]
ORDER BY [Task1].[C1] DESC, [Task1].[Start] DESC
Loading