如何通过单次Lambda查询根据DriverType关联三表获取DriverName

嘿，这个需求我之前做类似业务的时候碰到过，用LINQ的Lambda表达式结合条件关联/合并查询就能实现一次查询搞定。下面给你具体的思路和几种可行的代码方案：

我们需要根据Delivery表中的DriverType字段值，动态关联对应的驱动表（InternalDriver或ExternalDriver）获取DriverName。关键是要把两种情况的查询结果合并，确保最终只触发一次数据库查询。

首先假设你的实体类定义大概是这样的（方便理解）：

public class Delivery
{
    public int Id { get; set; }
    public string DriverType { get; set; } // 取值为"Internal"或"External"
    public int DriverId { get; set; } // 关联对应驱动表的主键ID
    // 其他业务字段...
}

public class InternalDriver
{
    public int Id { get; set; }
    public string DriverName { get; set; }
    // 其他字段...
}

public class ExternalDriver
{
    public int Id { get; set; }
    public string DriverName { get; set; }
    // 其他字段...
}

方案一：左连接+合并结果（推荐，无N+1问题） #

这个方案通过分别对两种DriverType做左连接，再用Union合并结果，EF会把整个逻辑转化为一条带UNION的SQL语句，确保只执行一次数据库查询：

// 处理Internal类型的配送记录，左连接InternalDriver表
var internalDeliveries = dbContext.Deliveries
    .Where(d => d.DriverType == "Internal")
    .GroupJoin(dbContext.InternalDrivers,
               delivery => delivery.DriverId,
               internalDriver => internalDriver.Id,
               (d, drivers) => new { Delivery = d, Drivers = drivers })
    .SelectMany(x => x.Drivers.DefaultIfEmpty(),
                (x, driver) => new 
                {
                    DeliveryId = x.Delivery.Id,
                    DriverType = x.Delivery.DriverType,
                    DriverName = driver?.DriverName // 无匹配时为null
                });

// 处理External类型的配送记录，左连接ExternalDriver表
var externalDeliveries = dbContext.Deliveries
    .Where(d => d.DriverType == "External")
    .GroupJoin(dbContext.ExternalDrivers,
               delivery => delivery.DriverId,
               externalDriver => externalDriver.Id,
               (d, drivers) => new { Delivery = d, Drivers = drivers })
    .SelectMany(x => x.Drivers.DefaultIfEmpty(),
                (x, driver) => new 
                {
                    DeliveryId = x.Delivery.Id,
                    DriverType = x.Delivery.DriverType,
                    DriverName = driver?.DriverName // 无匹配时为null
                });

// 合并两个结果集，得到最终数据
var finalResult = internalDeliveries.Union(externalDeliveries).ToList();

如果你习惯用查询表达式（LINQ Query Syntax），也可以写成这样：

var internalDeliveries = from d in dbContext.Deliveries
                         where d.DriverType == "Internal"
                         join id in dbContext.InternalDrivers
                         on d.DriverId equals id.Id into driverGroup
                         from driver in driverGroup.DefaultIfEmpty()
                         select new 
                         {
                             DeliveryId = d.Id,
                             DriverType = d.DriverType,
                             DriverName = driver?.DriverName
                         };

var externalDeliveries = from d in dbContext.Deliveries
                         where d.DriverType == "External"
                         join ed in dbContext.ExternalDrivers
                         on d.DriverId equals ed.Id into driverGroup
                         from driver in driverGroup.DefaultIfEmpty()
                         select new 
                         {
                             DeliveryId = d.Id,
                             DriverType = d.DriverType,
                             DriverName = driver?.DriverName
                         };

var finalResult = internalDeliveries.Union(externalDeliveries).ToList();

方案二：投影时动态判断（简单直观，但注意N+1问题） #

如果你的数据量不大，或者已经配置了合适的跟踪/缓存，也可以直接在投影的时候根据DriverType动态查询对应的表：

var result = dbContext.Deliveries
    .Select(d => new 
    {
        DeliveryId = d.Id,
        DriverType = d.DriverType,
        DriverName = d.DriverType switch
        {
            "Internal" => dbContext.InternalDrivers.FirstOrDefault(id => id.Id == d.DriverId)?.DriverName,
            "External" => dbContext.ExternalDrivers.FirstOrDefault(ed => ed.Id == d.DriverId)?.DriverName,
            _ => "Unknown" // 处理其他未知类型的情况
        }
    })
    .ToList();

注意：这种方式如果没有正确配置，可能会触发N+1查询（每条Delivery记录单独查一次驱动表），如果数据量较大的话不推荐。

• 如果DriverId可能不存在于对应的驱动表中，一定要用左连接（DefaultIfEmpty()），避免丢失Delivery记录。
• 如果DriverType还有其他取值，记得在判断中添加默认分支，避免出现null或未处理的情况。

内容的提问来源于stack exchange，提问作者Mohammad Monir Hossain