如何修正LINQ Lambda多连接查询以实现SQL字符串拼接逻辑？

报错信息 #

System.InvalidOperationException: The LINQ expression 'DbSet<TblObligor>()
    .Join(
        inner: DbSet<TblObligorGuaranties>(), 
        outerKeySelector: t => t.ObligorId, 
        innerKeySelector: t0 => (decimal)t0.ObligorID, 
        resultSelector: (t, t0) => new TransparentIdentifier<TblObligor, TblObligorGuaranties>(
            Outer = t, 
            Inner = t0
        ))
    .Join(
        inner: DbSet<TblObligorGuarantyTypes>(), 
        outerKeySelector: ti => ObligorService.MapObligorGuaranties(
            o: ti.Outer, 
            og: ti.Inner).GuarantyTypeID, 
        innerKeySelector: t1 => (Nullable<int>)t1.GuarantyTypeID, 
        resultSelector: (ti, t1) => new TransparentIdentifier<TransparentIdentifier<TblObligor, TblObligorGuaranties>, TblObligorGuarantyTypes>(
            Outer = ti, 
            Inner = t1
        ))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

需求 #

实现GuarantyTypeDescription字段的字符串拼接列，效果与原SQL一致。

原SQL查询 #

select 
  o2.ObligorID, 
  STUFF(
    (
      select 
        ',' + cast(
          ogt.GuarantyTypeDescription as nvarchar
        ) 
      from 
        tblObligor o 
        left join tblObligorGuaranties og on o.ObligorId = og.ObligorID 
        left join tblObligorGuarantyTypes ogt on og.GuarantyTypeID = ogt.GuarantyTypeID 
      where 
        1 = 1 
        and o.ObligorID = o2.ObligorID 
        and o.assetid = 1996323923 for xml path('')
    ), 
    1, 
    1, 
    ''
  ) as xmlstring 
from 
  tblObligor o2 
where 
 1 = 1 
 and o2.assetid = 1996323923

当前代码 #

public async Task<IEnumerable<ObligorGuarantyDTO>> GetObligorsListAsync(int? assetId)
{
    var obligorGuarantiesList = _context.TblObligor
       .Join(_context.TblObligorGuaranties, o => o.ObligorId, og => og.ObligorID, (o, og) => new { o, og })
       .Select(join => MapObligorGuaranties(join.o, join.og))
       .Join(_context.TblObligorGuarantyTypes, og => og.GuarantyTypeID, ogt => ogt.GuarantyTypeID, (og, ogt) => new { og, ogt })
       .Select(join => MapObligorGuarantyTypes(join.og, join.ogt))
       .AsEnumerable();

    return obligorGuarantiesList;
}

映射方法 #

private static ObligorGuarantyDTO MapObligorGuaranties(TblObligor o, TblObligorGuaranties og)
=> new ObligorGuarantyDTO()
{
    ObligorID = o.ObligorId,
    GuarantyID = og.GuarantyID,
    GuarantyTypeID = og.GuarantyTypeID,
    Release = og.Release,
    ReleaseDate= og.ReleaseDate,
    Note= og.Note,
    EditBy = og.EditBy,
    EditTime= og.EditTime
};

private static ObligorGuarantyDTO MapObligorGuarantyTypes(ObligorGuarantyDTO og, TblObligorGuarantyTypes ogt)
=> new ObligorGuarantyDTO()
{
    GuarantyTypeID = ogt.GuarantyTypeID,
    GuarantyTypeDescription = String.Join(", ", ogt.GuarantyTypeDescription)
};

问题分析 #

1. EF无法解析自定义映射方法：在LINQ查询中直接调用MapObligorGuaranties这类自定义方法，EF Core无法将其转换为对应的SQL语句，这是报错的直接原因。
2. 拼接逻辑错误：映射方法中的String.Join(", ", ogt.GuarantyTypeDescription)完全不符合需求——String.Join需要传入集合参数，这里传入单个字符串没有任何拼接效果，且原SQL是按ObligorID分组拼接所有关联的担保类型描述，当前代码没有分组逻辑。
3. 查询逻辑与原SQL不符：原SQL是先过滤资产ID，再按ObligorID分组子查询拼接，当前代码只是简单关联三张表，没有实现分组聚合逻辑。

修正方案 #

方案1：使用EF Core 6+的服务器端拼接（推荐） #

如果使用EF Core 6及以上版本，可以直接使用StringJoin方法实现服务器端的字符串拼接，性能最优：

public async Task<IEnumerable<ObligorGuarantyDTO>> GetObligorsListAsync(int? assetId)
{
    return await _context.TblObligor
        .Where(o => o.AssetId == assetId)
        .Select(o => new ObligorGuarantyDTO
        {
            ObligorID = o.ObligorId,
            // 利用导航属性关联，直接拼接同Obligor下的所有担保类型描述
            GuarantyTypeDescription = o.TblObligorGuaranties
                .Join(_context.TblObligorGuarantyTypes, 
                      og => og.GuarantyTypeID, 
                      ogt => ogt.GuarantyTypeID, 
                      (og, ogt) => ogt.GuarantyTypeDescription)
                .StringJoin(", ")
            // 补充其他字段：如果需要DTO的其他字段，直接从o和关联表中取值即可
        })
        .ToListAsync();
}

注：如果实体类没有配置导航属性，可以改用显式关联的方式查询。

方案2：无导航属性的服务器端分组拼接 #

如果实体类没有配置导航属性，可先关联三张表再分组聚合：

public async Task<IEnumerable<ObligorGuarantyDTO>> GetObligorsListAsync(int? assetId)
{
    var query = from o in _context.TblObligor
                join og in _context.TblObligorGuaranties on o.ObligorId equals og.ObligorID
                join ogt in _context.TblObligorGuarantyTypes on og.GuarantyTypeID equals ogt.GuarantyTypeID
                where o.AssetId == assetId
                // 按ObligorID分组，聚合担保类型描述
                group ogt.GuarantyTypeDescription by o.ObligorId into grouped
                select new ObligorGuarantyDTO
                {
                    ObligorID = grouped.Key,
                    GuarantyTypeDescription = string.Join(", ", grouped)
                };

    return await query.ToListAsync();
}

方案3：客户端分组拼接（兼容旧版EF） #

如果使用的EF Core版本不支持服务器端字符串拼接，可以先将原始数据拉取到客户端，再分组拼接：

public async Task<IEnumerable<ObligorGuarantyDTO>> GetObligorsListAsync(int? assetId)
{
    // 先查询所有关联的原始数据，拉到客户端
    var rawData = await _context.TblObligor
        .Where(o => o.AssetId == assetId)
        .Join(_context.TblObligorGuaranties, o => o.ObligorId, og => og.ObligorID, (o, og) => new { o.ObligorId, og.GuarantyTypeID })
        .Join(_context.TblObligorGuarantyTypes, og => og.GuarantyTypeID, ogt => ogt.GuarantyTypeID, (og, ogt) => new { og.ObligorId, ogt.GuarantyTypeDescription })
        .ToListAsync();

    // 客户端分组拼接
    return rawData
        .GroupBy(item => item.ObligorId)
        .Select(group => new ObligorGuarantyDTO
        {
            ObligorID = group.Key,
            GuarantyTypeDescription = string.Join(", ", group.Select(item => item.GuarantyTypeDescription))
            // 补充其他字段可从原始数据中提取，或关联查询时一并获取
        });
}

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