5

Some LINQ queries still puzzle me.

for a table 'Hits' containing two columns, 'Page' and 'Date', I want to find the most Pages with the most rows in a defined slice of time.

In SQL I would use this:

SELECT TOP 10
      [Page]
      ,COUNT([Page]) as Number
FROM dbo.[Hits]
WHERE [Date] >= CONVERT(datetime,'14 Jan 2009')
AND [Date] < CONVERT(datetime,'15 Jan 2009')
Group BY [Page]
Order by Number DESC

In LINQ I got no idea how to approach this, can anyone help me here? I tried to convert it using linqer, but it just shows an error for this expression.

Sam
  • 28,421
  • 49
  • 167
  • 247

2 Answers2

7

Something like this should work:

(from p in DataContext.Hits
where (p.Date >= minDate) && (p.Date < maxDate)
group p by p.Page into g
select new { Page = g.Key, Number = g.Count() }).OrderByDescending(x => x.Number).Take(10);
veggerby
  • 8,940
  • 2
  • 34
  • 43
  • Typo in p < maxDate -> p.Date < maxDate :) – Sam Mar 03 '09 at 13:34
  • Oh, and, of course, if your linq code is used in c# one would have to use '&&' instead of 'and'. Dunno how it would need to be in VB. – Sam Mar 03 '09 at 14:40
7
var top10hits = objectContext.Hits
  .Where(h => minDate <= h.Date && h.Date < maxDate)
  .GroupBy(h => h.Page)
  .Select(g => new { Page = g.Key, Number = g.Count() })
  .OrderByDescending(x => x.Number)
  .Take(10);
yfeldblum
  • 65,165
  • 12
  • 129
  • 169
Amy B
  • 108,202
  • 21
  • 135
  • 185
  • Typo in .Select(g => Page = g.Key, Number = g.Count()}) --> .Select(g => new {Page = g.Key, Number = g.Count()}) Otherwise "same same.. but different" :) – veggerby Mar 03 '09 at 13:14
  • Thanks for the typo notice - veggerby; and the edit - Justice. – Amy B Mar 03 '09 at 14:37
  • Also - @"same same... but different" I find the extension methods are more discoverable than the query comprehension syntax. So that's what I use. – Amy B Mar 03 '09 at 14:38