EF 无法从#temp 表中选择的存储过程推断返回模式 | 表中选择的存储过程推断返回模式

本文介绍了EF 无法从#temp 表中选择的存储过程推断返回模式的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

假设如下:

CREATE PROCEDURE [MySPROC]
AS
BEGIN

CREATE TABLE #tempSubset(
    [MyPrimaryKey] [bigint]  NOT NULL,
    [OtherColumn]  [int]     NOT NULL)

INSERT INTO #tempSubset (MyPrimaryKey, OtherColumn)
    SELECT SomePrimaryKey, SomeColumn
    FROM   SomeHugeTable
    WHERE  LimitingCondition = true

SELECT MyPrimaryKey, OtherColumn
FROM   #tempSubset
WHERE  SomeExpensiveCondition = true

END

当我生成函数导入或映射返回类型时，EF 不会生成复杂类型或告诉我:

When I generate a function import or map a return type, EF doesn't generate a complex type or tells me:

所选的存储过程或函数不返回任何列

如何克服这个问题?

其他答案建议使用表变量(出于性能原因不打算这样做)伪造返回模式并注释掉真正的存储过程，其他建议对视图做类似的事情...但必须有一种方法可以做到这一点，而不必增加不必要的开销或要求我中断更新模型的存储过程?

Other answers suggest using table variables (not going to do this for performance reasons) faking the return schema and commenting out the real stored procedure, other suggest doing similar with views... but there must be a way to do this without having to add unnecessary overhead or requiring me to break a stored procedure to update the model?

推荐答案

CREATE PROCEDURE [MySPROC]
AS
BEGIN

--supplying a data contract
IF 1 = 2 BEGIN
    SELECT
        cast(null as bigint)  as MyPrimaryKey,
        cast(null as int)    as OtherColumn
    WHERE
        1 = 2
END

CREATE TABLE #tempSubset(
    [MyPrimaryKey] [bigint]  NOT NULL,
    [OtherColumn]  [int]     NOT NULL)

INSERT INTO #tempSubset (MyPrimaryKey, OtherColumn)
    SELECT SomePrimaryKey, SomeColumn
    FROM   SomeHugeTable
    WHERE  LimitingCondition = true

SELECT MyPrimaryKey, OtherColumn
FROM   #tempSubset
WHERE  SomeExpensiveCondition = true

END

为结果集提供虚假数据契约是解决问题的最简单、最干净、最快捷的方法.同样的问题也存在于 SSIS 中的数据源控件中..NET 将从查询的无法访问的合同"部分读取结果集，并为复杂类型提供元数据.没有性能影响，也不需要注释掉执行实际工作的 SQL.

Supplying a faux data contract for the result set is the easiest, cleanest and fastest way to take care of the issue. This same problem exists in data source controls in SSIS too. .NET will read the result set from the unreachable "contract" section of the query and supply the metadata for the complex type. No performance impact and no need to comment out the SQL that does the actual work.

这篇关于EF 无法从#temp 表中选择的存储过程推断返回模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！