CODEWITHSUNDEEP
c#sql.netlinqentity-framework-6
Aleksey Kontsevich
Aleksey Kontsevich

Reputation: 5001

Return record with max column value using Entity Framework 6

Trying to get record with max datetime value for ReceivedDateTime column, however data set should be pre-filtered by some Id column (not unique). Solved this way:

using (var db = new SystemEntities())
{
    var records = db.Table.Where(p => p.Id == Id);
    var record = records.Where(p => p.ReceivedDateTime == records.Max(r => r.ReceivedDateTime)).FirstOrDefault();
    if(record != null)
    {

    }
}

Is there more beautiful, simpler and shorter implementation, notation? Thanks!

Upvotes: 7

Views: 13642

Answers (2)

Fabulous
Fabulous

Reputation: 2423

Update

Between the time I opened the question and tried out a result, the original answer had been fixed. My apologies for not checking before I posted.

Update

Seeing as I cannot comment, I will post an answer on the side.

The above suggested var record = db.Table.Where(p => p.Id == Id).Max(x => x.ReceivedDateTime).FirstOrDefault() will not compile because Max will return for you a datetime.

You can do it using OrderByDescending they way you would in an SQL statement

// I used an in memory array but it should be the same.
var item = items.Where(x => x.Id == 2).OrderByDescending(x => x.ReceivedDate).FirstOrDefault();

Upvotes: 2

OmG
OmG

Reputation: 18838

You can simplify like the following using OrderByDescending:

using (var db = new SystemEntities())
{
    var record = db.Table.Where(p => p.Id == Id).OrderByDescending(x => x.ReceivedDateTime).FirstOrDefault();
    if(record != null){}
}

Upvotes: 10

Related Questions