c# - The correct way to specify a sub query of a subquery using linq -

results = (from r in results  r.buildings.any(x=>x.structuralelements.any(s=>s.value == model.bedrooms.tostring() && s.category=="rm")) select r); 

i think i'm missing joins here. maybe implied? execution runs long can't watch evaluate generated query expression

the biggest problem in query this:

--@p1 = models.bedrooms.tostring() --@p2 = "rm" select * results r exists     (select x.* results tr join buildings x on tr.someid=x.someid tr.id = r.id , exists         (select s.* structuralelements s join buildings tx on tx.otherid = s.otherid tx.id=x.id , s.value = @p1 , s.category = @p2)) 

do see why bad? every result, you're running subquery (which in running subquery). going exponential increase in time/processing start adding things @ root levels (results , buildings) because of these nested subqueries. best bet use joins , distinct r values after you're done. sql like this:

select distinct      r.*      results r     inner join buildings x on x.someid = r.someid     inner join structuralelements s on s.otherid = r.otherid     s.value = @p1 , s.category = @p2 

the reason work when join, if there more 1 join back, duplicate original row. following illustration shows

ids r     x     s 1     -     - join x 1     1     - 1     2     - 1     3     - join s 1     1     1 1     1     2 1     2     5 1     2     6 

assuming s=2 , s=6 meet criteria, return (in r,x,s form) rows 1,1,2 , 1,2,6. getting distinct r in case return r=1, you're trying accomplish. using ef, relationships exist, don't need extra, reference columns you're trying filter by:

results =  (from r in results             x in r.buildings             s in x.structuralelements             s.value == model.bedrooms.tostring() && s.category=="rm"             select r).distinct(); 

this selectmany operator @ play (which takes collection , flattens out subcollections single collection).