LINQ syntax help: projection and grouping

Question

New to LINQ.. I am curious as to the syntax to do the following SQL query in LINQ

SELECT MAX(TMPS), DAY FROM WEATHERREADINGS
GROUP BY WEATHERREADINGS.DAY

What I have so far:

var minTemps = from ps in ww.WEATHERREADINGS
               group ps by ps.DATE.Hour into psByHour
               select new
               {
                   HourOfDay = psByHour.Max().DATE.Hour,
                   MaxTemp = psByHour.Max().TMPS
               };

I am getting the following error while doing this:

Exception Details: System.InvalidOperationException: Could not format node 'New' for execution as SQL.

any help greatly appreciated!!

Answer 1

I think the following is what you want. Note that you can get the key from the grouping so there is no need to aggregate there. You need to provide a mechanism to select the item to do the aggregation on for the other.

var maxTemps = from ps in ww.WEATHERREADINGS
               group ps by ps.Date.Hour into psByHour
               select new
               {
                    HourOfDay = psByHour.Key,
                    MaxTemp = psByHour.Max( p => p.TMPS )
               };

Answer 2

Or the functional approach that i tend to like better:

var result = ww.WEATHERREADINGS
                .GroupBy(a => a.Date.Hour)
                .Select(a => new
                       {
                         Hour = a.Key,
                         Max = a.Max(b => b.TMPS)
                       });