c# - LINQ query for finding Parent by Child -

i'm new ef\linq. how rewrite such sql query in linq:

select pacient_id visitdates id in  (select visitdate_id    reviews comments n'%name%') 

problem can't access colums created ef in linq query (visitdate_id, pacient_id) i'v tried this, slow:

list<pacient> found = new list<pacient>();             list<pacient> pacients = db.pacients.include(p => p.visits.select(w => w.reviews)).tolist();             bool k = false;             foreach (pacient p in pacients)             {                 foreach (visitdate date in p.visits)                 {                     foreach (review r in date.reviews)                     {                         if (r.comments.tolower().contains(name.tolower()))                         {                             found.add(p);                             k = true;                             break;                         }                     }                     if (k)                     {                         k = false;                         break;                     }                 }             } 

thanks!

this sql query asked for:

var results=db.visitdates   .where(vd=>vd.reviews.any(r=>r.comments.contains("name")))   .select(vd=>vd.pacient_id); 

.tolower() stuff isn't required (usually) because comparison handled database, default case insensitive comparisons, , adding them may cause query run slower.

if want pacients, it'd be:

var results=db.pacients   .where(p=>p.visitdates.any(vd=>vd.reviews.any(r=>r.comments.contains("name"))));