EF can't infer return schema from Stored Procedure selecting from a #temp table

entity-framework stored-procedures temp-tables

Question

Suppose the following:

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

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

How to overcome this?

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?

1
39
5/23/2017 12:02:37 PM

Accepted Answer

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

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.

62
8/9/2017 7:22:15 AM

Popular Answer

Adding this to the top of the stored procedure definition:

SET FMTONLY OFF
allowed the model to infer the schema from the temporary table without issue. As a bonus, it doesn't require additional maintenance for a contract.

Example:

SET FMTONLY OFF

CREATE TABLE #tempTable (
    ...
)

...

SELECT * FROM #tempTable 


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow