none
Problemas LINQ - Cláusula WHERE - Ajuda! RRS feed

  • Pergunta

  • Boa tarde,

    estou tendo problemas com a cláusula WHERE ao tentar adicionar um filtro (ilp.InicioVigencia <= data && ilp.FinalVigencia >= data) ao seguinte código:

          System.Linq.IQueryable<ListaPreco> qryLstPrc =
            (
              from p in dados.Produtos
              join pl in dados.Produtos on
               new { ProdPl = p.Produto, EmpPl = p.Empresa, FilPl = p.Filial, Emp = p.Empresa, Fil = p.Filial } equals
               new { ProdPl = (int)pl.ProdutoLista, EmpPl = (short)pl.EmpProduLista, FilPl = (short)pl.FilProduLista, Emp = pl.Empresa, Fil = pl.Filial }
              join tp in dados.TipoProd on
                new { p.TipoProduto, Empresa = p.EmpGrupProd, Filial = p.FilGrupProd } equals
                new { tp.TipoProduto, Empresa = tp.Empresa, Filial = tp.Filial }
              join lp in dados.ListaPrc on
                new { p.Produto, Empresa = p.Empresa, Filial = p.Filial } equals
                new { lp.Produto, Empresa = lp.EmpProdut, Filial = lp.FilProdut }
              join ilp in dados.ItLisPrc on
                new { Empresa = lp.Empresa, Filial = lp.Filial, ListaPrc = lp.ListaPreco } equals
                new { Empresa = ilp.EmpListaPrc, Filial = ilp.FilListaPrc, ListaPrc = ilp.ListaPreco }
              join cp in dados.ColPreco on
                new { Empresa = ilp.EmpColPreco, Filial = ilp.FilColPreco, ilp.ColunaPreco, Ativo = "S" } equals
                new { Empresa = cp.Empresa, Filial = cp.Filial, cp.ColunaPreco, Ativo = cp.IndAtivo }
              join mr in dados.MoedaRef on
                new { Empresa = (short)cp.EmpMoedaRef, Filial = (short)cp.FilMoedaRef, MoedaRef = (short)cp.MoedaRefer } equals
                new { Empresa = mr.Empresa, Filial = mr.Filial, MoedaRef = mr.MoedaRefer }
              join cpg in dados.CondPgto on
                new { Empresa = (short)usuario.Empresa, Filial = (short)usuario.Filial, CondPgto = condicaoPagto } equals
                new { Empresa = cpg.Empresa, Filial = cpg.Filial, CondPgto = cpg.CondicaoPagto } into cpgs
              from cpg in cpgs.DefaultIfEmpty() // LEFT OUTER JOIN
              where            
    	    (ilp.InicioVigencia <= data && ilp.FinalVigencia >= data) &&
                lp.Empresa == empLista &&
                lp.Filial == filLista &&
                p.Empresa == empProduto &&
                p.Filial == filProduto &&
                // Lista Reposição  
                (colPreco <= 0) ?
                  (
                    cp.ColunaPreco != ListaRepos && cp.IndAtivo == "S"
                  ) :
                  (
                    cp.ColunaPreco == colPreco && cp.ColunaPreco != ListaRepos && cp.IndAtivo == "S"
                  ) &&
                // Coluna Preco Fora
                (
                  from cp1 in dados.ColPreco
                  where
                    cp1.Empresa == ilp.EmpColPreco &&
                    cp1.Filial == ilp.FilColPreco &&
                    cp1.ColunaPreco == ilp.ColunaPreco &&
                    cp1.ColunaPreco == colPrecoFora
                  select 1
                ).Count() == 0 &&
                // Produto
                (codigoBarras == String.Empty) ?
                  (
                    p.Produto == produto
                  ) :
                  (
                    p.CodigoBarras == codigoBarras
                  ) &&
                // Moeda
                (moeda > 0) ?
                  (
                    cp.EmpMoedaRef == empMoeda &&
                    cp.FilMoedaRef == filMoeda &&
                    cp.MoedaRefer == moeda
                  ) :
                  (
                    cp.EmpMoedaRef == 0 &&
                    cp.FilMoedaRef == 0 &&
                    cp.MoedaRefer == 0                
                  ) &&
                // Fornecedor
                (fornecedor > 0)?
                  (
                    lp.Fornecedor == fornecedor &&
                    lp.EmpForneced == empFornecedor &&
                    lp.FilForneced == filFornecedor
                  ) :              
                    lp.Fornecedor == null              
             
              select new ListaPreco
              {
                Tipo = 0,
                Nome = cp.Nome,
                Valor = ilp.Valor,
                PercentualDesconto = ilp.PercentualDesconto,
                DescontoFinanceiro = ilp.DescontoFinanceiro,
                ColunaPreco = cp.ColunaPreco,
                Empresa = cp.Empresa,
                Filial = cp.Filial,
                Produto = p.Produto,
                EmpProdut = p.Empresa,
                FilProdut = p.Filial,
                NMProduto = p.Nome,
                TipoProduto = p.TipoProduto,
                Classe = p.Classe,
                Grupo = p.Grupo,
                SeqListaPreco = ilp.SeqListaPreco,
                CodProduto = p.Produto,
                NomeProduto = p.Nome,
                BarrasProduto = p.CodigoBarras,
                UnidadeProduto = p.Unidade,
                DescricaoProduto = p.Descricao,
                FatorPreco = cpg.FatorPreco,
                DescricaoCondPgto = cpg.Descricao,
                IndMaterialServico = tp.IndMaterialServico,
                TributacaoICMS = p.TributacaoICMS,
                CodigoNCM = (int)p.CodigoNCM,
                Agrupador = (p.Produto == 0) ? p.Produto + " - " + p.Nome : pl.Produto + " - " + pl.Nome,
              }
            );

    segue a classe ListaPreco:

      public class ListaPreco
      {
        public int Tipo;
        public string Nome;
        public decimal? Valor;
        public decimal? PercentualDesconto;
        public decimal? DescontoFinanceiro;
        public short ColunaPreco;
        public short Empresa;
        public short Filial;
        public int Produto;
        public short EmpProdut;
        public short FilProdut;
        public string NMProduto;
        public short TipoProduto;
        public short Classe;
        public short Grupo;
        public int SeqListaPreco;
        public int CodProduto;
        public string NomeProduto;
        public string BarrasProduto;
        public string UnidadeProduto;
        public string DescricaoProduto;
        public decimal? FatorPreco;
        public string DescricaoCondPgto;
        public string IndMaterialServico;
        public string TributacaoICMS;
        public int CodigoNCM;    
        public string Agrupador;
        public decimal ValorFator;
        public decimal VlrVaMinimo;
        public DateTime InicioVigencia;
        public DateTime FinalVigencia;
      }

    Ao compilar o código SQL gerado esta incorreto, deveria ter sido inserido a condição onde esta demarcado como ===>>>> no código gerado a seguir:

    SELECT [Project2].[produto]            AS [Produto], 
           0                               AS [C1], 
           [Project2].[nome2]              AS [Nome], 
           [Project2].[valor]              AS [Valor], 
           [Project2].[percentualdesconto] AS [PercentualDesconto], 
           [Project2].[descontofinanceiro] AS [DescontoFinanceiro], 
           [Project2].[colunapreco]        AS [ColunaPreco], 
           [Project2].[empresa2]           AS [Empresa], 
           [Project2].[filial2]            AS [Filial], 
           [Project2].[empresa]            AS [Empresa1], 
           [Project2].[filial]             AS [Filial1], 
           [Project2].[nome]               AS [Nome1], 
           [Project2].[tipoproduto]        AS [TipoProduto], 
           [Project2].[classe]             AS [Classe], 
           [Project2].[grupo]              AS [Grupo], 
           [Project2].[seqlistapreco]      AS [SeqListaPreco], 
           [Project2].[codigobarras]       AS [CodigoBarras], 
           [Project2].[unidade]            AS [Unidade], 
           [Project2].[descricao]          AS [Descricao], 
           [Project2].[c2]                 AS [C2], 
           [Project2].[c1]                 AS [C3], 
           [Project2].[indmaterialservico] AS [IndMaterialServico], 
           [Project2].[tributacaoicms]     AS [TributacaoICMS], 
           [Project2].[codigoncm]          AS [CodigoNCM], 
           CASE 
             WHEN ( 0 = [Project2].[produto] ) THEN 
             Cast( [Project2].[produto] AS NVARCHAR( 
             max)) 
             + N' - ' + [Project2].[nome] 
             ELSE Cast( [Project2].[produto1] AS NVARCHAR(max)) 
                  + N' - ' + [Project2].[nome1] 
           END                             AS [C4] 
    FROM   (SELECT [Extent1].[produto] 
                   AS 
                          [Produto], 
                   [Extent1].[filial] 
                   AS 
                          [Filial], 
                   [Extent1].[empresa] 
                   AS 
                          [Empresa], 
                   [Extent1].[classe] 
                   AS 
                          [Classe], 
                   [Extent1].[grupo] 
                   AS 
                          [Grupo], 
                   [Extent1].[tipoproduto] 
                   AS 
                          [TipoProduto], 
                   [Extent1].[nome] 
                   AS 
                          [Nome], 
                   [Extent1].[descricao] 
                   AS 
                          [Descricao], 
                   [Extent1].[unidade] 
                   AS 
                          [Unidade], 
                   [Extent1].[tributacaoicms] 
                   AS 
                          [TributacaoICMS], 
                   [Extent1].[codigoncm] 
                   AS 
                          [CodigoNCM], 
                   [Extent1].[codigobarras] 
                   AS 
                          [CodigoBarras], 
                   [Extent2].[produto] 
                   AS 
                          [Produto1], 
                   [Extent2].[nome] 
                   AS 
                          [Nome1], 
                   [Extent3].[indmaterialservico] 
                   AS 
                          [IndMaterialServico], 
                   [Extent4].[filial] 
                   AS 
                          [Filial1], 
                   [Extent4].[empresa] 
                   AS 
                          [Empresa1], 
                   [Extent4].[fornecedor] 
                   AS 
                          [Fornecedor], 
                   [Extent4].[filforneced] 
                   AS 
                          [FilForneced], 
                   [Extent4].[empforneced] 
                   AS 
                          [EmpForneced], 
                   [Extent5].[seqlistapreco] 
                   AS 
                          [SeqListaPreco], 
                   [Extent5].[iniciovigencia] 
                   AS 
                          [InicioVigencia], 
                   [Extent5].[finalvigencia] 
                   AS 
                          [FinalVigencia], 
                   [Extent5].[valor] 
                   AS 
                          [Valor], 
                   [Extent5].[percentualdesconto] 
                   AS 
                          [PercentualDesconto], 
                   [Extent5].[descontofinanceiro] 
                   AS 
                          [DescontoFinanceiro], 
                   [Extent6].[colunapreco] 
                   AS 
                          [ColunaPreco], 
                   [Extent6].[filial] 
                   AS 
                          [Filial2], 
                   [Extent6].[empresa] 
                   AS 
                          [Empresa2], 
                   [Extent6].[nome] 
                   AS 
                          [Nome2], 
                   [Extent6].[empmoedaref] 
                   AS 
                          [EmpMoedaRef], 
                   [Extent6].[filmoedaref] 
                   AS 
                          [FilMoedaRef], 
                   [Extent6].[moedarefer] 
                   AS 
                          [MoedaRefer], 
                   [Extent6].[indativo] 
                   AS 
                          [IndAtivo], 
                   [Project1].[c1] 
                   AS 
                          [C1], 
                   [Project1].[c2] 
                   AS 
                          [C2], 
                   (SELECT Count(1) AS [A1] 
                    FROM   [dbo].[colpreco] AS [Extent7] 
                    WHERE  ( Cast([Extent7].[empresa] AS INT) = Cast( 
                             [Extent5].[empcolpreco] AS INT 
                                                                ) ) 
                           AND ( Cast([Extent7].[filial] AS INT) = Cast( 
                                 [Extent5].[filcolpreco] AS INT) ) 
                           AND ( Cast([Extent7].[colunapreco] AS INT) = 
                                 Cast([Extent5].[colunapreco] AS INT) ) 
                           AND ( Cast([Extent7].[colunapreco] AS INT) = @p__linq__13 
                               )) AS 
                          [C3] 
            FROM   [dbo].[produtos] AS [Extent1] 
                   INNER JOIN [dbo].[produtos] AS [Extent2] 
                           ON ( [Extent1].[filial] = [Extent2].[filial] ) 
                              AND ( [Extent1].[empresa] = [Extent2].[empresa] ) 
                              AND ( [Extent1].[filial] = [Extent2].[filprodulista] ) 
                              AND ( [Extent1].[empresa] = 
                                  [Extent2].[empprodulista] ) 
                              AND ( [Extent1].[produto] = [Extent2].[produtolista] ) 
                   INNER JOIN [dbo].[tipoprod] AS [Extent3] 
                           ON ( [Extent1].[filgrupprod] = [Extent3].[filial] ) 
                              AND ( [Extent1].[empgrupprod] = [Extent3].[empresa] ) 
                              AND ( [Extent1].[tipoproduto] = 
                                    [Extent3].[tipoproduto] ) 
                   INNER JOIN [dbo].[listaprc] AS [Extent4] 
                           ON ( [Extent1].[filial] = [Extent4].[filprodut] ) 
                              AND ( [Extent1].[empresa] = [Extent4].[empprodut] ) 
                              AND ( [Extent1].[produto] = [Extent4].[produto] ) 
                   INNER JOIN [dbo].[itlisprc] AS [Extent5] 
                           ON ( [Extent4].[listapreco] = [Extent5].[listapreco] ) 
                              AND ( [Extent4].[filial] = [Extent5].[fillistaprc] ) 
                              AND ( [Extent4].[empresa] = [Extent5].[emplistaprc] ) 
                   INNER JOIN [dbo].[colpreco] AS [Extent6] 
                           ON 1 = 0 
                   LEFT OUTER JOIN (SELECT Cast(NULL AS VARCHAR(1))    AS [C1], 
                                           Cast(NULL AS DECIMAL(9, 4)) AS [C2] 
                                    FROM   (SELECT 1 AS X) AS [SingleRowTable1] 
                                    WHERE  1 = 0) AS [Project1] 
                                ON 1 = 1 
       ===>>>>     WHERE  ( [Extent6].[empmoedaref] IS NOT NULL ) 
                   AND ( [Extent6].[filmoedaref] IS NOT NULL ) 
                   AND ( [Extent6].[moedarefer] IS NOT NULL )) AS [Project2] 
    WHERE  ( CASE 
               WHEN ( ( [Project2].[iniciovigencia] <= @p__linq__3 ) 
                      AND ( [Project2].[finalvigencia] >= @p__linq__4 ) 
                      AND ( Cast([Project2].[empresa1] AS INT) = @p__linq__5 ) 
                      AND ( Cast([Project2].[filial1] AS INT) = @p__linq__6 ) 
                      AND ( Cast([Project2].[empresa] AS INT) = @p__linq__7 ) 
                      AND ( Cast([Project2].[filial] AS INT) = @p__linq__8 ) 
                      AND ( @p__linq__9 <= 0 ) ) THEN 
                 CASE 
                   WHEN ( ( Cast([Project2].[colunapreco] AS INT) <> @p__linq__10 ) 
                          AND ( 'S' = [Project2].[indativo] ) ) THEN Cast(1 AS BIT) 
                   WHEN ( NOT ( ( Cast([Project2].[colunapreco] AS INT) <> 
                                  @p__linq__10 ) 
                                AND ( 'S' = [Project2].[indativo] ) ) ) THEN Cast(0 
                   AS BIT) 
                 END 
               WHEN ( ( Cast([Project2].[colunapreco] AS INT) = @p__linq__11 ) 
                      AND ( Cast([Project2].[colunapreco] AS INT) <> @p__linq__12 ) 
                      AND ( 'S' = [Project2].[indativo] ) 
                      AND ( 0 = [Project2].[c3] ) 
                      AND ( ( @p__linq__14 = @p__linq__15 ) 
                             OR ( ( @p__linq__14 IS NULL ) 
                                  AND ( @p__linq__15 IS NULL ) ) ) ) THEN 
                 CASE 
                   WHEN ( [Project2].[produto] = @p__linq__16 ) THEN Cast(1 AS BIT) 
                   WHEN ( [Project2].[produto] <> @p__linq__16 ) THEN Cast(0 AS BIT) 
                 END 
               WHEN ( ( ( [Project2].[codigobarras] = @p__linq__17 ) 
                         OR ( ( [Project2].[codigobarras] IS NULL ) 
                              AND ( @p__linq__17 IS NULL ) ) ) 
                      AND ( @p__linq__18 > 0 ) ) THEN 
                 CASE 
                   WHEN ( ( ( Cast([Project2].[empmoedaref] AS INT) = @p__linq__19 ) 
                             OR ( ( [Project2].[empmoedaref] IS NULL ) 
                                  AND ( @p__linq__19 IS NULL ) ) ) 
                          AND ( ( Cast([Project2].[filmoedaref] AS INT) = 
                                  @p__linq__20 ) 
                                 OR ( ( [Project2].[filmoedaref] IS NULL ) 
                                      AND ( @p__linq__20 IS NULL ) ) ) 
                          AND ( ( Cast([Project2].[moedarefer] AS INT) = 
                                  @p__linq__21 ) 
                                 OR ( ( [Project2].[moedarefer] IS NULL ) 
                                      AND ( @p__linq__21 IS NULL ) ) ) ) THEN 
                   Cast(1 AS BIT) 
                   WHEN ( NOT ( ( Cast([Project2].[empmoedaref] AS INT) = 
                                  @p__linq__19 ) 
                                AND ( ( CASE 
                                          WHEN ( [Project2].[empmoedaref] IS NULL ) 
                                        THEN 
                                          Cast 
                                          (1 
                                          AS BIT) 
                                          ELSE Cast(0 AS BIT) 
                                        END ) = ( CASE 
                                                    WHEN ( @p__linq__19 IS NULL ) 
                                                  THEN 
                                                    Cast(1 AS BIT) 
                                                    ELSE Cast(0 AS BIT) 
                                                  END ) ) 
                                AND ( Cast([Project2].[filmoedaref] AS INT) = 
                                      @p__linq__20 
                                    ) 
                                AND ( ( CASE 
                                          WHEN ( [Project2].[filmoedaref] IS NULL ) 
                                        THEN 
                                          Cast 
                                          (1 
                                          AS BIT) 
                                          ELSE Cast(0 AS BIT) 
                                        END ) = ( CASE 
                                                    WHEN ( @p__linq__20 IS NULL ) 
                                                  THEN 
                                                    Cast(1 AS BIT) 
                                                    ELSE Cast(0 AS BIT) 
                                                  END ) ) 
                                AND ( Cast([Project2].[moedarefer] AS INT) = 
                                      @p__linq__21 ) 
                                AND ( ( CASE 
                                          WHEN ( [Project2].[moedarefer] IS NULL ) 
                                        THEN 
                                          Cast(1 AS BIT) 
                                          ELSE Cast(0 AS BIT) 
                                        END ) = ( CASE 
                                                    WHEN ( @p__linq__21 IS NULL ) 
                                                  THEN 
                                                    Cast(1 AS BIT) 
                                                    ELSE Cast(0 AS BIT) 
                                                  END ) ) ) ) THEN Cast(0 AS BIT) 
                 END 
               WHEN ( ( 0 = Cast([Project2].[empmoedaref] AS INT) ) 
                      AND ( 0 = Cast([Project2].[filmoedaref] AS INT) ) 
                      AND ( 0 = Cast([Project2].[moedarefer] AS INT) ) 
                      AND ( @p__linq__22 > 0 ) ) THEN 
                 CASE 
                   WHEN ( ( ( [Project2].[fornecedor] = @p__linq__23 ) 
                             OR ( ( [Project2].[fornecedor] IS NULL ) 
                                  AND ( @p__linq__23 IS NULL ) ) ) 
                          AND ( ( Cast([Project2].[empforneced] AS INT) = 
                                  @p__linq__24 ) 
                                 OR ( ( [Project2].[empforneced] IS NULL ) 
                                      AND ( @p__linq__24 IS NULL ) ) ) 
                          AND ( ( Cast([Project2].[filforneced] AS INT) = 
                                  @p__linq__25 ) 
                                 OR ( ( [Project2].[filforneced] IS NULL ) 
                                      AND ( @p__linq__25 IS NULL ) ) ) ) THEN 
                   Cast(1 AS BIT) 
                   WHEN ( NOT ( ( [Project2].[fornecedor] = @p__linq__23 ) 
                                AND ( ( CASE 
                                          WHEN ( [Project2].[fornecedor] IS NULL ) 
                                        THEN 
                                          Cast(1 AS BIT) 
                                          ELSE Cast(0 AS BIT) 
                                        END ) = ( CASE 
                                                    WHEN ( @p__linq__23 IS NULL ) 
                                                  THEN 
                                                    Cast(1 AS BIT) 
                                                    ELSE Cast(0 AS BIT) 
                                                  END ) ) 
                                AND ( Cast([Project2].[empforneced] AS INT) = 
                                      @p__linq__24 
                                    ) 
                                AND ( ( CASE 
                                          WHEN ( [Project2].[empforneced] IS NULL ) 
                                        THEN 
                                          Cast 
                                          (1 
                                          AS BIT) 
                                          ELSE Cast(0 AS BIT) 
                                        END ) = ( CASE 
                                                    WHEN ( @p__linq__24 IS NULL ) 
                                                  THEN 
                                                    Cast(1 AS BIT) 
                                                    ELSE Cast(0 AS BIT) 
                                                  END ) ) 
                                AND ( Cast([Project2].[filforneced] AS INT) = 
                                      @p__linq__25 
                                    ) 
                                AND ( ( CASE 
                                          WHEN ( [Project2].[filforneced] IS NULL ) 
                                        THEN 
                                          Cast 
                                          (1 
                                          AS BIT) 
                                          ELSE Cast(0 AS BIT) 
                                        END ) = ( CASE 
                                                    WHEN ( @p__linq__25 IS NULL ) 
                                                  THEN 
                                                    Cast(1 AS BIT) 
                                                    ELSE Cast(0 AS BIT) 
                                                  END ) ) ) ) THEN Cast(0 AS BIT) 
                 END 
               WHEN ( [Project2].[fornecedor] IS NULL ) THEN Cast(1 AS BIT) 
               ELSE Cast(0 AS BIT) 
             END ) = 1 

    Se alguém puder ajudar ou se tiver uma noção e puder dar uma dica fico no aguardo.

    Att,

    Luiz Satto.

    quarta-feira, 23 de abril de 2014 20:34

