将关联4张表的SQL左连接查询转换为C# LINQ方法语法咨询

实现方案 #

步骤1：定义自定义返回DTO

因为需要同时返回多张表的字段，无法直接复用Device实体，需先定义数据传输对象：

public class DeviceWithLoanInfoDto
{
    // Devices表全量字段
    public int Id { get; set; }
    public int ProgramID { get; set; }
    public string Type { get; set; }
    public string Serial_Number { get; set; }
    public string Label { get; set; }
    public decimal Price { get; set; }
    public DateTime Purchase_Date { get; set; }
    public string Device_Status { get; set; }
    
    // Persons表借出人员字段
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    
    // Programs表项目字段
    public string Program_Name { get; set; } = string.Empty;
    // 其余需要的Programs表字段可自行补充
}

步骤2：LINQ方法语法实现（等效原生SQL）

var deviceList = _context.Devices
    // 左连接Programs表
    .GroupJoin(
        _context.Programs,
        ds => ds.ProgramID,
        p => p.Id,
        (ds, pGroup) => new { ds, Program = pGroup.FirstOrDefault() }
    )
    // 左连接DeviceLoan借阅记录表
    .GroupJoin(
        _context.DeviceLoan,
        joinRes => joinRes.ds.Id,
        l => l.device_id,
        (joinRes, lGroup) => new { joinRes.ds, joinRes.Program, Loan = lGroup.FirstOrDefault() }
    )
    // 左连接Persons人员表
    .GroupJoin(
        _context.Persons,
        joinRes => joinRes.Loan.person_id,
        ps => ps.Id,
        (joinRes, psGroup) => new { joinRes.ds, joinRes.Program, joinRes.Loan, Person = psGroup.FirstOrDefault() }
    )
    // 过滤条件：指定设备类型、指定项目ID
    .Where(res => res.ds.Type == typeName && res.Program.Id == id)
    // 映射到DTO对象
    .Select(res => new DeviceWithLoanInfoDto
    {
        // 赋值Devices全量字段
        Id = res.ds.Id,
        ProgramID = res.ds.ProgramID,
        Type = res.ds.Type,
        Serial_Number = res.ds.Serial_Number,
        Label = res.ds.Label,
        Price = res.ds.Price,
        Purchase_Date = res.ds.Purchase_Date,
        Device_Status = res.ds.Device_Status,
        // 空值处理等效SQL的COALESCE，无借阅人时返回空字符串
        FirstName = res.Person != null ? res.Person.first_name : string.Empty,
        LastName = res.Person != null ? res.Person.last_name : string.Empty,
        Program_Name = res.Program != null ? res.Program.program_name : string.Empty
    })
    .ToList();

关键说明 #

你原有代码的错误主要有两点：

1. 使用内连接而非左连接，导致未借出的设备直接被过滤
2. 关联Persons表时关联条件写错，将l.person_id = ps.Id误写为l.device_id = ps.Id，导致关联结果异常

上述实现通过GroupJoin + FirstOrDefault实现左连接效果，可覆盖指定项目下所有已借出、未借出的设备，空值处理逻辑完全对齐原生SQL的COALESCE函数。

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