What is the recommended way for adding a SQL Server stored procedure to an Entity Framework model

Question

Summary

In our application we use Entity Framework 6 and SQL Server 2016. When importing into the EF model a stored procedure with complex logic, in particular when using temp tables, we put on top:

SET FMTONLY OFF

Which causes the stored procedure to run the full logic and hence return the correct output format, which allows the EF model to generate a complex type correctly.

In general, it works OK. However, Microsoft documentation https://learn.microsoft.com/en-us/sql/t-sql/statements/set-fmtonly-transact-sql?view=sql-server-ver15 states: Do not use this feature. So I'm wondering what the recommended way is to import a stored procedure and generate a complex type correctly.

More details

Firstly, I'm confused by this note Do not use this feature on the Microsoft website about FMTONLY. Isn't it the same command (with option ON) called by the EF model updater? This would mean the EF model updater uses a feature which should not be used.

Secondly, I assume that this EF model updater behaviour aims to prevent an unwanted DB data manipulation when a stored procedure is executed with all input arguments equal NULL. But the actual behaviour does not seemed aligned with this assumption.

Consider a simple stored procedure below where I tried to avoid using SET FMTONLY OFF. I analysed the exact behaviour using SQL profiler while updating the EF model from Visual Studio (2013, CU 5, Pro).

SET NOCOUNT ON;

IF (1=0) --FOR EF edmx update this will actually execute
BEGIN
    DECLARE @tempTable TABLE
                       (
                           [id] [int], 
                           [date_local] [datetime2](7), 
                           [value] [float] NULL
                       )

    SELECT * FROM @tempTable
    RETURN 0
END

CREATE TABLE #TempValue
(
    [id] [int] NOT NULL,
    [date_local] [datetime2](7) NOT NULL,
    [value] [float] NULL

    CONSTRAINT [PK_TempValue] 
        PRIMARY KEY CLUSTERED ([id] ASC, [date_local] ASC)
                    WITH (PAD_INDEX =OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO #TempValue (id, date_local, value)
    SELECT @id, *
    FROM OPENJSON(@valueJson)
    WITH (
          date_local datetime2(7) '$.dateTime',
          value float '$.value'
         );

SELECT * FROM #TempValue

Points:

1. The EF model updater calls it, goes inside IF (1=0) (as expected with SET FMTONLY ON), and runs SELECT * from @tempTable correctly.

2. It ignores return 0 and continues to run the rest of the stored procedure body.

3. It skips CREATE TABLE #TempValue but then tries to run INSERT INTO #TempValue which of course doesn't exist, so it stops here and doesn't execute SELECT * FROM #TempValue, which would also fail.

In this case the complex type was generated correctly, because the last successful select was SELECT * from @tempTable. But I feel this was just luck because there are no subsequent selects which actually run. To me, this whole behaviour is flawed, in particular the skipping of return 0 and CREATE TABLE #TempValue but then allowing INSERT INTO; if you allow INSERT INTO surely you're not preserving the DB data?

So the 2 workarounds we tried that actually work are as follows:

1. Put SET FMTONLY OFF on top. It works but I'm a bit worried by this Do not use this feature on the Microsoft website.

2. Comment out whole SP body and add DECLARE @tempTable table ... SELECT * from @tempTable as in example above. After updating EF model remove the added chunk and uncomment the original code. Do this every time EF model is updated, so it's not very efficient.

What is a proper and recommended way to do it?