Query with LINQ, trying to perform a RIGHT JOIN

1

I need to make the following query through LINQ, using the given tables:

You must return all vehicles where the "Type" has the "PART" field equal to "true", the connection of the "Vehicle" table with the "Type" table is done through the "Model" table, but if the vehicle has already been attached to the "Set" table it also should not be displayed.

In this case I need to return vehicles with IDs 51, 66 and 72

Veiculo
-----------------
ID PLACA   IDMODELO
49 AAA0001 13
50 AAA0002 13
51 AAA0003 15
65 AAA0004 19
66 AAA0005 14
71 AAA0006 13
72 AAA0007 19


Modelo
-----------------
ID MODELO   IDTIPO
13 Reboque  22
14 Caminhão 27
15 Carreta  28
19 Bitrem   27


Tipo
-----------------
ID TIPO PARTE
22 V1   false
27 V2   true
28 R1   true


Conjunto
-----------------
IDVEICULOCONJUNTO IDVEICULO
50                65

I tried the following query:

from vei in dc.Veiculo
join mod in dc.ModeloVeiculo on vei.IdModeloVeiculo equals mod.IdModeloVeiculo
join tiv in dc.TipoVeiculo on mod.IdTipoVeiculo equals tiv.IdTipoVeiculo
join vec in dc.VeiculoConjunto on vei.IdVeiculo equals vec.IdVeiculo into jData
from jvei in jData.DefaultIfEmpty()
where tiv.ParteConjunto.Equals(true) &&
    (jvei == null || (idVeiculo == 0 || jvei.IdVeiculo == idVeiculo))
select vei

but it returns IDs 51, 65, 65 and 72

    
asked by anonymous 14.10.2014 / 15:30

1 answer

2

I solved the question as follows:

from vei in dc.Veiculo
join mod in dc.ModeloVeiculo on vei.IdModeloVeiculo equals mod.IdModeloVeiculo
join tiv in dc.TipoVeiculo on mod.IdTipoVeiculo equals tiv.IdTipoVeiculo
join vec in dc.VeiculoConjunto on vei.IdVeiculo equals vec.IdVeiculo into jData
from jvei in jData.DefaultIfEmpty()
where tiv.ParteConjunto.Equals(true) &&
    (jvei == null || (idVeiculo != 0 && jvei.IdVeiculo == idVeiculo))
select vei
    
14.10.2014 / 22:20