Linq2Sql, GroupBy and return specific object

Question

Hi!

I'm have a list of keywords;

• pizza (10:13 PM)
• milk (10:12 PM)
• pizza (10:11 PM)
• pizza (10:10 PM)
• milk (10:09 PM)
• beans (10:08 PM)
• corn (10:07 PM)
• juice (10:06 PM)
• foo (10:05 PM)

I want to group them like this (top 5, notice foo is not in the list):

• Pizza (3) (10:13 PM)
• Milk (2) (10:12 PM)
• Beans (1) (10:08 PM)
• Corn (1) (10:07 PM)
• Juice (1) (10:06 PM)

So basically: list keywords after date created, grouped by keyword and .Take(5). And how is this possible using lambda expressions? And how to return them as object of a specific type? Let's say type LatestSearch.

UPDATE Ended up with this query:

var searches = db.Searches
             .GroupBy(s => s.Keyword)
             .Select(g => g.OrderByDescending(o => o.CreatedDate).First())
             .OrderByDescending(o => o.CreatedDate)
             .Take(5);

Answer 1

Like this:

table.Where(s => whatever)
     .GroupBy(s => s.Keyword)
     .Select(g => new { 
         Keyword = g.OrderByDescending(s => s.Date),
         Count = g.Count(),
         Date = g.Max(s => s.Date())
      })
     .OrderByDescending(s => s.Count)
     .Take(5);

EDIT: If you only want an IEnumerable<LatestSearch>, write the following:

table.Where(s => whatever)
     .GroupBy(s => s.Keyword)
     .OrderByDescending(g => g.Count())
     .Select(g => g.First());
     .Take(5);