How to add one more condition in a query using linq?

Question

How to add one more condition in a query using linq?

Navigation

#1 by (5 votes)
#2 by (3 votes)

3

How can I add one more condition in a join?

I have the query below;

 var consultaVeiculo = (from e in dm.Tabela_SegVeiculos
                                       join r in dm.Tabela_VeiculoRefers
                                       on  e.Modelo equals r.Cod_referencia 
                                       into veicRef
                                       from Referencia in veicRef.DefaultIfEmpty()
                                       where e.Seguradora == idSeguradora                                       
                                       orderby e.Modelo_cia
                                       select new { Descricao = Referencia == null ? "" : Referencia.Descricao, e });

It generates the following query in the sql server:

SELECT 
    (CASE 
        WHEN [t2].[test] IS NULL THEN CONVERT(NVarChar(50),'')
        ELSE CONVERT(NVarChar(50),[t2].[Descricao])
     END) AS [Descricao], [t0].[Seguradora], [t0].[Veic_cia], [t0].[Fabricante], [t0].[Modelo_cia], [t0].[Modelo], [t0].[Categ_AT], [t0].[Situacao], [t0].[Data_inclusao], [t0].[Data_alteracao], [t0].[COD_FIPE]
FROM [dbo].[Tabela_SegVeiculos] AS [t0]
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t1].[Descricao], [t1].[Cod_referencia]
    FROM [dbo].[Tabela_VeiculoRefer] AS [t1]
    ) AS [t2] ON [t0].[Modelo] = ([t2].[Cod_referencia])
WHERE ([t0].[Seguradora] = 83) AND ([t2].[Cod_referencia] > 0)
ORDER BY [t0].[Modelo_cia]

I need to add ON [t0].[Modelo] = ([t2].[Cod_referencia]) right after and [t2].[Cod_referencia] > 0 .

I have tried to do this in the form below, but as shown in the image it displays an error.

sql-server linq

asked by anonymous 05.07.2016 / 21:53

2 answers

3

 var consultaVeiculo = (from e in dm.Tabela_SegVeiculos
            join r in dm.Tabela_VeiculoRefers
            on new { id = e.Modelo, verdadeiro = true } equals new { id = r.Cod_referencia, verdadeiro = r.Cod_referencia > 0}
            into veicRef
            from Referencia in veicRef.DefaultIfEmpty()
            where e.Seguradora == idSeguradora
            orderby e.Modelo_cia
            select new { Descricao = Referencia == null ? "" : Referencia.Descricao, e });

05.07.2016 / 22:04

"Carrying" variables between functions, does strange character appear in place? Why does Ruby have two send and __send__ methods?

score 5 · Accepted Answer

In this case, do not use in Join . Use no Where . Linq is not SQL:

var consultaVeiculo = (from e in dm.Tabela_SegVeiculos
                                   join r in dm.Tabela_VeiculoRefers
                                   on e.Modelo equals r.Cod_referencia 
                                   into veicRef
                                   from Referencia in veicRef.DefaultIfEmpty()
                                   where e.Seguradora == idSeguradora && r.Cod_referencia > 0                                      
                                   orderby e.Modelo_cia
                                   select new { Descricao = Referencia == null ? "" : Referencia.Descricao, e });

Or, according to the problem of the author:

var consultaVeiculo = (from e in dm.Tabela_SegVeiculos
                                   join r in (from ri 
                                              in dm.Tabela_VeiculoRefers 
                                              where ri.Cod_referencia > 0 
                                              select ri)
                                   on e.Modelo equals r.Cod_referencia 
                                   into veicRef
                                   from Referencia in veicRef.DefaultIfEmpty()
                                   where e.Seguradora == idSeguradora && r.Cod_referencia > 0                                      
                                   orderby e.Modelo_cia
                                   select new { Descricao = Referencia == null ? "" : Referencia.Descricao, e });