如何在EDMX文件中添加含临时表与PIVOT的存储过程？

你没法把这个存储过程导入EDMX的核心原因有两个：

1. EF6导入存储过程时，会通过SET FMTONLY ON来推断返回结果的结构，但你的存储过程用了本地临时表#t1——在FMTONLY ON模式下，SELECT INTO #t1不会实际创建临时表，后续查询#t1会直接报错，导致EF无法获取结果元数据。
2. 动态PIVOT生成的结果列是动态变化的，EF无法映射到固定结构的实体或复杂类型，这也是EDMX拒绝导入的关键原因。

方案1：直接在EF中调用存储过程，返回DataTable（适配动态列） #

既然EDMX没法处理动态结果，直接绕开EDMX的导入流程，用ADO.NET的方式执行存储过程并返回DataTable，这样能完整保留动态生成的列。在你的DbContext里添加如下方法：

public DataTable GetEmpTrainings(int id)
{
    var resultTable = new DataTable();
    var connection = Database.Connection;
    
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "EmpTrainings";
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@id", id));

        if (connection.State != ConnectionState.Open)
        {
            connection.Open();
        }

        using (var reader = command.ExecuteReader())
        {
            resultTable.Load(reader);
        }
    }

    return resultTable;
}

在MVC控制器里调用这个方法，就能拿到动态结构的结果，之后可以直接把DataTable转换成IEnumerable<dynamic>或者直接绑定到前端控件。

方案2：修改存储过程为静态PIVOT（如果资格类型固定） #

如果你的Qualification（资格）类型是固定不变的，把动态PIVOT改成静态的CASE WHEN写法，这样存储过程返回固定列，EF就能正常导入到EDMX：

ALTER PROCEDURE EmpTrainings
    @id INT = 0  
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        s.EmpName,
        MAX(CASE WHEN q.Qualification = '安全培训' THEN qs.DateCompleted END) AS 安全培训,
        MAX(CASE WHEN q.Qualification = '操作认证' THEN qs.DateCompleted END) AS 操作认证,
        MAX(CASE WHEN q.Qualification = '消防演练' THEN qs.DateCompleted END) AS 消防演练
    FROM QualStaff qs
    INNER JOIN Qual q ON qs.QualificationID = q.QualificationID
    INNER JOIN Staff s ON qs.StaffID = s.StaffID
    WHERE qs.StatusID = 1 AND qs.Id = @Id
    GROUP BY s.EmpName
END

修改完成后，重新尝试导入EDMX，EF就能识别到固定的结果列，生成对应的复杂类型供你调用。

方案3：调整存储过程让EF能识别临时表（仅适用于固定列场景） #

如果必须保留临时表，但结果列是固定的，可以在存储过程开头明确创建临时表结构，避免FMTONLY ON模式下的报错：

ALTER PROCEDURE EmpTrainings
    @id INT = 0  
AS
BEGIN
    SET NOCOUNT ON;
    SET FMTONLY OFF;

    -- 先定义临时表结构
    CREATE TABLE #t1 (
        Qualification NVARCHAR(100),
        EmpName NVARCHAR(100),
        DateCompleted DATE
    )

    INSERT INTO #t1
    SELECT q.Qualification, s.EmpName, qs.DateCompleted
    FROM QualStaff qs
    INNER JOIN Qual q ON qs.QualificationID = q.QualificationID
    INNER JOIN Staff s ON qs.StaffID = s.StaffID
    WHERE qs.StatusID = 1 AND qs.Id = @Id

    -- 后续动态PIVOT逻辑不变
    DECLARE @cols AS NVARCHAR(max) = STUFF((SELECT DISTINCT ',' + Quotename(Qualification) 
                                            FROM #t1
                                            FOR XML PATH(''), TYPE)
                                         .value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 

    DECLARE @query AS NVARCHAR(max) = ' SELECT * FROM #t1 PIVOT (MAX(DateCompleted) FOR Qualification IN ('+@cols+') ) pvt';

    EXECUTE(@query)

    DROP TABLE #t1
END

不过这个方法还是解决不了动态列的问题，EF依然无法映射动态结果，所以只适合你后续把动态PIVOT改成静态的场景。

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