Ajuda com Query LINQ

Java Ninja Membro desde: 12/09/2007 13:17:41 Mensagens: 281 Offline

Bom dia pessoal, tenho o seguinte problema, se alguem souber a solução e poder me ajudar ficarei muito feliz

Tenho a seguinte Query LINQ abaixo. o problema é o seguinte:

var transferencias = new TransferenciaService().ObtemListaDeTransferenciasPendentes();

return (from despesa in 
                            ObjectContext.FSI.Where(e => e.FSI_EMP.Equals("01") &&
                                                                 e.FSI_FILIAL.Equals("01") &&
                                                                 e.FSI_REFER.Equals("004400"))
                         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();

Tenho um WebService "TransferenciaService().ObtemListaDeTransferenciasPendentes();", ele me retorna uma lista de despesas a serem transferiadas que fica em outro banco de dados, ai quando vou fazer uma query LINQ com o provider LinqToEntitiesDomainService, eu tenho que filtrar estas despesas na query !

Solução 1: usar a lista de transferencias como um objeto da query, o problema disto é que o LINQ, sendo algo assim:

Isto funciona, o problema é que ele traz para a memoria toda a tabela FSI, toda a tabela FSB e junta tudo em memoria ficando muito lento

Solução 2: fazer um Where igual esta na query

So que ai eu preciso de um for que leia toda a minha lista que vem do WebService e chame este metodo Where varias vezes. Não estou conseguindo fazer isto alguem sabe me dizer como fazer??

Muito Obrigado por qualquer ajuda.

Java Ninja Membro desde: 12/09/2007 13:17:41 Mensagens: 281 Offline

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'

Notícias, artigos e o maior fórum brasileiro sobre Java