convert SQL to LINQ query

scropio gurl

10y59.3kLINQ

i want to convert this query into LINQ

Select D.ID as ID, D.OName,
(Select Count(*) from tblve WHERE MID = D.ID and Vme <> ''),
D.Mil,D.Speed
from tblRe M
inner join tblReg D
On M.RID = D.RID

main problem is subquery

9 Replies

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

Jaman Savar5y ago
Select * from order
+1
Francisco Jose5y ago
declare @usuario int = 0;
declare @fase int = -1;
declare @grupoTrabalho int = 0;
declare @dataInicio datetime = '2021-06-01 00:00:00';
declare @dataFim datetime = '2021-06-30 23:59:59';
set dateformat ymd
print @dataInicio
print @dataFim
SELECT convert(varchar(10),TEMP.Data, 103),
TEMP.Usuário,
TEMP.Fase,
TEMP.Grupo,
SUM(TEMP.QTD_DOCS) 'Total de Imagens',
SUM(TEMP.QTD_IMGS) 'Total de Imagens',
SUM(TEMP.Horas_Trab) AS 'Horas_Trab'
FROM
(
SELECT convert(varchar(10),h.data_hora_fim, 103) as 'Data',
U.nome as 'Usuário',
h.fase 'Fase',
g.nome 'Grupo',
SUM(H.qnt_docs) AS 'QTD_DOCS',
SUM(H.qnt_imgs) AS 'QTD_IMGS',
SUM(DATEDIFF(MINUTE, h.data_hora_fim, data_hora_inicio)/60) AS 'Horas_Trab'
FROM SCAN_LOTE L
join SCAN_LOTE_HISTORICO H ON H.lote_id=L.id
join USUARIO U on H.[usuario_id]=U.id
join GRUPO G on l.grupo_trabalho_id = G.id
where
(h.data_hora_inicio BETWEEN @dataInicio and @datafim)
and (h.data_hora_fim BETWEEN @dataInicio and @dataFim)
and
(H.usuario_id = @usuario or @usuario = 0)
and (L.fase = @fase or @fase = -1)
and (L.grupo_trabalho_id = @grupoTrabalho or @grupoTrabalho = 0)
AND
U.USUARIO_EXTERNO < 2 AND U.EXCLUIDO = 0
and H.usuario_id = u.id
AND H.FINALIZADO = 1
AND L.EXCLUIDO = 0
GROUP BY h.data_hora_fim, u.nome, h.fase, g.nome
UNION
SELECT 'Total',
'-',
'-',
'-',
SUM(H.qnt_docs) 'Total de Documentos',
SUM(H.qnt_imgs) 'Total de Imagens',
SUM(DATEDIFF(MINUTE, h.data_hora_fim, data_hora_inicio)/60) AS 'Horas_Trab'
FROM SCAN_LOTE L
join SCAN_LOTE_HISTORICO H ON H.lote_id=L.id
join USUARIO U on H.[usuario_id]=U.id
where
(h.data_hora_inicio BETWEEN @dataInicio and @dataFim)
and (h.data_hora_fim BETWEEN @dataInicio and @dataFim)
and
(H.usuario_id = @usuario or @usuario = 0)
and (L.fase = @fase or @fase = -1)
and (L.grupo_trabalho_id = @grupoTrabalho or @grupoTrabalho = 0)
AND
U.USUARIO_EXTERNO < 2 AND U.EXCLUIDO = 0
and H.usuario_id = u.id
AND H.FINALIZADO = 1
AND L.EXCLUIDO = 0
) TEMP
GROUP BY TEMP.QTD_IMGS, TEMP.Data, TEMP.Usuário, TEMP.Fase, TEMP.Grupo
ORDER BY TEMP.Data, TEMP.Usuário, TEMP.Fase
+1
Satya Karki5y ago
Hi, to convert SQL query to LINQ you can try below site.
http://www.sqltolinq.com/
+2
Wellington Jesus5y ago
Select Distinct
NoTanqueMedidor
From Tanque
Where Tanque.NoTanqueMedidor <> 0
And Tanque.IdPosto = @IdPosto
And DatePart(hh,GetDate()) >= @NoHora
And NoTanqueMedidor Not In (Select Distinct
NoTanque
From MedicaoDeTanque
Where MedicaoDeTanque.IdPosto = @IdPosto
And Convert(Date,DtMedicaoDeTanque) = Convert(Date,GetDate())
And DatePart(hh,DtMedicaoDeTanque) >= @NoHora)
Order By Tanque.NoTanqueMedidor
+1
Dhara Patel6y ago
var Data = tblRe.Join(
                                    tblReg,
                                    M => M.RID,
                                    D => D.RID,
                                    (M, D) => new
                                       {
                                          ID = D.ID,
                                          Name=D.OName,
                                          Total=tblve.Count(r=>r.MID==D.ID && Vme!=null),
                                          Mile=D.Mil,
                                          Speed=D.Speed
                                          });
+1
Mageshwaran R6y ago
Hi,
please refer this link:
http://www.sqltolinq.com/
+1
Madan Shekar7y ago
var result= (from table1obj in db.table1
join  table2obj in db.table2 on table1obj.RID equals table2obj.RID
select new
{

table2objid=table2obj.ID,
table2objName=table2obj.oname,
countvalue=db.tblve.where(t=>t.MID==table2obj.ID).Tolist().count


}).Tolist();

if it helps mark as answer otherwise ping me with query
0
Rakesh Dandaragi7y ago
;WITH Numbers AS
(
SELECT 1 AS value
UNION ALL
SELECT value + 1 AS value
FROM Numbers
WHERE Numbers.value <= 99
)
SELECT FinalTable.FromDate,FinalTable.AddedDays,FinalTable.AdjustedDate
FROM (
SELECT Final.*,ROW_Number() OVER (ORDER BY (SELECT NULL)) AS AddedDays
FROM (
SELECT tbl.FromDate,CASE WHEN DATENAME(dw,tbl.AdjustedDate) = 'Saturday' THEN 0
WHEN DATENAME(dw,tbl.AdjustedDate) = 'Sunday' THEN 0
WHEN tbl.AdjustedDate in (SELECT HolidayDate FROM mHolidayMasterTBL) THEN 0
ELSE 1 END AS LogicNumber ,tbl.days, tbl.AdjustedDate
FROM (
SELECT @FromDate AS FromDate, DATEADD(DAY,num,@FromDate) AS AdjustedDate, num AS days
FROM (
SELECT ROW_Number() OVER (ORDER BY (SELECT NULL)) AS num FROM Numbers
) t
WHERE num <= 100
) tbl
)Final
WHERE LogicNumber = 1
)FinalTable
WHERE AddedDays = @NoOfDays
+1
Vinay Singh10y ago
Hi try the below tool to convert it
https://www.linqpad.net/
http://www.sqltolinq.com/
+3