Get the latest Value of table via EntityFramework

Question

I have a table that have several field and each of them update separately by separate ViewModel , Now I wanna to get the latest Value of a specific field (maybe it has updated in fifth record of my table) , OK? now what I have write is look like this :

  public ViewResult ShowPiece()
        {
            var context = new SiteContext();
            var showPiece = context.Portraits.LastOrDefault();
            return View(showPiece);
        }

but when I run the application and navigate above action , I got thie Error :

LINQ to Entities does not recognize the method , and this method cannot be translated into a store expression...

what is the problem with that ??

Answer 1

Use descending ordering (by date, or id) and FirstOrDefault which is supported:

var showPiece = context.Portraits
                       .OrderByDescending(p => p.Date)
                       .FirstOrDefault();

---

Another option, is select portrait which has max date (id) via subquery (as Evelie suggested in comments):

var showPiece = context.Portraits
              .FirstOrDefault(p => p.Date == context.Portraits.Max(x => x.Date));

I made a little investigation here. In first case (ordering) following query is generated:

SELECT TOP (1) [t0].*
FROM [Portraits] AS [t0]
ORDER BY [t0].[Date] DESC

In second case (getting max):

SELECT TOP (1) [t0].*
FROM [Portraits] AS [t0]
WHERE [t0].[Date] = ((
    SELECT MAX([t1].[Date])
    FROM [Portraits] AS [t1]
    ))

Execution plan is almost same, but in second case Top is executed twice. Thus Top costs 0% comparing to Index Scan, this should not be a problem.