Problemas LINQ - Cláusula WHERE - Ajuda!

Question

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.

Answer 1

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 !

Answer 2

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.

Answer 3

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 ?

Answer 4

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.

Answer 5

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 !

Answer 6

Diego isto resolveu o problema, obrigado.

Att,

Luiz Satto.