How to get a group ordered by the count column

Question

It is hard to ask the question in plain english so I'll show what I'm trying to do.

Here's my SQL code:

select top 100 [Name], COUNT([Name]) as total from ActivityLog  
where [Timestamp] between '2010-10-28' and '2010-10-29 17:00'  
group by [Name]  
order by total desc  

I need to write that in LinQ. So far I have the following:

var groups = from ActivityLog log in ctx.ActivityLog
 where log.Timestamp > dateFrom
 where log.Timestamp <= dateTo
 group log by log.Name;

but I don't have the COUNT(*) column to sort from :(

Answer 1

I'm afraid I am far more comfortable with the fluent syntax (as opposed to query syntax), but here is one possible LINQ answer:

 ctx.ActivityLog
   .Where(x => x.TimeStamp > dateFrom && x.TimeStamp <= dateTo)
  .GroupBy(x => x.Name)
  .Select(x => new { Name = x.Key, Total = x.Count() })
  .OrderByDescending(x => x.Total)
  .Take(100)

EDIT:

Alright, I stepped out of my comfort zone and came up with a query syntax version, just don't expect too much. I warned you about my abilities above:

(from y in (
    from x in (
        from log in ActivityLog
        where log.Timestamp > dateFrom
        where log.Timestamp <= dateTo
        group log by log.Name)
    select new { Name = x.Key, Total = x.Count() })
orderby y.Total descending
select new { Name = y.Name, Total = y.Total }).Take(100)

Answer 2

diceguyd30's answer technically is LINQ and is correct. In fact, the query syntax gets translated to those Queryable/Enumerable methods by the compiler. That said what's missing is using the group ... by ... into syntax. The equivalent query should be close to this:

var query = from log in ctx.ActivityLog
            where log.TimeStamp > dateFrom && log.TimeStamp <= dateTo
            group log by log.Name into grouping
            orderby grouping.Count() descending
            select new { Name = grouping.Key, Total = grouping.Count() };

var result = query.Take(100);

Note that in C# the Take(100) method has no equivalent in query syntax so you must use the extension method. VB.NET, on the other hand, does support Take and Skip in query syntax.