ASP.NET Core MVC三表关联API开发：Linq实现及架构疑问

一、修正DbContext定义 #

ViewModel是用于展示数据的视图模型，并非数据库表对应的实体类，因此绝对不能在DbContext中为ViewModel声明DbSet。正确的DbContext应该包含三个实体类的DbSet：

public class UserContext : DbContext
{
    public UserContext(DbContextOptions<UserContext> options) : base(options)
    {
    }

    public DbSet<Companies> Companies { get; set; }
    public DbSet<CompanyUsers> CompanyUsers { get; set; }
    public DbSet<PersonalInfo> PersonalInfo { get; set; }
}

另外注意：你的Companies类中属性名compnayid是拼写错误，应该改为companyid以匹配数据库表字段：

[Table("companies")]
public class Companies
{
    public int companyid { get; set; } // 修正拼写错误
    public string companyname { get; set; }
}

同时你提供的SQL语句中存在多处compnayusers拼写错误，需改为companyusers，后续Linq查询会对应正确的实体类。

二、编写对应SQL的Linq查询 #

可以通过Join操作实现多表关联，对应原SQL的逻辑，以下提供两种写法：

方式1：查询语法（更接近SQL结构） #

var query = from cu in _context.CompanyUsers
            join c in _context.Companies on cu.companyid equals c.companyid
            join pi in _context.PersonalInfo on cu.id equals pi.userID
            where cu.companyid == 2 && cu.IsRemote == 0
            orderby cu.name
            select new ViewModel
            {
                companyUsersvm = cu,
                companiesvm = c,
                personalInfovm = pi
            };

var result = await query.ToListAsync();

方式2：方法语法 #

var result = await _context.CompanyUsers
    .Where(cu => cu.companyid == 2 && cu.IsRemote == 0)
    .Join(_context.Companies, 
          cu => cu.companyid, 
          c => c.companyid, 
          (cu, c) => new { cu, c })
    .Join(_context.PersonalInfo, 
          combined => combined.cu.id, 
          pi => pi.userID, 
          (combined, pi) => new ViewModel
          {
              companyUsersvm = combined.cu,
              companiesvm = combined.c,
              personalInfovm = pi
          })
    .OrderBy(vm => vm.companyUsersvm.name)
    .ToListAsync();

三、控制器示例代码 #

假设使用ASP.NET Core Web API，控制器代码如下：

[ApiController]
[Route("api/[controller]")]
public class UserController : ControllerBase
{
    private readonly UserContext _context;

    public UserController(UserContext context)
    {
        _context = context;
    }

    [HttpGet]
    public async Task<IActionResult> GetNonRemoteUsers()
    {
        var result = await _context.CompanyUsers
            .Where(cu => cu.companyid == 2 && cu.IsRemote == 0)
            .Join(_context.Companies, 
                  cu => cu.companyid, 
                  c => c.companyid, 
                  (cu, c) => new { cu, c })
            .Join(_context.PersonalInfo, 
                  combined => combined.cu.id, 
                  pi => pi.userID, 
                  (combined, pi) => new ViewModel
                  {
                      companyUsersvm = combined.cu,
                      companiesvm = combined.c,
                      personalInfovm = pi
                  })
            .OrderBy(vm => vm.companyUsersvm.name)
            .ToListAsync();

        return Ok(result);
    }
}

四、优化建议 #

当前ViewModel包含了实体类的所有属性，而原SQL仅需要返回部分字段。为了更高效且符合API设计规范，建议创建DTO（数据传输对象），只包含需要返回的字段：

public class UserDto
{
    public int CompanyId { get; set; }
    public string CompanyName { get; set; }
    public string UserName { get; set; }
    public int UserId { get; set; }
    public string Designation { get; set; }
    public string OtherInfo { get; set; }
}

然后在Linq查询中直接映射到DTO：

select new UserDto
{
    CompanyId = cu.companyid,
    CompanyName = c.companyname,
    UserName = cu.name,
    UserId = cu.id,
    Designation = cu.designation,
    OtherInfo = pi.otherinfo
}

这样返回的JSON数据会更简洁，避免返回不必要的字段。

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