How To Add Temp Table Support In Entity Framework

When a Temp table is defined in a procedure, it cannot be accessed in the entity framework. This is due to the absence of the schema of the temp table in the entity framework. An example is described below.

Introduction 

 

Suppose you define a temp table in a procedure and retrieve the data from the table through the entity framework. It will not be accessible due to the absence of the schema in the EF. Therefore, you should define the table in the database. Below is an example of the procedure:

CREATE PROCEDURE dbo.Demo

AS
BEGIN
    SET NOCOUNT ON;
     IF 1=0 BEGIN
       SET FMTONLY OFF
     END

    CREATE TABLE #Temp
    (
        ProductID   integer NOT NULL,
        Name        nvarchar(50) COLLATE DATABASE_DEFAULT NOT NULL
    );

    INSERT INTO #Temp
        ([ProductID], [Name])
    SELECT
        p.[ProductID], p.[Name]
    FROM Production.Product AS p

    SELECT
        t.[Name], t.[ProductID]
    FROM #Temp AS t

    DROP TABLE #Temp;
END;

Conclusion

 

In the above blog, we studied how to add temp table support in an entity framework.