select IN using Entity query

Asked

Viewed 50 times

0

How do I play 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 next 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();

planoRiscos.Auditoria.Processoid returns the id I need, it would be the number 3 of the pure SQL query, but the result of the Linq query is returning all the records of the evaluac_impact table, however I only need records where the fk_objective in evaluac_impact exists within the objective table where the objective fk_process is equal to the parameter passed (planRiscos.Auditoria.Processoid).

  • Hello! Welcome to Sopt! You could show us some code you’ve tried?

  • 1

    Thank you, I edited the question and posted the code I tried

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.