c# - .NetCore 查询 - 连接多对多表并返回结果并包含带有字符串数组的键

Question

拜托，在使用 .netCore 时，我在处理多对多关系时遇到了一些挑战。我有这个案例；一个用户可以属于多个部门，一个部门可以有多个用户。最初，我在实现多对多关系时遇到了一些挑战。但经过大量研究，我能够得到解决方案。现在，加入表格以获取所有将管理作为其部门一部分的用户。我对如何在查询时将用户部门放入自己的数组感到有些困惑

这是我到目前为止的课程

// User Class
    public class User
    {
        public long Id { get; set; }     
        public string FirstName { get; set; }
        public string LastName { get; set; }       
        public virtual ICollection<UserDepartment> UserDepartments { get; set; }
    }

// Department Class

public class Department
{
    public int Id { get; set; }
    public string DepartmentName { get; set; }
    public virtual ICollection<UserDepartment> UserDepartments { get; set; }
}

// UserDepartment Class (Intermediary Table)

public class UserDepartment
{
    public long UserId { get; set; }
    public int DepartmentId { get; set; }
    public User User { get; set; }
    public Department Department { get; set; }
}

这是我的愿望输出；

[
{
    "userId": 1,
    "fName": "John",
    "lName": "Doe",
    "depts": ["Administration"]
},
{
    "userId": 2,
    "fName": "Simon",
    "lName": "Simpson",
    "depts": ["Administration", "Support"]
},

]

这是我自己尝试的查询

  public async Task<IEnumerable<object>> Test()
  {
        var result = await _context.Users
             .Select(p => new
             {
                 UserId = p.Id,
                 FName = p.FirstName,
                 LName = p.LastName,
                 Depts = //TODO - To fix getting user depts p.UserDepartments.Any(d => d.Department.DepartmentName == "Administration")
             }).ToListAsync();

        return result;

  }

标签： c#entity-frameworklinqasp.net-core

Answer 1

您可以使用 .net 核心中提供的 include 方法来修复获取用户部门，就像这样：

public async Task<IEnumerable<object>> Test()
{
    var result = await _context.Users.Include(s => s.UserDepartments).ThenInclude(n => n.Department).Select(p => new 
    {
            UserId = p.Id,
            FName = p.FirstName,
            LName = p.LastName,
            Depts = p.UserDepartments.Select(s=>s.Department).Select(k => k.DepartmentName)

    }).ToListAsync();        
    return result;
}