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 :(
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)
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With