如何使用Entity Framework实现动态列数查询（SQL Server端执行）

EF6 基于强类型LINQ实现，无法直接通过原生LINQ方法生成动态列的透视查询，要保证所有聚合计算在SQL Server端执行、不加载全量明细到内存，可以按以下步骤实现：

步骤1：轻量获取全量服务维度 #

这一步仅查询服务维度表，数据量极小，不会产生额外性能开销，不需要拉取考勤标记明细表的全量数据：

// 查询直接在数据库执行，仅返回服务ID、名称两个字段
var allServices = dbContext.Servicios
    .Select(s => new { s.ServicioId, s.ServicioNombre })
    .ToList();

步骤2：动态拼接SQL Server原生PIVOT查询 #

利用SQL Server内置的PIVOT行转列语法，基于上一步拿到的服务列表动态生成透视列，整个聚合计算逻辑完全在数据库引擎内完成：

// 构造透视列片段，处理特殊字符避免SQL语法错误
var pivotColList = new List<string>();
var selectColList = new List<string>();
foreach (var service in allServices)
{
    string safeColName = $"[{service.ServicioNombre.Replace("]", "]]")}]";
    pivotColList.Add(safeColName);
    // 空值补0，和报表展示逻辑对齐
    selectColList.Add($"ISNULL({safeColName}, 0) AS {safeColName}");
}
string pivotColumns = string.Join(", ", pivotColList);
string selectServiceColumns = string.Join(", ", selectColList);

// 拼接完整查询SQL，保留原有空成本中心的处理逻辑
string pivotSql = $@"
SELECT 
    CenterName AS Centers,
    {selectServiceColumns},
    Total
FROM (
    SELECT 
        CASE WHEN m.CentroCostoNombre IS NULL OR LTRIM(RTRIM(m.CentroCostoNombre)) = '' 
            THEN '(sin centro de costo)' 
            ELSE m.CentroCostoNombre 
        END AS CenterName,
        s.ServicioNombre,
        COUNT(*) OVER (PARTITION BY m.CentroCostoId) AS Total,
        m.MarcacionId -- 替换为marcaciones表的实际主键字段即可
    FROM marcaciones m
    LEFT JOIN Servicios s ON m.ServicioId = s.ServicioId
) src
PIVOT (
    COUNT(MarcacionId)
    FOR ServicioNombre IN ({pivotColumns})
) pvt
ORDER BY Centers";

步骤3：执行查询绑定报表 #

因为结果列是动态生成的，直接通过数据读取器加载结果即可，全程不会把明细数据拉到内存处理：

var reportTable = new DataTable();
// 复用EF现有数据库连接，不需要新建连接
if (dbContext.Database.Connection.State != ConnectionState.Open)
{
    dbContext.Database.Connection.Open();
}
using (var cmd = dbContext.Database.Connection.CreateCommand())
{
    cmd.CommandText = pivotSql;
    using (var reader = cmd.ExecuteReader())
    {
        reportTable.Load(reader);
    }
}
// 直接将reportTable绑定到报表控件即可

方案说明 #

• 所有聚合、透视计算均在SQL Server端完成，没有加载marcaciones表全量明细到内存，性能和原有分组查询基本一致
• 服务列完全根据数据库中实际存在的服务动态生成，不需要提前预知服务数量和名称
• 保留原有空成本中心显示为(sin centro de costo)的逻辑，空服务计数自动补0，和预期报表格式完全对齐
• 自动处理服务名称中的特殊字符，避免SQL语法错误

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