Entity Framework Function Import does not work with SP which use temp tables in it

If you ever tried to generate Entity Framework model from database for Stored Procedures (Function Import) - you might have notices that Entity Framework cannot receive information about columns for those SP which uses temp tables in it.

The Reason for that is that EF importer tries to execute that SP with FMTONLY set to ON, which makes you SP fail, as temp tables are not created when FMTONLY=ON.

In order to make those SP work, you can make the following changes:
1) Add to the beginning of the SP code:
DECLARE @FMTONLY BIT;

if 1=0 
BEGIN
   set @FMTONLY = 1;
   SET FMTONLY OFF;
END

2) Add to the end of SP:

if @FMTONLY = 1 
BEGIN
   SET FMTONLY ON;
END

This will fix your issue with Function Import caused by using temp tables. If you keep seeing that Entity Framework does not receive information about columns, probably the problem is in something else, you can figure it out trying to execute your store proc with FMTONLY ON, like that:

SET FMTONLY ON;
exec MyProcedureThatDoesNotReturnsColumnsToEF