Odtnhj

I am using Entity Framework for the following:
Get the latest row where some columns are equal to X and Y. The LINQ looks like this (I have also tried to rewrite it as "FROM WHERE" statement, among other things, to no avail):

var logEvent = Context.Set<Log>().Where(e =>
 e.Id == id && e.MessageType == messageType)
 .OrderByDescending(e => e.DateTime)
 .FirstOrDefault();

I would expect this to generate the following:

SELECT TOP (1) *
FROM [dbo].[Log]
WHERE [Log].[Id] = @p__linq__0
AND [Log].[MessageType] = @p__linq__1
ORDER BY [Log].[DateTime] DESC

Instead it generates an inner Select that just iterates over the whole table, and the pick TOP 1 from the ordered rows.
(I have indexed DateTime and Id)

SELECT TOP (1) *
 FROM ( SELECT *
 FROM [dbo].[Log] AS [Extent1]
 WHERE ([Extent1].[Id] = @p__linq__0)
 AND ([Extent1].[MessageType] = @p__linq__1)
 ) AS [Project1]
 ORDER BY [Project1].[DateTime] DESC
GO

What is causing this inner SELECT?

FirstOrDefault() is the reason of translating LINQ query into SELECT TOP (1) FROM (another select)

@Bagus Tesa directed me to the correct answer. Thank you all.

Doing the OrderBy() FIRST was the solution.
The following LINQ query generates the expected query:

var logEvent = Context.Set<Log>()
.OrderByDescending(e => e.DateTime)
.FirstOrDefault(e => (e.Id == id) && e.MessageType == messageType));

I also tested in case I wanted more than 1 result row:

var importLogEvent1 = Context.Set<ImportLog>()
.OrderByDescending(e => e.DateTime)
.Where(e => (e.Id == id) && e.MessageType == messageType))
.Take(5);

This also generated a "clean" SQL query.

NOTE: As some have pointed out, the generated SQL query even though it looked "bad", might very well have resulted in a equally good execution plan. I did not look into this.