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

Question

How can I add one more condition to a Join ?

I have the consultation 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 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 it right after the ON [t0].[Modelo] = ([t2].[Cod_referencia]) be added the and [t2].[Cod_referencia] > 0.

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

Answer 1

For this case, do not use Join. Use in the 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 even, according to the author’s problem:

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 });

Answer 2

 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 });