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

Try something like this:

var lastShowPieceId = context.Portraits.Max(x => x.Id);
return context.Portraits.FirstOrDefault(x => x.Id == lastShowPieceId);

I had that situation and this worked for me.

Answer 2

I've tried every of the replies that you've here.

But no one had really worked.

My solution was:

        List<Portraits> portraitsList = db.Portraits.ToList();
        int idPortraits = portraitsList.Last().PortraitsId;
        portratisList.Remove(portratisList.Last());

        Portraits p = db.Mementos.Find(idPortraits);

Answer 3

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.

Answer 4

var s = con.Portraits.Where(j => j.Date.HasValue)
                  .OrderByDescending(a => a.Date)
                  .Select(p => p).FirstOrDefault();