How to get the latest row in a table using Entity Framework (considering performance)?

Question

I am concerning about performance, specially on large table. In my example StatusId is a IDENTIY PRIMARY KEY.

I would like to know if there is a better way (faster) to get the last entry in a table.

  public IQueryable<GaStatus> GetLastStatus()
    {
        return context.GaStatuses.OrderByDescending(x => x.StatusId).Take(1);
    }

Solution

The Entity Framework generates the following SQL for me:

SELECT TOP (1) 
[Extent1].[StatusId] AS [StatusId]
FROM [dbo].[GaStatuses] AS [Extent1]
ORDER BY [Extent1].[StatusId] DESC

This looks like a reasonable way to get the desired result, although 'Extent1' is not easy to read. See for example How to read the last row with SQL Server.

Therefore, I don't believe there is a better or faster way to do this in Entity Framework.

But there is almost always a way to improve the performance of a given SQL query. For example, looking at the execution plan in my particular database, the ORDER BY does a clustered index scan. This might or might not be good from a performance point of view. So, depending on your database, you might be able to identify performance problems and improve performance by writing the SQL directly rather than having Entity Framework generate it for you.

Comments

[AcidJunkie]

@ken2k Having a look at the actual SQL Execution Plan says otherwise. The main load comes from the index scan.