Grouping in LINQ with multiple entity records

Question

I have a LINQ query that averages a list of Notes grouped by risk sources.

 var mediaControles = await (from ac in _context.AvaliacaoControle
 join c in _context.Controle on ac.ControleID equals c.ControleID
 join nc in _context.NotaControle on ac.NotaControleID equals 
 nc.NotaControleID
 group ac by ac.FonteDeRiscoID into groupDate
 select new EventoImpactoVM()
 {
 fonteID = groupDate.Key,
 NotaControle = groupDate.Average(p => p.NotaControle.Nota)
 }).ToListAsync();

The query below retrieves a list of risk sources grouped by Risk Event and with the calculated average of the probability field (error location) Calculates the mean of the field Note of the table Notacontrol of the previous query.

var avgProb = await (from ms in _context.AvaliacaoFonte
join prob in _context.Probabilidade on ms.ProbabilidadeID equals 
prob.ProbabilidadeID

//join da consulta anterior
join m_c in mediaControles on ms.FonteDeRiscoID equals m_c.fonteID
group new { m_c, ms } by ms into groupDate

select new EventoImpactoVM()
{                                         
eventoID = groupDate.Key.EventoID,

//calculation of the average of the probability notes, does not work! returns an error

lambda_method(Closure , <>f__AnonymousType4 )

NotaProbabilidade = groupDate.Average(a 
=> a.ms.Probabilidade.NotaProbabilidade),

//cálculo da média da média
MediaControle = groupDate.Average(a => a.m_c.NotaControle)
}).ToListAsync(); 

From what I understand the error in question points fault in the instantiation of the anonymous variable ms, I have tried several alternatives but can not solve, I believe the problem is in the group new {m_c, ms}. When I group only one of the m_c or ms variables the error does not occur, when I group the two, only m_c returns the result. I thank all who can help.

Answer 1

Try grouping only by the specific fields (the fields that will be used in your clause select) instead of trying to group by the whole entity. Do the following:

group new 
{
    ms.EventoID, 
    ms.Probabilidade.NotaProbabilidade,
    m_c.NotaControle
} by ms into groupDate