Double join query returning multiple identical records instead of unique ones

Question

I am trying to get the teams a person belongs to using this query. The problem is that it returns the teams of all the departments of the team the person is in (I get multiple identical records back). I guess I have to replace the .contains but I can't figure out with what as I'm a complete newb and I can't find any helpful examples with double joins. What do I have to change to make it work as intended? Thanks in advance.

public IQueryable<Team> GetTeamsByPersonID(int id)
    {
        return from t in entities.Teams
               join d in entities.Departments
                on t.TeamID equals d.TeamID
               where (from p in entities.Person_Departments
                      join dep in entities.Departments
                      on p.DepartmentID equals dep.DepartmentID
                      where p.PersonID == id
                      select dep.TeamID).Contains(d.TeamID)
               select t;
    }

Yahia · Accepted Answer · 2011-08-27 19:31:25Z

4

try

return (
from t in entities.Teams
from d in entities.Departments
from p in entities.Person_Departments
where t.TeamID == d.TeamID && p.DepartmentID == d.DepartmentID && p.PersonID == id
select t
).Distinct();

answered Aug 27, 2011 at 19:31

Yahia

70.5k9 gold badges117 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Double join query returning multiple identical records instead of unique ones

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related