Select all distinct last records of column1 and order by column2 in Linq (Not Working Accordingly)

So I have a table like this:

Now I want distinct ShortCode order by the ID descending. In other words, the distinct last records. Like this:

So I tried GroupBy like:

var data = db.ShortCodes.GroupBy(x => x.ShortCode).Select(x => x.FirstOrDefault()).OrderByDescending(s=> s.ID);

This gave me distinct records but not the last ones, nor ordered by ID descending:

Now I also tried like suggested here

var data = db.ShortCodeManager
               .GroupBy(s => s. ShortCode)
               .Select(g => g.First())
               .OrderByDescending(s => s.ID);

This gave me the error The method 'First' can only be used as a final query operation. Consider using the method 'FirstOrDefault' in this instance instead.

So I modified to FirstOrDefault() like:

var data = db.ShortCodeManager
               .GroupBy(s => s. ShortCode)
               .Select(g => g.FirstOrDefault())
               .OrderByDescending(s => s.ID);

This also gave me distinct records but not the last records:

So finally I tried like suggested here:

var data = db.ShortCodeManager.Where(a => a.ID > 0).GroupBy(x => x.ShortCode).OrderByDescending(grp => grp.Max(g => g.ID)).Select(a => a.FirstOrDefault());

Again, this gave me distinct records but not the last ones, nor ordered by ID descending:

So how am I to write the query to get the result I want in Linq? Also note, I need more of the distinct last records than ordering by ID descending. If anyone also knows how to write it in raw SQL it might be useful as well.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

This LINQ query should work for your case:

var result = db.ShortCodeManager
    .GroupBy(x => x.ShortCode)
    .Select(gr => new { Id = gr.Max(g => g.Id), ShortCode = gr.Key})
    .ToList();

EDIT:

Based on your comment it looks like you need to cast anonymous object result to ShortCodeManagerModel type and then pass it to your view. So, somethin like this:

var result = db.ShortCodeManager
    .GroupBy(x => x.ShortCode)
    .Select(gr => new { Id = gr.Max(g => g.Id), ShortCode = gr.Key})
    .ToList();

var model = result 
    .Select(x => new ShortCodeManagerModel { Id = x.Id, ShortCode = x.ShortCode })
    .ToList();

And then pass model to you view.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0