How to do this query in LINQ

Hello everyone!

I having a problem with doing the query in LINQ. I use Firebird and my query is:

SELECT (R.DATA_TERMINO + M.INTERVALO_MANUTENCAO_PREVENTIVA) AS PROXIMA_MANUTENCAO,
R.DATA_TERMINO,
M.STATUS AS SITUACAO,
R.NUMERO_RM,
M.MODELO,
M.FABRICANTE,
MT.CODMAT,
MT.DESCRICAO AS MATERIAL,
F.DESCRICAO AS FAMILIA_MATERIAL,
S.DESCRICAO AS SERVICO,
S.CODIGO_CENTRO_CUSTO,
FU.NOME AS REQUISITANTE,
CASE R.POSICAO_SETOR_RESPONSAVEL
WHEN 0 THEN 'Em Manutenção'
WHEN 1 THEN 'Aguardando Peça'
WHEN 2 THEN 'Aguardando Avaliação'
WHEN 3 THEN 'Não Autorizada'
WHEN 4 THEN 'Concluída'
WHEN 5 THEN 'Aguardando Liberação do Setor Solicitante'
END AS POSICAO_RM
FROM MANUFATURA M
LEFT OUTER JOIN RM R ON
M.ID_MATERIAL = R.ID_EQUIPAMENTO
LEFT OUTER JOIN MATERIAL MT ON
M.ID_MATERIAL = MT.CODIGO
LEFT OUTER JOIN FAMILIA_MATERIAL F ON
MT.CODIGO_FAMILIA_MATERIAL = F.CODIGO
LEFT OUTER JOIN SERVICOS S ON
M.ID_SETOR = S.CODIGO
LEFT OUTER JOIN FUNCIONARIO FU ON
R.ID_REQUISITANTE = FU.CODIGO
WHERE
M.intervalo_manutencao_preventiva > 0
AND R.PERIODICA = 'S'
AND r.id_setor_requisitado = 39
AND R.DATA_INICIO =
(SELECT
MAX(xR.DATA_INICIO)
FROM
RM xR
WHERE
xR.ID_EQUIPAMENTO = M.ID_MATERIAL
AND xR.TIPO = 2
AND xR.PERIODICA = 'S'
)
AND (SELECT FIRST 1 yR.SITUACAO FROM RM yR WHERE yR.ID_EQUIPAMENTO = M.ID_MATERIAL ORDER BY yR.DATA_TERMINO DESC) IN (1,2,5)
ORDER BY S.DESCRICAO, 1

How can I do this? Mainly when comparing date fields within a subselect.

Tks.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Gaurav Raj8y ago
Accepted answer
u can also convert this query into store procedure then you can call with linq...........
Thales Junqueira8y ago
Thank you all! I'll try to do it through a procedure.
Jignesh Kumar8y ago
Could you please provide table script for this that will help or as Jaish indicated that way you can try tools to generate script ?
Jaish Mathews8y ago
Why can't try with a SQL -> LINQ converter? e.g.
http://www.sqltolinq.com/
https://www.linqpad.net/