entity-framework - EF 无法从 #temp 表中选择的存储过程推断返回架构

Question

假设如下:

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 不会生成复杂类型或告诉我:

The selected stored procedure or function returns no columns

如何克服这个问题？

其他答案建议 using table variables (出于性能原因不会这样做) faking the return schema and commenting out the real stored procedure ，其他答案建议 doing similar with views ...但必须有一种方法可以做到这一点，而不必增加不必要的开销或要求我破坏存储过程来更新型号？

Answer 1

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。