how to add other fields to group by linq

Question

I have to select and group by because I need to get the average and sum of some items, my problem is first of all the group by is based on month(m_date.Month) so inside the group by I don't have access to year anymore, my second problem is what if I want to have other properties from statisticsDaily class in my query? with this query I only have access to grouped by fields, look at below:

 var rslt = await (from d in db.statMonth.Include(f=>f.MasterData).Where(d=>d.m_turbine_id == IPAddress.Parse(id) && d.m_date >= frm)
                          group d by d.m_date.Month into g 
                          select new statisticsDaily
                          {
                              Date = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(g.Key),
                              Production = g.Sum(s => s.m_energy_prod),
                              m_wind_speed = g.Average(s => s.m_wind_speed),
                              Availability = g.Average(s => s.m_availability),              
                          }
             ).OrderBy(s=>s.Date).ToListAsync(); 

my statsticDaily class is :

         public class statisticsDaily
        {
            public string Date { get; set; }
            public Nullable<float> Production { get; set; }
            public Nullable<float> m_wind_speed { get; set; }
            public Nullable<float> Availability { get; set; }
            public string Comments { get; set; }
            public string TurbineId { get; set; }
            public string Countries { get; set; }
            
        }

Answer 1

This is exactly how group by works. If you need to access Year too so you need to group by Month and Year together:

 var rslt = await (from d in db.statMonth.Include(f=>f.MasterData).Where(d=>d.m_turbine_id == IPAddress.Parse(id) && d.m_date >= frm)
                          group d by new { d.m_date.Month, d.m_date.Year} into g 
                          select new statisticsDaily
                          {
                              Year = g.Key.Year// We can access Year now since we grouped by Year as well
                              Date = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(g.Key.Month),
                              Production = g.Sum(s => s.m_energy_prod),
                              m_wind_speed = g.Average(s => s.m_wind_speed),
                              Availability = g.Average(s => s.m_availability),              
                          }
             ).OrderBy(s=>s.Date).ToListAsync(); 

See: Using group by on multiple columns

On the other, You can access to list of other properties as g.ToList() when you group by items. See: LINQ Group By and select collection

Answer 2

Every StatMonth has a property m_Date. Your GroupBy makes groups of StatMonths that have equal value for m_Date.Month.

So the StatMonth with an m_Date in February 2019 will be in the same Group as all StatMonth with an m_Date in Februari 2020 and Februari 2018.

You wrote:

...so inside the group by I don't have access to year anymore,

It depend on what you want to do with the Year.

• Do you want to make groups of StatMonths with m_Date in the same month of the same year? So do you want to make a group of StatMonths of 2019-02, 2019-03, 2019-04, ..., 2020-02, 2020-03, etc?
• Or do you want to make a February group of sub groups: the 2019 sub group, the 2020 sub group, etc. And similarly a March group with 2019 sub group, 2020 sub group.

If you look at the generic method syntax of GroupBy, you'll see the following:

var result = db.StatMonths
    .Where(...)
    .GroupBy(
        // parameter KeySelector: I want to make Groups of StatMonths that have the 
        // same value for this key:
        statMonth => ... // this will be the key,

        // for example: groups with same Year/Month combination of m_Date
        statMonth => new 
        {
           Year = statMonth.m_Date.Year,
           Month = statMonth.m_Date.Month,
        },

        // parameter resultSelector, for every Key, and all StatMonths that have this key
        // make one new object:
        (yearMonthCombination, statMonthsWithThisYearMonthCombination) => new ...
}

So in parameter resultSelector, you get the key, and all items that have this key. So if you need groups with same year/month combination, create a key as above. If you want to make groups with the same month for all years, and subgroups per year:

// parameter keySelector: make groups with same month, for all year:
statMonth => stathMonth.m_Date.Month,

// parameter resultSelector: take the key and all statMonths with this key to make a new
(month, statMonthsWithThisMonth) => new
{
     Month = month,

     SubGroups = statMonthsWithThisMonth.GroupBy(
         // make subgroups with same Year,
         // all statMonths in this group already have the same month
         statMonth => statMonth.m_Date.Year,

         // parameter resultSelector: use the year, and all statMonths of the group
         // with this year
         (year, statMonthisWithThisYear) => new
         {
              Year = year,
              ... // other statMonth
         })
  })

You didn't say what you wanted to do other than in your example, so I don't know whether it is better to make a sequence of groups with same year/month combination or a sequence with same month of sub groups with same year.

IMHO the later makes it more difficult to see what happens without adding much functionality.