select fieldA from TableA
where fieldA = 459249 and
fieldB in (
select FieldC
from TableB
where FieldD in
(
select cte.FieldE
from TableC cte
where key= 'ABDC123'
)
)
I got to the point below, the project does not compile:
TableA.Where(x => x.FieldA == 459249)
.Select(x => x.FieldA .Contains(TableB.Select(i => i.FieldB).Contains
(
TableC.Where(c => c.FieldC== "ABDC123").Select(c => c.FieldD).ToString()
)
));
This because Select brings a field that is not exactly IEnumerable . Contains only exists for IEnumerable s.
First, replace the first two Select with SelectMany (which joins all the selected elements in a IEnumerable ).
Try not to use Contains out of Where . In this case, Intersect works best.
TableA.Where(x => x.FieldA == 459249)
.SelectMany(x => x.FieldA).Intersect(TableB
.SelectMany(i => i.FieldB)
.Intersect(TableC
.Where(c => c.FieldC == "ABDC123")
.Select(c => c.FieldD))
)
);
I believe that this way you can achieve:
var resultado = from tableA in TableA
join tableB in TableB on tableA.FieldB equals tableB.FieldC
join tableC in TableC on tableB.FieldD equals TableC.FieldE
where tableA.FieldA == 459249 && tableC.key = "ABDC123"
select tableA.FieldA;
Note: I have not been able to test this code yet.