如何将给定T-SQL代码转换为对应的LINQ与Lambda表达式

注：原代码实际为PostgreSQL语法（使用array_agg、||字符串拼接符），且存在逻辑冗余：左连接后单条places匹配多条sub_places时，分组后array_agg(plc)会返回多个完全重复的places JSON字符串。以下先提供严格对齐原逻辑的实现，再给出符合常规业务场景的优化版本。

• 前置假设：已完成EF Core上下文配置，对应实体结构如下：
  • Places：映射public.places表，包含字段Id(数值型)、Key(字符串)、Text(字符串)、Other(字符串)、Value(字符串)
  • SubPlaces：映射public.sub_places表，包含字段Id(数值型)、Key(字符串)、Text(字符串)、ImgUrl(字符串)

严格对齐原SQL逻辑的LINQ查询语法 #

var query = from plc in dbContext.Places
            join sub in dbContext.SubPlaces 
                on plc.Value equals sub.Key into subMatches
            from matchedSub in subMatches.DefaultIfEmpty() // 实现LEFT JOIN逻辑
            orderby plc.Id
            select new 
            {
                _order = plc.Id,
                PlcJson = $"{{\"id\": {plc.Id}, \"key\": \"{plc.Key}\", \"text\": \"{plc.Text}\", \"other\": \"{plc.Other}\"}}",
                SubJson = matchedSub == null ? null : $"{{\"id\": {matchedSub.Id}, \"key\": \"{matchedSub.Key}\", \"text\": \"{matchedSub.Text}\", \"img_url\": \"{matchedSub.ImgUrl}\"}}"
            } into innerResult
            group innerResult by new { innerResult.PlcJson, innerResult._order } into grouped
            orderby grouped.Key._order
            select new 
            {
                _plc = grouped.Select(x => x.PlcJson).ToArray() // 对应array_agg聚合逻辑
            };

严格对齐原SQL逻辑的Lambda表达式写法 #

var lambdaQuery = dbContext.Places
    .GroupJoin(
        inner: dbContext.SubPlaces,
        outerKeySelector: plc => plc.Value,
        innerKeySelector: sub => sub.Key,
        resultSelector: (plc, subMatches) => new { plc, subMatches }
    )
    .SelectMany(
        collectionSelector: temp => temp.subMatches.DefaultIfEmpty(),
        resultSelector: (temp, matchedSub) => new
        {
            _order = temp.plc.Id,
            PlcJson = $"{{\"id\": {temp.plc.Id}, \"key\": \"{temp.plc.Key}\", \"text\": \"{temp.plc.Text}\", \"other\": \"{temp.plc.Other}\"}}",
            SubJson = matchedSub == null ? null : $"{{\"id\": {matchedSub.Id}, \"key\": \"{matchedSub.Key}\", \"text\": \"{matchedSub.Text}\", \"img_url\": \"{matchedSub.ImgUrl}\"}}"
        }
    )
    .OrderBy(innerResult => innerResult._order)
    .GroupBy(innerResult => new { innerResult.PlcJson, innerResult._order })
    .OrderBy(grouped => grouped.Key._order)
    .Select(grouped => new
    {
        _plc = grouped.Select(x => x.PlcJson).ToArray()
    });

优化版本说明 #

• 逻辑修正：原SQL聚合重复plc字段无实际业务价值，常规场景下应聚合每个places关联的所有sub_places为数组，避免冗余数据
• 安全修正：手动拼接JSON存在特殊字符转义风险，字段包含双引号、反斜杠时会导致JSON格式错误，建议使用官方System.Text.Json组件做序列化
• 优化后代码：

using System.Text.Json;

var optimizedQuery = dbContext.Places
    .GroupJoin(
        inner: dbContext.SubPlaces,
        outerKeySelector: plc => plc.Value,
        innerKeySelector: sub => sub.Key,
        resultSelector: (plc, subMatches) => new
        {
            _order = plc.Id,
            PlcInfo = JsonSerializer.Serialize(new { plc.Id, plc.Key, plc.Text, plc.Other }),
            SubInfos = subMatches.Select(sub => JsonSerializer.Serialize(new { sub.Id, sub.Key, sub.Text, sub.ImgUrl })).ToArray()
        }
    )
    .OrderBy(res => res._order)
    .ToList();

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