LINQ查询连接表中附加字段的多对多关系

Question

我想从投诉中获得所有记录，无论是分配给用户还是不分配给用户，以及投诉分配给的所有用户的列表，每个投诉使用LINQ查询左连接分组。这些表有多对多关系表，附加字段如日期等。我试了很多，如果有人帮助我这个问题。提前感谢

public class Complaint
{
[Key]
public int Id { get; set; }
[Required]
public string? Name { get; set; }
[Required]
public string? Email { get; set; }
[Required]
public string? Complaint{ get; set; }
public ICollection<AsignComplaintToUsers> asignComplaintToUsers { get; set; }
}
public class ApplicationUser : IdentityUser
{
[Column(TypeName = "nvarchar(100)")]
public string? FirstName { get; set; }
[PersonalData]
[Column(TypeName = "nvarchar(100)")]
public string? LastName { get; set; }
public ICollection<AsignComplaintToUsers> asignComplaintToUsers { get; set; }
}

public class AsignComplaintToUsers
{
[System.ComponentModel.DataAnnotations.Key]
public int Id { get; set; }
public int ComplaintId { get; set; }
[ForeignKey("ComplaintId")]
public Complaint complaint { get; set; }

public string? AsignToId { get; set; }
[ForeignKey("AsignTo")]
public ApplicationUser applicationUser { get; set; }
public string? AsignById { get; set; }
}

Answer 1

用这个查询我解决了这个问题

var xyz = (from c in _context.complaints
join AC in _context.asignComplaintToUsers
on c.Id equals AC.ComplaintId into temp
from t in temp.DefaultIfEmpty()
join AppUser in _context.applicationUsers
on t.AsignTo equals AppUser.Id into temp2
from t2 in temp2.DefaultIfEmpty()
select new
{
complaint = c,
asugnSugessionTousers = t,
usersWhoAsignedComplaints = t2
}).ToList();