Group by LINQ appears to be N+1

Hi, I am having trouble writing a query to select grouped data out of a counter table.  Essentially what I want is

SELECT [Date.Year], [Date.Month] /* etc SNIP */, Counter, COUNT(*) 
FROM [Counter] 
WHERE (/* SNIP */) 
GROUP BY [Date.Year], /* etc */

So, I have the following LINQ

	var result =
	(
		from counter in UnitOfWork.Counters
		where
			counter.Date > args.DateRange.Begin
			&& counter.Date < args.DateRange.End
			&& args.Counters.Contains(counter.CounterType)
		let d = counter.Date
		group counter by new { d.Year, d.Month, d.Day, d.Hour, d.Minute }
		into g
		select new
		{
			Date = new DateTime(g.Key.Year, g.Key.Month, g.Key.Day, g.Key.Hour, g.Key.Minute, 0),
			Count = g.Count()
		}
	).ToArray();

The problem being that this seems to generate a N+1, one to select the grouping keys and then N more queries to fetch the count for that key. If you could point out what I've done wrong, that'd be awesome

I really do want to stay away from doing this as a stored procedure because the Where clause is actually a bit more dynamic than what I've shown here.

Thanks in advance

You've not done anything wrong, but you've hit a limitation on how smart we are able to be in group-by operations.  At present, we are able to fetch the group results in a single go only if the grouping key consists only of raw columns.  If the grouping key involves functions or properties, we cannot do a bulk operation and have to fall back to the N+1 behaviour.  In your case, the grouping key involves extracting the .Year, .Month etc. properties from the Date column, so your query falls afoul of the 'no functions' rule.

I'm not sure if there is an elegant way to do this in LightSpeed while allowing the Where clause to be dynamic.  The best way I can think of would involve fetching the dates back as a list, then doing an IN query with that list, but I'm not sure whether it will be possible to express the necessary IN query in LightSpeed.  You might be able to do the IN query using a stored procedure or a plain old ADO.NET command -- you're not materialising entities so you don't strictly need LightSpeed to mediate that particular query... (and you can use IUnitOfWork.PrepareCommand to enrol a raw ADO.NET command against a UOW's connection and transaction if transactionality is a concern).