EF不能从存储过程中从#temp表中选择返回模式 [英] EF can't infer return schema from Stored Procedure selecting from a #temp table

问题描述

假设如下：

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:

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

The selected stored procedure or function returns no columns

如何克服这个？

其他答案建议 using table v ariables （由于性能原因不会这样做）伪造退货模式并注释真正的存储过程 ，其他建议与视图相似...但是必须有一种方法可以做到这一点，而不必增加不必要的开销或要求我打破存储过程来更新模型？

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表中选择返回模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！