Join 2 tables and return the resulting one in LINQ

Question

I have 2 tables Patients and Studies that have a shared field called Studyid. I need to get the study name from the Studies table where PatID is a method's parameter. I am joining the tables but I am failing to return the resulting one, so far I am retuning a single table to which I cannot implement the Where condition.

db.Patients.Join(db.Studies, ct => ct.Studyid, st => st.Studyid, (ct, st) => st).Where(p => p.Patientstudyid == PatId).ToList() which obviously won't work since Studies have no patient's information.

How to return the joined table?

Answer 1

I would prefer query syntax when using joins. It is easily to refactor and to understand what query do.

var query = 
    from p in db.Patients
    join st in db.Studies on p.Studyid equals ct.Studyid
    where p.Patientstudyid == PatId
    select st.Name;

var result = query.ToList();