var transferencias = new TransferenciaService().ObtemListaDeTransferenciasPendentes();
IQueryable<FSI> obj = null;
foreach (var t in transferencias)
{
var FSI_EMP = t.FSI_EMP;
var FSI_FILIAL = t.FSI_FILIAL;
var FSI_REFER = t.FSI_REFER;
if (obj == null)
{
obj = ObjectContext.FSI.Where(e => e.FSI_EMP.Equals(FSI_EMP) &&
e.FSI_FILIAL.Equals(FSI_FILIAL) &&
e.FSI_REFER.Equals(FSI_REFER));
}
else
{
obj = obj.Union(ObjectContext.FSI.Where(e => e.FSI_EMP.Equals(FSI_EMP) &&
e.FSI_FILIAL.Equals(FSI_FILIAL) &&
e.FSI_REFER.Equals(FSI_REFER)));
}
}
return (from despesa in
obj
join cliente in ObjectContext.SA1 on
new { despesa.FSI_CODCLI, despesa.FSI_LOJA } equals
new { FSI_CODCLI = cliente.A1_COD, FSI_LOJA = cliente.A1_LOJA }
join assunto in ObjectContext.FSB on
new { despesa.FSI_ASSUNT } equals
new { FSI_ASSUNT = assunto.FSB_CODIGO }
group despesa by new
{
cliente.A1_NOME,
assunto.FSB_DESCRI,
} into g
select new TransferenciasPendentesData()
{
Key = g.Key.A1_NOME + g.Key.FSB_DESCRI,
Cliente = g.Key.A1_NOME,
Assunto = g.Key.FSB_DESCRI,
Valor = g.Sum(e => e.FSI_VLUREA)
}).OrderBy(e => e.Cliente)
.Skip(pageSize * pageIndex).Take(pageSize).ToList();
Query gerado e super rapida:
exec sp_executesql N'SELECT TOP (15)
[Project4].[C2] AS [C1],
[Project4].[C3] AS [C2],
[Project4].[A1_NOME] AS [A1_NOME],
[Project4].[FSB_DESCRI] AS [FSB_DESCRI],
[Project4].[C1] AS [C3]
FROM ( SELECT [Project4].[C1] AS [C1], [Project4].[A1_NOME] AS [A1_NOME], [Project4].[FSB_DESCRI] AS [FSB_DESCRI], [Project4].[C2] AS [C2], [Project4].[C3] AS [C3], row_number() OVER (ORDER BY [Project4].[A1_NOME] ASC) AS [row_number]
FROM ( SELECT
[GroupBy1].[A1] AS [C1],
[GroupBy1].[K1] AS [A1_NOME],
[GroupBy1].[K2] AS [FSB_DESCRI],
1 AS [C2],
[GroupBy1].[K1] + [GroupBy1].[K2] AS [C3]
FROM ( SELECT
[Extent3].[A1_NOME] AS [K1],
[Extent4].[FSB_DESCRI] AS [K2],
SUM([Distinct1].[C38]) AS [A1]
FROM (SELECT DISTINCT
[UnionAll1].[FSI_EMP] AS [C1],
[UnionAll1].[FSI_FILIAL] AS [C2],
[UnionAll1].[FSI_REFER] AS [C3],
[UnionAll1].[FSI_CODDOC] AS [C4],
[UnionAll1].[FSI_ASSUNT] AS [C5],
[UnionAll1].[FSI_CONTRA] AS [C6],
[UnionAll1].[FSI_CODCLI] AS [C7],
[UnionAll1].[FSI_LOJA] AS [C8],
[UnionAll1].[FSI_DATA] AS [C9],
[UnionAll1].[FSI_LOCALI] AS [C10],
[UnionAll1].[FSI_TIPOD] AS [C11],
[UnionAll1].[FSI_IDIORI] AS [C12],
[UnionAll1].[FSI_IDITRD] AS [C13],
[UnionAll1].[FSI_PERCEN] AS [C14],
[UnionAll1].[FSI_VLRORI] AS [C15],
[UnionAll1].[FSI_DATDIG] AS [C16],
[UnionAll1].[FSI_TIMEK] AS [C17],
[UnionAll1].[FSI_QUANT] AS [C18],
[UnionAll1].[FSI_VLRUNI] AS [C19],
[UnionAll1].[FSI_SALDO] AS [C20],
[UnionAll1].[FSI_OK] AS [C21],
[UnionAll1].[FSI_IDPFAT] AS [C22],
[UnionAll1].[FSI_FFATU] AS [C23],
[UnionAll1].[FSI_MSIDEN] AS [C24],
[UnionAll1].[FSI_IDPAI] AS [C25],
[UnionAll1].[FSI_ASSORI] AS [C26],
[UnionAll1].[FSI_STATUS] AS [C27],
[UnionAll1].[FSI_ORIGEM] AS [C28],
[UnionAll1].[FSI_CODRAT] AS [C29],
[UnionAll1].[FSI_CODRLI] AS [C30],
[UnionAll1].[FSI_DTLANC] AS [C31],
[UnionAll1].[FSI_DTLCFT] AS [C32],
[UnionAll1].[FSI_DATFAT] AS [C33],
[UnionAll1].[FSI_FLDTGP] AS [C34],
[UnionAll1].[FSI_CODRET] AS [C35],
[UnionAll1].[FSI_CODCOM] AS [C36],
[UnionAll1].[FSI_CCUSTO] AS [C37],
[UnionAll1].[FSI_VLUREA] AS [C38],
[UnionAll1].[FSI_APPRIM] AS [C39],
[UnionAll1].[FSI_RATEIO] AS [C40],
[UnionAll1].[FSI_TIPRAT] AS [C41],
[UnionAll1].[FSI_MOTBX] AS [C42],
[UnionAll1].[FSI_USERGA] AS [C43],
[UnionAll1].[FSI_USERGI] AS [C44],
[UnionAll1].[D_E_L_E_T_] AS [C45],
[UnionAll1].[R_E_C_N_O_] AS [C46],
[UnionAll1].[R_E_C_D_E_L_] AS [C47],
[UnionAll1].[FSI_DTREVA] AS [C48],
[UnionAll1].[FSI__NUSER] AS [C49],
[UnionAll1].[FSI_HRREVA] AS [C50],
[UnionAll1].[FSI_MTAXA] AS [C51],
[UnionAll1].[FSI__MOEDA] AS [C52],
[UnionAll1].[FSI_MOEDA] AS [C53]
FROM (SELECT
[Extent1].[FSI_EMP] AS [FSI_EMP],
[Extent1].[FSI_FILIAL] AS [FSI_FILIAL],
[Extent1].[FSI_REFER] AS [FSI_REFER],
[Extent1].[FSI_CODDOC] AS [FSI_CODDOC],
[Extent1].[FSI_ASSUNT] AS [FSI_ASSUNT],
[Extent1].[FSI_CONTRA] AS [FSI_CONTRA],
[Extent1].[FSI_CODCLI] AS [FSI_CODCLI],
[Extent1].[FSI_LOJA] AS [FSI_LOJA],
[Extent1].[FSI_DATA] AS [FSI_DATA],
[Extent1].[FSI_LOCALI] AS [FSI_LOCALI],
[Extent1].[FSI_TIPOD] AS [FSI_TIPOD],
[Extent1].[FSI_IDIORI] AS [FSI_IDIORI],
[Extent1].[FSI_IDITRD] AS [FSI_IDITRD],
[Extent1].[FSI_PERCEN] AS [FSI_PERCEN],
[Extent1].[FSI_VLRORI] AS [FSI_VLRORI],
[Extent1].[FSI_DATDIG] AS [FSI_DATDIG],
[Extent1].[FSI_TIMEK] AS [FSI_TIMEK],
[Extent1].[FSI_QUANT] AS [FSI_QUANT],
[Extent1].[FSI_VLRUNI] AS [FSI_VLRUNI],
[Extent1].[FSI_SALDO] AS [FSI_SALDO],
[Extent1].[FSI_OK] AS [FSI_OK],
[Extent1].[FSI_IDPFAT] AS [FSI_IDPFAT],
[Extent1].[FSI_FFATU] AS [FSI_FFATU],
[Extent1].[FSI_MSIDEN] AS [FSI_MSIDEN],
[Extent1].[FSI_IDPAI] AS [FSI_IDPAI],
[Extent1].[FSI_ASSORI] AS [FSI_ASSORI],
[Extent1].[FSI_STATUS] AS [FSI_STATUS],
[Extent1].[FSI_ORIGEM] AS [FSI_ORIGEM],
[Extent1].[FSI_CODRAT] AS [FSI_CODRAT],
[Extent1].[FSI_CODRLI] AS [FSI_CODRLI],
[Extent1].[FSI_DTLANC] AS [FSI_DTLANC],
[Extent1].[FSI_DTLCFT] AS [FSI_DTLCFT],
[Extent1].[FSI_DATFAT] AS [FSI_DATFAT],
[Extent1].[FSI_FLDTGP] AS [FSI_FLDTGP],
[Extent1].[FSI_CODRET] AS [FSI_CODRET],
[Extent1].[FSI_CODCOM] AS [FSI_CODCOM],
[Extent1].[FSI_CCUSTO] AS [FSI_CCUSTO],
[Extent1].[FSI_VLUREA] AS [FSI_VLUREA],
[Extent1].[FSI_APPRIM] AS [FSI_APPRIM],
[Extent1].[FSI_RATEIO] AS [FSI_RATEIO],
[Extent1].[FSI_TIPRAT] AS [FSI_TIPRAT],
[Extent1].[FSI_MOTBX] AS [FSI_MOTBX],
[Extent1].[FSI_USERGA] AS [FSI_USERGA],
[Extent1].[FSI_USERGI] AS [FSI_USERGI],
[Extent1].[D_E_L_E_T_] AS [D_E_L_E_T_],
[Extent1].[R_E_C_N_O_] AS [R_E_C_N_O_],
[Extent1].[R_E_C_D_E_L_] AS [R_E_C_D_E_L_],
[Extent1].[FSI_DTREVA] AS [FSI_DTREVA],
[Extent1].[FSI__NUSER] AS [FSI__NUSER],
[Extent1].[FSI_HRREVA] AS [FSI_HRREVA],
[Extent1].[FSI_MTAXA] AS [FSI_MTAXA],
[Extent1].[FSI__MOEDA] AS [FSI__MOEDA],
[Extent1].[FSI_MOEDA] AS [FSI_MOEDA]
FROM [dbo].[FSI] AS [Extent1]
WHERE ([Extent1].[FSI_EMP] = @p__linq__0) AND ([Extent1].[FSI_FILIAL] = @p__linq__1) AND ([Extent1].[FSI_REFER] = @p__linq__2)
UNION ALL
SELECT
[Extent2].[FSI_EMP] AS [FSI_EMP],
[Extent2].[FSI_FILIAL] AS [FSI_FILIAL],
[Extent2].[FSI_REFER] AS [FSI_REFER],
[Extent2].[FSI_CODDOC] AS [FSI_CODDOC],
[Extent2].[FSI_ASSUNT] AS [FSI_ASSUNT],
[Extent2].[FSI_CONTRA] AS [FSI_CONTRA],
[Extent2].[FSI_CODCLI] AS [FSI_CODCLI],
[Extent2].[FSI_LOJA] AS [FSI_LOJA],
[Extent2].[FSI_DATA] AS [FSI_DATA],
[Extent2].[FSI_LOCALI] AS [FSI_LOCALI],
[Extent2].[FSI_TIPOD] AS [FSI_TIPOD],
[Extent2].[FSI_IDIORI] AS [FSI_IDIORI],
[Extent2].[FSI_IDITRD] AS [FSI_IDITRD],
[Extent2].[FSI_PERCEN] AS [FSI_PERCEN],
[Extent2].[FSI_VLRORI] AS [FSI_VLRORI],
[Extent2].[FSI_DATDIG] AS [FSI_DATDIG],
[Extent2].[FSI_TIMEK] AS [FSI_TIMEK],
[Extent2].[FSI_QUANT] AS [FSI_QUANT],
[Extent2].[FSI_VLRUNI] AS [FSI_VLRUNI],
[Extent2].[FSI_SALDO] AS [FSI_SALDO],
[Extent2].[FSI_OK] AS [FSI_OK],
[Extent2].[FSI_IDPFAT] AS [FSI_IDPFAT],
[Extent2].[FSI_FFATU] AS [FSI_FFATU],
[Extent2].[FSI_MSIDEN] AS [FSI_MSIDEN],
[Extent2].[FSI_IDPAI] AS [FSI_IDPAI],
[Extent2].[FSI_ASSORI] AS [FSI_ASSORI],
[Extent2].[FSI_STATUS] AS [FSI_STATUS],
[Extent2].[FSI_ORIGEM] AS [FSI_ORIGEM],
[Extent2].[FSI_CODRAT] AS [FSI_CODRAT],
[Extent2].[FSI_CODRLI] AS [FSI_CODRLI],
[Extent2].[FSI_DTLANC] AS [FSI_DTLANC],
[Extent2].[FSI_DTLCFT] AS [FSI_DTLCFT],
[Extent2].[FSI_DATFAT] AS [FSI_DATFAT],
[Extent2].[FSI_FLDTGP] AS [FSI_FLDTGP],
[Extent2].[FSI_CODRET] AS [FSI_CODRET],
[Extent2].[FSI_CODCOM] AS [FSI_CODCOM],
[Extent2].[FSI_CCUSTO] AS [FSI_CCUSTO],
[Extent2].[FSI_VLUREA] AS [FSI_VLUREA],
[Extent2].[FSI_APPRIM] AS [FSI_APPRIM],
[Extent2].[FSI_RATEIO] AS [FSI_RATEIO],
[Extent2].[FSI_TIPRAT] AS [FSI_TIPRAT],
[Extent2].[FSI_MOTBX] AS [FSI_MOTBX],
[Extent2].[FSI_USERGA] AS [FSI_USERGA],
[Extent2].[FSI_USERGI] AS [FSI_USERGI],
[Extent2].[D_E_L_E_T_] AS [D_E_L_E_T_],
[Extent2].[R_E_C_N_O_] AS [R_E_C_N_O_],
[Extent2].[R_E_C_D_E_L_] AS [R_E_C_D_E_L_],
[Extent2].[FSI_DTREVA] AS [FSI_DTREVA],
[Extent2].[FSI__NUSER] AS [FSI__NUSER],
[Extent2].[FSI_HRREVA] AS [FSI_HRREVA],
[Extent2].[FSI_MTAXA] AS [FSI_MTAXA],
[Extent2].[FSI__MOEDA] AS [FSI__MOEDA],
[Extent2].[FSI_MOEDA] AS [FSI_MOEDA]
FROM [dbo].[FSI] AS [Extent2]
WHERE ([Extent2].[FSI_EMP] = @p__linq__3) AND ([Extent2].[FSI_FILIAL] = @p__linq__4) AND ([Extent2].[FSI_REFER] = @p__linq__5)) AS [UnionAll1] ) AS [Distinct1]
INNER JOIN [dbo].[SA1] AS [Extent3] ON ([Distinct1].[C7] = [Extent3].[A1_COD]) AND ([Distinct1].[C8] = [Extent3].[A1_LOJA])
INNER JOIN [dbo].[FSB] AS [Extent4] ON [Distinct1].[C5] = [Extent4].[FSB_CODIGO]
GROUP BY [Extent3].[A1_NOME], [Extent4].[FSB_DESCRI]
) AS [GroupBy1]
) AS [Project4]
) AS [Project4]
WHERE [Project4].[row_number] > 0
ORDER BY [Project4].[A1_NOME] ASC',N'@p__linq__0 varchar(8000),@p__linq__1 varchar(8000),@p__linq__2 varchar(8000),@p__linq__3 varchar(8000),@p__linq__4 varchar(8000),@p__linq__5 varchar(8000)',@p__linq__0='01',@p__linq__1='01',@p__linq__2='004400',@p__linq__3='01',@p__linq__4='01',@p__linq__5='004416'
