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 ??

Sergey Berezovskiy · Answer 1 · 2013-02-20T14:58:39.087

59

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.

edited Feb 20 '13 at 14:58

answered Feb 20 '13 at 13:27

Sergey Berezovskiy

232,247
41
429
459

Out of curiosity.. Will OrderByDescending and then FirstOrDefault actually pick the first without ordering or would for example Max on date be better for performance where he would just grab the highest date? – Evelie Feb 20 '13 at 13:31
@Evelie actually you cannot just select Max, because whole record is needed – Sergey Berezovskiy Feb 20 '13 at 13:54
FirstOrDefault(c=>c.Date == context.Portraits.Max(d=>d.Date)) would not order it and work. But my point wasnt to provide a better solution to the question.. just wanted to know if your solution actually sorts the entries or not – Evelie Feb 20 '13 at 14:24

score 8 · Answer 2 · answered Mar 06 '17 at 19:54

8

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.

answered Mar 06 '17 at 19:54

Tiago Ávila

2,737
1
31
34

spajce · Answer 3 · 2013-02-20T14:13:23.003

0

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

edited Feb 20 '13 at 14:13

answered Feb 20 '13 at 13:48

spajce

7,044
5
29
44

This will select single field instead of whole entity – Sergey Berezovskiy Feb 20 '13 at 13:51
This answer doesn't add anything new. No need to check if Date is nullable or not; EF will check for it nulls via meta-model; The Select method is not needed; Essentially it is the same as Sergey's answer – an phu Nov 09 '15 at 20:49

score -3 · Answer 4 · answered Nov 02 '17 at 14:42

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);

Get the latest Value of table via EntityFramework

4 Answers4

Linked