Why should we write SET FMTONLY OFF in stored procedures when using the Entity Framework

Question

I recently joined one of the projects on my team. They use ASP.NET MVC and MS SQL together with the Entity Framework as ORM.

I noticed that each of the stored procedures used in EF has this common line at the beginning of the stored procedure definition

IF(0=1) SET FMTONLY OFF

I thought this was a very strange condition, so I worked on it a bit and also asked my colleagues about it. They say that when EF displays the stored procedure, it sends all parameters as null and skips the entire if loop. Therefore, he would also skip the condition IF(0=1)and thenSET FMTONLY OFF

When searching for SET FMTONLY OFFMSDN, says

Returns only metadata to the client. It can be used to check the response format without actually executing the request.

This becomes a problem when you do not control the database, you must constantly inform the database administrator about its addition and explain to them again and again why this is necessary in the first place.

I still don't have a clear idea of ​​why this is required. If someone can explain this in a little detail or direct me to some link that covers this topic, that would mean peace to me.

Answer 1

, . , null . , .., null . , , , , .

Answer 2

, , SSRS. , FMTONLY , . . FMTONLY SSRS

Answer 3

IF(0=1) SET FMTONLY OFF .

Entity Framework , , (, ORM ).

( ) , , . ( , orm.

, , , EF ( )

int .

ftmonly ( ) -

1. if the procedure in question is complex and misleading to EF (EF reads the first returned circuit, the flow logic is ignored)
2. let EF set a flag for you. (I clear it below to exit early)
3. always use false logic (which will be ignored when FTMONLY is enabled - interpret this as EF trying to read the circuit)

4. at the beginning of a complex procedure, do the following

   if(0=1)  -- if FMTONLY is on this if condition is ignored
   begin
       -- this loop will only be entered if fmtonly is on (ie EF schema read)
       select 
           column1
           ,column2
           ...
           ,columnX
       from whateverA
           cross join whateverB
           ...
           cross join whateverQ
       -- joins don't matter but they might make it easier to get the column definitions
       -- and names you desire.   the important thing here is generating the proper 
       -- return schema... which is as complex as whatever you are trying to return
       where 1=0
   
       set FMTONLY off -- do this so that you can now force an early return since EF
       -- usually only wants the first data set schema...  other orms might
       -- do something different
       return  -- this will be ignored if FMTONLY is still on
   
   end