Respostas

  • Olá tente seu where antes do DefaultIsEmpty() por ex :

    var sql = from tb in db.TB1
                                      join a in db.TB2 on tb.ID equals a.ID
                                      where a.ID_NA > 0
                                      select a;

    Veja que o where está logo após a juncao e não depois do from ! 

    Se não for isso ! Poste o Sql que deseja mas sem ser o gerado pelo Enity !

    quinta-feira, 24 de abril de 2014 14:22

Todas as Respostas

  • Qual seria o sql desejado ! Mostre um exemplo mas somente com uma juncao assim fica ruim pra ler...

    No Where o que desejaria que acontecesse !

    quinta-feira, 24 de abril de 2014 00:21
  • Bom dia,

    desculpe eu simplesmente adicionei o código gerado sem exemplificar o que desejava...

    Segue abaixo o sql desejado:

    SELECT [Project2].[produto]            AS [Produto], 
           0                               AS [C1], 
           [Project2].[nome2]              AS [Nome], 
           [Project2].[valor]              AS [Valor], 
           [Project2].[percentualdesconto] AS [PercentualDesconto], 
           [Project2].[descontofinanceiro] AS [DescontoFinanceiro], 
           [Project2].[colunapreco]        AS [ColunaPreco], 
           [Project2].[empresa2]           AS [Empresa], 
           [Project2].[filial2]            AS [Filial], 
           [Project2].[empresa]            AS [Empresa1], 
           [Project2].[filial]             AS [Filial1], 
           [Project2].[nome]               AS [Nome1], 
           [Project2].[tipoproduto]        AS [TipoProduto], 
           [Project2].[classe]             AS [Classe], 
           [Project2].[grupo]              AS [Grupo], 
           [Project2].[seqlistapreco]      AS [SeqListaPreco], 
           [Project2].[codigobarras]       AS [CodigoBarras], 
           [Project2].[unidade]            AS [Unidade], 
           [Project2].[descricao]          AS [Descricao], 
           [Project2].[c2]                 AS [C2], 
           [Project2].[c1]                 AS [C3], 
           [Project2].[indmaterialservico] AS [IndMaterialServico], 
           [Project2].[tributacaoicms]     AS [TributacaoICMS], 
           [Project2].[codigoncm]          AS [CodigoNCM], 
           CASE 
             WHEN ( 0 = [Project2].[produto] ) THEN 
             Cast( [Project2].[produto] AS NVARCHAR( 
             max)) 
             + N' - ' + [Project2].[nome] 
             ELSE Cast( [Project2].[produto1] AS NVARCHAR(max)) 
                  + N' - ' + [Project2].[nome1] 
           END                             AS [C4] 
    FROM   (SELECT [Extent1].[produto]                                       AS 
                   [Produto], 
                   [Extent1].[filial]                                        AS 
                   [Filial], 
                   [Extent1].[empresa]                                       AS 
                   [Empresa], 
                   [Extent1].[classe]                                        AS 
                   [Classe], 
                   [Extent1].[grupo]                                         AS 
                   [Grupo], 
                   [Extent1].[tipoproduto]                                   AS 
                          [TipoProduto], 
                   [Extent1].[nome]                                          AS 
                   [Nome], 
                   [Extent1].[descricao]                                     AS 
                   [Descricao] 
                          , 
                   [Extent1].[unidade] 
                          AS [Unidade], 
                   [Extent1].[tributacaoicms]                                AS 
                          [TributacaoICMS], 
                   [Extent1].[codigoncm]                                     AS 
                   [CodigoNCM] 
                          , 
                   [Extent1].[codigobarras] 
                          AS [CodigoBarras], 
                   [Extent2].[produto]                                       AS 
                   [Produto1], 
                   [Extent2].[nome]                                          AS 
                   [Nome1], 
                   [Extent3].[indmaterialservico]                            AS 
                          [IndMaterialServico], 
                   [Extent4].[filial]                                        AS 
                   [Filial1], 
                   [Extent4].[empresa]                                       AS 
                   [Empresa1], 
                   [Extent4].[fornecedor]                                    AS 
                          [Fornecedor], 
                   [Extent4].[filforneced]                                   AS 
                          [FilForneced], 
                   [Extent4].[empforneced]                                   AS 
                          [EmpForneced], 
                   [Extent5].[seqlistapreco]                                 AS 
                          [SeqListaPreco], 
                   [Extent5].[iniciovigencia]                                AS 
                          [InicioVigencia], 
                   [Extent5].[finalvigencia]                                 AS 
                          [FinalVigencia], 
                   [Extent5].[valor]                                         AS 
                   [Valor], 
                   [Extent5].[percentualdesconto]                            AS 
                          [PercentualDesconto], 
                   [Extent5].[descontofinanceiro]                            AS 
                          [DescontoFinanceiro], 
                   [Extent6].[colunapreco]                                   AS 
                          [ColunaPreco], 
                   [Extent6].[filial]                                        AS 
                   [Filial2], 
                   [Extent6].[empresa]                                       AS 
                   [Empresa2], 
                   [Extent6].[nome]                                          AS 
                   [Nome2], 
                   [Extent6].[empmoedaref]                                   AS 
                          [EmpMoedaRef], 
                   [Extent6].[filmoedaref]                                   AS 
                          [FilMoedaRef], 
                   [Extent6].[moedarefer]                                    AS 
                          [MoedaRefer], 
                   [Extent6].[indativo]                                      AS 
                   [IndAtivo], 
                   [Project1].[c1]                                           AS [C1] 
                   , 
                   [Project1].[c2] 
                   AS [C2], 
                   (SELECT Count(1) AS [A1] 
                    FROM   [dbo].[colpreco] AS [Extent7] 
                    WHERE  ( Cast([Extent7].[empresa] AS INT) = Cast( 
                             [Extent5].[empcolpreco] AS INT 
                                                                ) ) 
                           AND ( Cast([Extent7].[filial] AS INT) = Cast( 
                                 [Extent5].[filcolpreco] AS INT) ) 
                           AND ( Cast([Extent7].[colunapreco] AS INT) = 
                                 Cast([Extent5].[colunapreco] AS INT) ) 
                           AND ( Cast([Extent7].[colunapreco] AS INT) = 2 )) AS [C3] 
            FROM   [dbo].[produtos] AS [Extent1] 
                   INNER JOIN [dbo].[produtos] AS [Extent2] 
                           ON ( [Extent1].[filial] = [Extent2].[filial] ) 
                              AND ( [Extent1].[empresa] = [Extent2].[empresa] ) 
                              AND ( [Extent1].[filial] = [Extent2].[filprodulista] ) 
                              AND ( [Extent1].[empresa] = [Extent2].[empprodulista] 
                                  ) 
                              AND ( [Extent1].[produto] = [Extent2].[produtolista] ) 
                   INNER JOIN [dbo].[tipoprod] AS [Extent3] 
                           ON ( [Extent1].[filgrupprod] = [Extent3].[filial] ) 
                              AND ( [Extent1].[empgrupprod] = [Extent3].[empresa] ) 
                              AND ( [Extent1].[tipoproduto] = 
                                    [Extent3].[tipoproduto] ) 
                   INNER JOIN [dbo].[listaprc] AS [Extent4] 
                           ON ( [Extent1].[filial] = [Extent4].[filprodut] ) 
                              AND ( [Extent1].[empresa] = [Extent4].[empprodut] ) 
                              AND ( [Extent1].[produto] = [Extent4].[produto] ) 
                   INNER JOIN [dbo].[itlisprc] AS [Extent5] 
                           ON ( [Extent4].[listapreco] = [Extent5].[listapreco] ) 
                              AND ( [Extent4].[filial] = [Extent5].[fillistaprc] ) 
                              AND ( [Extent4].[empresa] = [Extent5].[emplistaprc] ) 
                   -- 
                   INNER JOIN [dbo].[colpreco] AS [Extent6] 
                           ON ( [Extent5].[colunapreco] = [Extent6].[colunapreco] ) 
                              AND ( [Extent5].[filcolpreco] = [Extent6].[filial] ) 
                              AND ( [Extent5].[empcolpreco] = [Extent6].[empresa] ) 
                              AND ( [Extent6].indativo = 'S' ) 
                              AND ( [Extent6].colunapreco <> 9 ) 
                   LEFT OUTER JOIN (SELECT Cast(NULL AS VARCHAR(1))    AS [C1], 
                                           Cast(NULL AS DECIMAL(9, 4)) AS [C2] 
                                    FROM   (SELECT 1 AS X) AS [SingleRowTable1] 
                                    WHERE  1 = 0) AS [Project1] 
                                ON 1 = 1 
            /* Que adicionasse aqui \/ */ 
            WHERE  ( [Extent6].[empmoedaref] IS NOT NULL ) 
                   AND ( [Extent5].iniciovigencia <= '04/22/2014' ) 
                   AND ( [Extent5].finalvigencia >= '04/22/2014' ) 
                   AND ( [Extent6].[filmoedaref] IS NOT NULL ) 
                   AND ( [Extent6].[moedarefer] IS NOT NULL )) AS [Project2] 
            /* Que adicionasse aqui /\ */ 
    WHERE  ( CASE 
               WHEN ( /*  ( [Project2].[iniciovigencia] <= '04/23/2014' ) <<<< Não aqui 
                                     AND ( [Project2].[finalvigencia] >= '04/23/2014' )  
                                     AND*/ ( Cast([Project2].[empresa1] AS INT) = 
                              1 ) 
                                            AND ( Cast([Project2].[filial1] AS INT) 
                                                  = 0 ) 
                                            AND ( Cast([Project2].[empresa] AS INT) 
                                                  = 1 ) 
                                            AND ( Cast([Project2].[filial] AS INT) = 
                                                  0 ) 
                                            AND ( -1 <= 0 ) ) THEN 
                 CASE 
                   WHEN ( ( Cast([Project2].[colunapreco] AS INT) <> 9 ) 
                          AND ( 'S' = [Project2].[indativo] ) ) THEN Cast(1 AS BIT) 
                   WHEN ( NOT ( ( Cast([Project2].[colunapreco] AS INT) <> 9 ) 
                                AND ( 'S' = [Project2].[indativo] ) ) ) THEN Cast(0 
                   AS BIT) 
                 END 
               WHEN ( ( Cast([Project2].[colunapreco] AS INT) = -1 ) 
                      AND ( Cast([Project2].[colunapreco] AS INT) <> 9 ) 
                      AND ( 'S' = [Project2].[indativo] ) 
                      AND ( 0 = [Project2].[c3] ) ) THEN 
                 CASE 
                   WHEN ( [Project2].[produto] = 2 ) THEN Cast(1 AS BIT) 
                   WHEN ( [Project2].[produto] <> 2 ) THEN Cast(0 AS BIT) 
                 END 
               WHEN ( ( ( [Project2].[codigobarras] = '' ) 
                         OR ( ( [Project2].[codigobarras] IS NULL ) 
                              AND ( '' IS NULL ) ) ) 
                      AND ( 0 > 0 ) ) THEN 
                 CASE 
                   WHEN ( ( ( Cast([Project2].[empmoedaref] AS INT) = 1 ) 
                             OR ( ( [Project2].[empmoedaref] IS NULL ) 
                                  AND ( 1 IS NULL ) ) ) 
                          AND ( ( Cast([Project2].[filmoedaref] AS INT) = 0 ) 
                                 OR ( ( [Project2].[filmoedaref] IS NULL ) 
                                      AND ( 0 IS NULL ) ) ) 
                          AND ( ( Cast([Project2].[moedarefer] AS INT) = 0 ) 
                                 OR ( ( [Project2].[moedarefer] IS NULL ) 
                                      AND ( 0 IS NULL ) ) ) ) THEN Cast(1 AS BIT) 
                   WHEN ( NOT ( ( Cast([Project2].[empmoedaref] AS INT) = 1 ) 
                                AND ( ( CASE 
                                          WHEN ( [Project2].[empmoedaref] IS NULL ) 
                                        THEN 
                                          Cast 
                                          (1 
                                          AS BIT) 
                                          ELSE Cast(0 AS BIT) 
                                        END ) = ( CASE 
                                                    WHEN ( 1 IS NULL ) THEN Cast(1 
                                                    AS BIT) 
                                                    ELSE Cast(0 AS BIT) 
                                                  END ) ) 
                                AND ( Cast([Project2].[filmoedaref] AS INT) = 0 ) 
                                AND ( ( CASE 
                                          WHEN ( [Project2].[filmoedaref] IS NULL ) 
                                        THEN 
                                          Cast 
                                          (1 
                                          AS BIT) 
                                          ELSE Cast(0 AS BIT) 
                                        END ) = ( CASE 
                                                    WHEN ( 0 IS NULL ) THEN Cast(1 
                                                    AS BIT) 
                                                    ELSE Cast(0 AS BIT) 
                                                  END ) ) 
                                AND ( Cast([Project2].[moedarefer] AS INT) = 0 ) 
                                AND ( ( CASE 
                                          WHEN ( [Project2].[moedarefer] IS NULL ) 
                                        THEN 
                                          Cast(1 AS BIT) 
                                          ELSE Cast(0 AS BIT) 
                                        END ) = ( CASE 
                                                    WHEN ( 0 IS NULL ) THEN Cast(1 
                                                    AS BIT) 
                                                    ELSE Cast(0 AS BIT) 
                                                  END ) ) ) ) THEN Cast(0 AS BIT) 
                 END 
               WHEN ( ( 0 = Cast([Project2].[empmoedaref] AS INT) ) 
                      AND ( 0 = Cast([Project2].[filmoedaref] AS INT) ) 
                      AND ( 0 = Cast([Project2].[moedarefer] AS INT) ) 
                      AND ( 0 > 0 ) ) THEN 
                 CASE 
                   WHEN ( ( ( [Project2].[fornecedor] = -1 ) 
                             OR ( ( [Project2].[fornecedor] IS NULL ) 
                                  AND ( -1 IS NULL ) ) ) 
                          AND ( ( Cast([Project2].[empforneced] AS INT) = -1 ) 
                                 OR ( ( [Project2].[empforneced] IS NULL ) 
                                      AND ( -1 IS NULL ) ) ) 
                          AND ( ( Cast([Project2].[filforneced] AS INT) = -1 ) 
                                 OR ( ( [Project2].[filforneced] IS NULL ) 
                                      AND ( -1 IS NULL ) ) ) ) THEN Cast(1 AS BIT) 
                   WHEN ( NOT ( ( [Project2].[fornecedor] = -1 ) 
                                AND ( ( CASE 
                                          WHEN ( [Project2].[fornecedor] IS NULL ) 
                                        THEN 
                                          Cast(1 AS BIT) 
                                          ELSE Cast(0 AS BIT) 
                                        END ) = ( CASE 
                                                    WHEN ( -1 IS NULL ) THEN Cast(1 
                                                    AS BIT) 
                                                    ELSE Cast(0 AS BIT) 
                                                  END ) ) 
                                AND ( Cast([Project2].[empforneced] AS INT) = -1 ) 
                                AND ( ( CASE 
                                          WHEN ( [Project2].[empforneced] IS NULL ) 
                                        THEN 
                                          Cast 
                                          (1 
                                          AS BIT) 
                                          ELSE Cast(0 AS BIT) 
                                        END ) = ( CASE 
                                                    WHEN ( -1 IS NULL ) THEN Cast(1 
                                                    AS BIT) 
                                                    ELSE Cast(0 AS BIT) 
                                                  END ) ) 
                                AND ( Cast([Project2].[filforneced] AS INT) = -1 ) 
                                AND ( ( CASE 
                                          WHEN ( [Project2].[filforneced] IS NULL ) 
                                        THEN 
                                          Cast 
                                          (1 
                                          AS BIT) 
                                          ELSE Cast(0 AS BIT) 
                                        END ) = ( CASE 
                                                    WHEN ( -1 IS NULL ) THEN Cast(1 
                                                    AS BIT) 
                                                    ELSE Cast(0 AS BIT) 
                                                  END ) ) ) ) THEN Cast(0 AS BIT) 
                 END 
               WHEN ( [Project2].[fornecedor] IS NULL ) THEN Cast(1 AS BIT) 
               ELSE Cast(0 AS BIT) 
             END ) = 1 

    Att,

    Luiz Satto.

    quinta-feira, 24 de abril de 2014 11:28
  • Mesmo assim está complicado essa query gerada pelo entity é ruim de ler ! Mas pelo que vi vc quer que seu Where esteja junto com seu Left Outer JOin correto ?
    quinta-feira, 24 de abril de 2014 11:49
  • Realmente a query gerada é muito ilegível;

    Sim o que eu quero é inserir o filtro de data logo após o LEFT OUTER JOIN, mas de forma que ele utilize a junção anterior que neste caso foi nomeada por ele como 'EXTENT5':

    ...LEFT OUTER JOIN (
    	SELECT CAST(NULL AS VARCHAR(1))    AS [C1], 
                   CAST(NULL AS DECIMAL(9, 4)) AS [C2] 
            FROM 
    	  (SELECT 1 AS X) AS [SINGLEROWTABLE1] 
            WHERE  
    	  1 = 0) AS [PROJECT1] 
        ON 1 = 1 
        /* QUE ADICIONASSE AQUI \/ */ 
        WHERE  
              ( [EXTENT6].[EMPMOEDAREF] IS NOT NULL ) 
          AND ( [EXTENT5].INICIOVIGENCIA <= '04/22/2014' ) 
          AND ( [EXTENT5].FINALVIGENCIA >= '04/22/2014' ) 
          AND ( [EXTENT6].[FILMOEDAREF] IS NOT NULL ) 
          AND ( [EXTENT6].[MOEDAREFER] IS NOT NULL )) AS [PROJECT2] 
    WHERE  
      ( CASE 
          WHEN ( /*  ( [PROJECT2].[INICIOVIGENCIA] <= '04/23/2014' ) <<<< NÃO AQUI 
                 AND ( [PROJECT2].[FINALVIGENCIA] >= '04/23/2014' )  
                 AND*/ ( CAST([PROJECT2].[EMPRESA1] AS INT) = 
            1 ) 
          AND ( CAST([PROJECT2].[FILIAL1] AS INT) = 0 ) 
          AND ( CAST([PROJECT2].[EMPRESA] AS INT) = 1 ) 
          AND ( CAST([PROJECT2].[FILIAL] AS INT) = 0 ) 
          AND ( -1 <= 0 ) ) THEN 
        CASE...

    Att,

    Luiz Satto.

    quinta-feira, 24 de abril de 2014 12:12
  • Olá tente seu where antes do DefaultIsEmpty() por ex :

    var sql = from tb in db.TB1
                                      join a in db.TB2 on tb.ID equals a.ID
                                      where a.ID_NA > 0
                                      select a;

    Veja que o where está logo após a juncao e não depois do from ! 

    Se não for isso ! Poste o Sql que deseja mas sem ser o gerado pelo Enity !

    quinta-feira, 24 de abril de 2014 14:22
  • Diego isto resolveu o problema, obrigado.

    Att,

    Luiz Satto.

    quinta-feira, 24 de abril de 2014 14:52