Why DefaultIfEmpty() removes Order By in linq to entities

Question

I have been facing issues with wrong item being returned by LINQ query. "Wrong" in this context means sorting not being applied. I then logged the query generated by LINQ to console and found that ORDER BY clause was omitted by the query when using DefaultIfEmpty(0). After removing DefaultIfEmpty(0), the ORDER BY clause could be seen and result was also right.

LINQ with DefaultIfEmpty(0):

decimal gross = db.Salaries.Where(a => a.EmployeeId == dataItem.empid && 
a.ComponentId == comp.id && 
a.StartDate <= period.DateEnd)
.OrderByDescending(a => a.StartDate)
.Select(a => a.Amount)
.DefaultIfEmpty(0)
.FirstOrDefault();

Generated SQL (does not have ORDER BY)

SELECT 
[Limit1].[C1] AS [C1]
FROM ( SELECT 
    CASE WHEN ([Project1].[C1] IS NULL) THEN 0 ELSE [Project1].[Amount] END AS [C1]
    FROM   ( SELECT 1 AS X ) AS [SingleRowTable1]
    LEFT OUTER JOIN  (SELECT 
        [Extent1].[Amount] AS [Amount], 
        1 AS [C1]
        FROM [Salaries] AS [Extent1]
        WHERE (([Extent1].[EmployeeId] = @p__linq__0) AND ([Extent1].[ComponentId] = @p__linq__1)) AND ([Extent1].[StartDate] <= @p__linq__2) ) AS [Project1] ON 1 = 1 LIMIT 1
)  AS [Limit1]
-- p__linq__0: '3' (Type = Int32, IsNullable = false)
-- p__linq__1: '1' (Type = Int32, IsNullable = false)
-- p__linq__2: '30-06-2020 00:00:00' (Type = DateTime, IsNullable = false)

LINQ without DefaultIfEmpty(0):

decimal gross = db.Salaries.Where(a => a.EmployeeId == dataItem.empid && 
a.ComponentId == comp.id && 
a.StartDate <= period.DateEnd)
.OrderByDescending(a => a.StartDate)
.Select(a => a.Amount)
.FirstOrDefault();

Generated SQL (with ORDER BY):

SELECT 
[Project1].[Amount] AS [Amount]
FROM ( SELECT 
    [Extent1].[StartDate] AS [StartDate], 
    [Extent1].[Amount] AS [Amount]
    FROM [Salaries] AS [Extent1]
    WHERE (([Extent1].[EmployeeId] = @p__linq__0) AND ([Extent1].[ComponentId] = @p__linq__1)) AND ([Extent1].[StartDate] <= @p__linq__2)
)  AS [Project1]
ORDER BY [Project1].[StartDate] DESC LIMIT 1
-- p__linq__0: '3' (Type = Int32, IsNullable = false)
-- p__linq__1: '1' (Type = Int32, IsNullable = false)
-- p__linq__2: '30-06-2020 00:00:00' (Type = DateTime, IsNullable = false)

Am I applying DefaultIfEmpty incorrectly?

The underlying database is SQLite and this is a WPF project.

Why DefaultIfEmpty() removes Order By in linq to entities

0 Answers