带附加字段的多对多关联表LINQ左连接查询求助

方式1：利用EF Core导航属性（推荐） #

你的实体类已定义完整的导航关系，EF Core会自动处理左连接逻辑，直接通过投影即可获取所有投诉（含未分配用户的）及其关联的分配用户列表：

var result = _context.Complaints
    .Select(complaint => new 
    {
        // 投诉核心信息
        ComplaintId = complaint.Id,
        complaint.Name,
        complaint.Email,
        ComplaintContent = complaint.Complaint,
        // 关联的分配用户，无分配时返回空集合
        AssignedUsers = complaint.asignComplaintToUsers
            .Select(assignment => new 
            {
                UserId = assignment.applicationUser.Id,
                assignment.applicationUser.FirstName,
                assignment.applicationUser.LastName,
                assignment.applicationUser.Email
            })
            .ToList()
    })
    .ToList();

若需确保导航属性被提前加载，可结合Include和ThenInclude（投影方式仍为更优选择，避免加载冗余数据）：

var result = _context.Complaints
    .Include(c => c.asignComplaintToUsers)
        .ThenInclude(acu => acu.applicationUser)
    .Select(complaint => new 
    {
        ComplaintId = complaint.Id,
        complaint.Name,
        complaint.Email,
        ComplaintContent = complaint.Complaint,
        AssignedUsers = complaint.asignComplaintToUsers
            .Select(assignment => new 
            {
                UserId = assignment.applicationUser.Id,
                assignment.applicationUser.FirstName,
                assignment.applicationUser.LastName,
                assignment.applicationUser.Email
            })
            .ToList()
    })
    .ToList();

方式2：显式左连接LINQ查询 #

如果需要手动控制左连接逻辑，可使用以下写法：

var result = from complaint in _context.Complaints
             join assignment in _context.AsignComplaintToUsers
                 on complaint.Id equals assignment.ComplaintId into assignmentGroup
             from assignment in assignmentGroup.DefaultIfEmpty()
             join user in _context.Users
                 on assignment?.AsignToId equals user.Id into userGroup
             from user in userGroup.DefaultIfEmpty()
             group new { assignment, user } by complaint into complaintGroup
             select new 
             {
                 ComplaintId = complaintGroup.Key.Id,
                 complaintGroup.Key.Name,
                 complaintGroup.Key.Email,
                 ComplaintContent = complaintGroup.Key.Complaint,
                 AssignedUsers = complaintGroup
                     .Where(item => item.user != null)
                     .Select(item => new 
                     {
                         UserId = item.user.Id,
                         item.user.FirstName,
                         item.user.LastName,
                         item.user.Email
                     })
                     .Distinct()
                     .ToList()
             };

关键说明 #

• 方式1依赖EF导航属性，代码简洁且EF会自动优化SQL执行效率，是生产环境的首选方案。
• 方式2通过DefaultIfEmpty()实现左连接，确保无分配用户的投诉也被返回；分组后通过Where过滤空用户、Distinct避免同一用户重复显示（若同一投诉多次分配给同一用户）。
• 注意实体类中AsignComplaintToUsers的拼写（标准应为AssignComplaintToUsers），建议修正笔误以提升代码可读性。

内容的提问来源于stack exchange，提问作者Fawad