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

Solution 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

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.

Solution 2:

Adding this to the top of the stored procedure definition:

SET FMTONLY OFF

Example:

SET FMTONLY OFF

CREATE TABLE #tempTable (
    ...
)

...

SELECT * FROM #tempTable 

Solution 3:

Solution 1 Use a table variable instead of a temporary table.

Solution 2 Use the Set FMTONLY off; SQL command in the procedure and you will get the column information to create a new complex type.

Solution 3 This is not a good way, but it's a very easy way. Just add a select statement with dummy data and it will not execute because 1=0.

you can check details on this link