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