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

user_13336072 0
Viewed 67

So I have a table like this:

enter image description here

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

enter image description here

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:

enter image description here

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:

enter image description here

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:

enter image description here

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.

asp.net sql linq database c#
1 Answers

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.

user_29257940
Related