LINQ实现Oracle LISTAGG功能失败，求非内存解决方案

原SQL查询 #

SELECT  
    M.EMPLOYEEID, LISTAGG(L.NAME, ',') WITHIN GROUP (ORDER BY L.NAME) AS Locations
FROM
    EMPLOYEEOFFICES M 
LEFT JOIN 
    OFFICELIST L ON M.OFFICELISTID = l.officelistid 
GROUP BY 
    M.EMPLOYEEID

尝试的LINQ代码 #

var empOfficeLocations = (from om in _dbContext.EMPLOYEEOFFICES 
                          join ol in _dbContext.Officelists
                                  on om.Officelistid equals ol.Officelistid into grps
                          from grp in grps
                          group grp by om.Employeeid into g
                          select new
                                 {
                                     EmployeeId = g.Key,
                                     Locations = string.Join(",", g.Select(x => x.Name))
                                 }).ToList();

报错信息 #

Processing of the LINQ expression 'GroupByShaperExpression:
KeySelector: e.EMPLOYEEID,
ElementSelector:ProjectionBindingExpression: EmptyProjectionMember
by 'RelationalProjectionBindingExpressionVisitor' failed. This may indicate either a bug or a limitation in Entity Framework.

查询表达式编译结果 #

Compiling query expression:
      'DbSet<EMPLOYEEOFFICES>()
          .Join(
              inner: DbSet<Officelist>(),
              outerKeySelector: e => e.Officelistid,
              innerKeySelector: o => o.Officelistid,
              resultSelector: (e, o) => new {
                  e = e,
                  o = o
               })
          .GroupBy(
              keySelector: <>h__TransparentIdentifier0 => <>h__TransparentIdentifier0.e.Employeeid,
              elementSelector: <>h__TransparentIdentifier0 => <>h__TransparentIdentifier0.o.Name)
          .Select(g => new {
              EMPLOYEEID = g.Key,
              Locations = string.Join(
                  separator: ", ",
                  values: g)
           })

期望结果 #

环境信息 #

• 数据库：Oracle 11g
• Microsoft.EntityFrameworkCore v5.0.9
• Oracle.EntityFrameworkCore v5.21.4

解决方案 #

核心问题 #

原LINQ代码失败的原因是：EF Core 5无法将客户端方法string.Join转换为Oracle的LISTAGG聚合函数，且GroupBy后的投影逻辑无法被正确翻译为数据库端执行的SQL，导致抛出翻译失败异常。

方法1：执行原生SQL查询（推荐） #

直接使用原生SQL执行原查询逻辑，让聚合操作在数据库端完成，完全规避EF Core的翻译限制：

方式1：映射到值元组

var empOfficeLocations = _dbContext.Database.SqlQuery<(string EmployeeId, string Locations)>(@"
    SELECT  
        M.EMPLOYEEID, LISTAGG(L.NAME, ',') WITHIN GROUP (ORDER BY L.NAME) AS Locations
    FROM
        EMPLOYEEOFFICES M 
    LEFT JOIN 
        OFFICELIST L ON M.OFFICELISTID = l.officelistid 
    GROUP BY 
        M.EMPLOYEEID
").ToList();

方式2：映射到DTO类

如果需要强类型对象，先定义数据传输对象：

public class EmployeeLocationDto
{
    public string EmployeeId { get; set; }
    public string Locations { get; set; }
}

再执行查询：

var empOfficeLocations = _dbContext.Set<EmployeeLocationDto>().FromSqlRaw(@"
    SELECT  
        M.EMPLOYEEID, LISTAGG(L.NAME, ',') WITHIN GROUP (ORDER BY L.NAME) AS Locations
    FROM
        EMPLOYEEOFFICES M 
    LEFT JOIN 
        OFFICELIST L ON M.OFFICELISTID = l.officelistid 
    GROUP BY 
        M.EMPLOYEEID
").ToList();

方法2：使用EF.Functions扩展（可选） #

部分Oracle EF Core驱动版本支持通过EF.Functions调用数据库原生函数，但EF Core 5版本的Oracle驱动对LISTAGG的支持有限。若尝试此方法，需查看驱动文档确认函数映射规则，不过原生SQL方法的兼容性和可靠性更高。

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