如何在LINQ左连接的条件中嵌入带min()的内联子查询

核心实现逻辑 #

你提供的SQL本质是关联人员表后，左连接该人员下service_code='N'的最小id_service对应的服务记录，等效LINQ可以用查询表达式和链式方法两种方式实现，两种写法都可以被EF/EF Core正确翻译为对应SQL，无额外性能问题。

1. 查询表达式写法（更贴近SQL逻辑，易读性高） #

var query = from person in DbContext.Persons
            // 左连接SERVICE表
            join s in DbContext.Services on 
                new { person.id_person, TargetId = (from s2 in DbContext.Services 
                                                    where s2.id_person == person.id_person && s2.service_code == "N"
                                                    select s2.id_service).Min() } 
                equals new { s.id_person, TargetId = s.id_service } 
                into serviceJoin
            from service in serviceJoin.DefaultIfEmpty()
            // 额外筛选确保service_code=N，和原SQL条件完全对齐
            where service == null || service.service_code == "N"
            select new 
            {
                // 按需填写需要返回的字段
                person.id_person,
                person.Name,
                ServiceInfo = service
            };

2. 链式方法写法（适合偏好流式调用的场景） #

var query = DbContext.Persons
    .GroupJoin(
        DbContext.Services,
        person => new 
        {
            person.id_person,
            MinServiceId = DbContext.Services
                .Where(s => s.id_person == person.id_person && s.service_code == "N")
                .Select(s => s.id_service)
                .Min()
        },
        s => new { s.id_person, MinServiceId = s.id_service },
        (person, serviceJoin) => new { person, serviceJoin }
    )
    .SelectMany(
        x => x.serviceJoin.DefaultIfEmpty(),
        (x, service) => new { x.person, service }
    )
    .Where(x => x.service == null || x.service.service_code == "N");

补充说明 #

• 两种写法都将子查询直接嵌入到连接匹配键的定义中，完全等效你提供的SQL连接逻辑
• 若对应人员不存在service_code='N'的服务记录，返回的service会为null，符合左连接的预期行为

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