How do I reproduce the following query using ef core, or in LINQ?
SELECT ob.PK_OBJETIVO,
ev.NM_EVENTO,
ifi.QT_NOTA_IMPACTO,
imi.QT_NOTA_IMPACTO,
ire.QT_NOTA_IMPACTO,
(ifi.QT_NOTA_IMPACTO + imi.QT_NOTA_IMPACTO + ire.QT_NOTA_IMPACTO)/3 AS
Media
FROM AVALIACAO_IMPACTO AS ai
INNER JOIN EVENTO AS EV ON ev.PK_EVENTO = ai.FK_AVALIACAO_IMPACTO_EVENTO
INNER JOIN OBJETIVO AS ob ON ob.PK_OBJETIVO =
ai.FK_AVALIACAO_IMPACTO_OBJETIVO
INNER JOIN IMPACTO_FINANCEIRO AS ifi ON ifi.PK_IMPACTO_FINANCEIRO =
ai.FK_AVALIACAO_IMPACTO_IMPACTO1
INNER JOIN IMPACTO_MISSAO AS imi ON IMI.PK_IMPACTO_MISSAO =
AI.FK_AVALIACAO_IMPACTO_IMPACTO2
INNER JOIN IMPACTO_REPUTACAO AS IRE ON IRE.PK_IMPACTO_REPUTACAO =
AI.FK_AVALIACAO_IMPACTO_IMPACTO3
WHERE ai.FK_AVALIACAO_IMPACTO_OBJETIVO IN
(SELECT OBJ.PK_OBJETIVO
FROM OBJETIVO AS OBJ
WHERE OBJ.FK_OBJETIVO_PROCESSO = 3)
I made the following attempt:
var queryImpactos = await _context.AvaliacaoImpacto
.Where(e => _context.Objetivo.Select(o =>
o.ProcessoID).Contains(planoRiscos.Auditoria.ProcessoID))
.Include(e => e.Evento).Include(e => e.Objetivo)
.Include(e => e.ImpactoFinanceiro).Include(e =>
e.ImpactoMissao).Include(e => e.ImpactoReputacao).ToListAsync();
planRiscos.Auditoria.ProcessoID returns the id I need, it would be the number 3 of the pure SQL query, but the result of the query linq is returning all records of the evaluation_impact table, however I only need the records where the fk_objective in impact_valuation exists within the target table where the fk_process in objective is equal to the passed parameter (planRiscos.Auditoria.ProcessoID).