实体框架核心:跨多个多对多导航属性的查询

我需要一些帮助来创建跨多个多对多导航属性的“内部连接”。目标是 TasksController 返回分配给登录用户的任务列表。

Task 具有1对1导航属性 Project . 项目 与有多对多关系 Group 具有派生连接实体。 集团 与有多对多关系 User 还具有派生连接实体。

public class Task 
{
    public int? ProjectID
    public Project Project
}

public class Project 
{
    public ICollection<Group> ProjectGroups
    public ICollection<Task> ProjectTasks
}

public class Group 
{
    public ICollection<Project> GroupProjects
    public ICollection<User> GroupMembers
}

public class User 
{
    public ICollection<Group> GroupMemberships
}

如果我使用 .Include 和 .ThenInclude ,查询返回结果,但它创建 LEFT JOIN s,它不会按用户筛选结果。。。

var tasks = from t in _context.Task
                              .Include(t => t.Project)
                              .ThenInclude(p => p.ProjectGroups)
                              .ThenInclude(g => g.GroupMembers.Where(u => u.Login == "username"))
            select t;

如果我使用 .Join ,这将创建 INNER JOIN ,但我不知道如何弥合多对多的关系。。。

var tasks = from t in _context.Task
            // This works fine
            .Join(_context.Project, 
                  task => task.ProjectID, 
                  project => project.ID, 
                  (task, project) => new { Task = task, Project = project }
                 ) 
            // This does not, can't figure out how to get from the ProjectGroups collection to Group
            .Join(_context.Group, 
                  taskProject => taskProject.Project.ProjectGroups.Select(pg => pg.ID),
                  group => group.ID,
                  (taskProject, group) => new { Task = taskProject.Task, Project = taskProject.Project, Group = group }
                 )
            select t;

任何帮助都将不胜感激!