Linq - Get Max date from resultset

Question

I need to convert the following SQL query to Linq :-

 SELECT CODE,SCODE,MAX(SDATE) AS SDATE FROM SHIFTSCHEDULE 
 WHERE COMPANY = 'ABC' 
 GROUP BY CODE,SCODE 
 ORDER BY MAX(SDATE) 
 DESC

So far, I have tried this :-

var data = ctx.ShiftSchedule.Where(m =>
                                    m.Company == company && m.EmployeeId == item.EmployeeId
                                )
                                .GroupBy(m =>
                                    new
                                    {
                                        m.EmployeeId,
                                        m.ShiftId
                                    })
                                .Select(m =>
                                new
                                {
                                    EmployeeId = m.Key.EmployeeId,
                                    ShiftCode = m.Key.ShiftId,
                                    ShiftDate = m.Max(gg => gg.ShiftDate)
                                }).ToList();

The results i get are :-

Now what i want is to get record or item in this result set which is MaxDate. In the above image the MaxDate is 1st record.

How to get the MAXDATE from the resultset?

Answer 1

This should work:-

var data = ctx.ShiftSchedule.Where(x => x.Company == company 
                                        && x.EmployeeId == item.EmployeeId)
                            .GroupBy(x => new { x.CODE, x.SCODE })
                            .Select(x => new 
                                        {
                                            CODE = x.Key.CODE,
                                            SCODE = x.Key.SCODE,
                                            SDATE  = x.Max(z => z.SDATE) 
                                        })
                            .OrderByDescending(x => x.SDATE).FirstOrDefault();

You can order the resulting collection and fetch the first object using FirstOrDefault.

If you want just MAXDATE, you can only project that.

Answer 2

Just add .OrderByDescending(x => x.ShiftDate).First(); at the end.

OrderByDescending date and then take .First()

 var data = ctx.ShiftSchedule.Where(m =>
                                        m.Company == company && m.EmployeeId == item.EmployeeId
                                    )
                                    .GroupBy(m =>
                                        new
                                        {
                                            m.EmployeeId,
                                            m.ShiftId
                                        })
                                    .Select(m =>
                                    new
                                    {
                                        EmployeeId = m.Key.EmployeeId,
                                        ShiftCode = m.Key.ShiftId,
                                        ShiftDate = m.Max(gg => gg.ShiftDate)
                                    }).ToList().OrderByDescending(x => x.ShiftDate).First();