为什么使用实体框架时必须在存储过程中将SET FMTONLY OFF写入 [英] Why do we have to write SET FMTONLY OFF in stored procedures when using Entity Framework

问题描述

我最近加入了我团队中的一个项目。他们使用ASP.NET MVC和MS SQL以及Entity Framework作为ORM。

我注意到EF中使用的每个存储过程在开始时都有此共同之处的存储过程定义

  IF（0 = 1）SET FMTONLY OFF 
  

我认为这是一个非常奇怪的状况，因此我在Google上搜索了一下，并询问了我的同事。他们说，当EF映射存储过程时，它将所有参数发送为null并跳过所有if循环。因此，它也会跳过 IF（0 = 1）条件，然后将FMTONLY OFF设置为

$

$ b

在搜索设置FMTONLY OFF MSDN时b

仅将元数据返回给客户端。可以用来测试
响应的格式，而无需实际运行查询。

当您不执行此操作时便会出现问题控制数据库，您必须不断告诉DBA将其添加并一遍又一遍地向他们解释为什么需要它。

我仍然没有一个清楚的主意，为什么需要这样做。如果有人可以对此做一些详细解释或引导我到达涉及此主题的某个链接，那么这对我来说就是一个世界。

解决方案

具有 IF（0 = 1）设置FMTONLY OFF
似乎是随意在读取的存储过程中进行的冒险

实体框架是此标志的唯一来源，该标志被设置为我知道的标准做法（大概其他ORM可以使用它）。

p>

目的（据我所知）是提供一种无需实际接触任何数据即可获取过程返回模式的方法。 （某些存储过程您不希望仅仅为了更新orm的对象模型而执行。

因此，除非您有一个表在计算EF模型的次数已更新（学术上可能很有趣）

有关其他信息，请参见
存储过程返回int而不是结果集

最安全ftmonly与实体框架结合使用的方式（在我看来）是..在以下情况下

1. 如果所涉及的程序非常复杂并且使EF混乱（EF读取返回的第一个架构，忽略了流逻辑）


2. 让EF为您设置标志（我在下面清除了它以便更早退出）


3. 始终使用错误的逻辑（在FTMONLY打开时将被忽略-解释为EF试图读取模式）



4. 程序执行以下操作

     if（0 = 1）-如果FMTONLY处于此状态，则忽略条件
   开始
   -仅在打开fmtonly（即读取EF模式）时才进入此循环
   选择
    column1 
   ，column2 
    ... 
   ， columnX 
   来自任何A 
   交叉连接任何B 
    ... 
   交叉连接任何Q 
   -连接无关紧要，但它们可能使获取列定义更容易
   -和您想要的名字。这里重要的是生成正确的
   -返回模式...这与您要返回
   一样复杂，其中1 = 0 
    
   设置FMTONLY- -执行此操作，以便您现在可以强制执行自EF 
   以来的早期返回-通常只希望第一个数据集架构...其他规范可能
   -做一些不同的
   返回-如果FMTONLY仍在
    
   结束
     

$ b上，则将被忽略$ b

I recently joined one of the project in my team. They use ASP.NET MVC and MS SQL along with Entity Framework as ORM.

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

IF(0=1) SET FMTONLY OFF

I thought this was a very strange condition so I googled a bit about it and also asked my co workers about it. They say that when EF maps the stored procedure it send all parameters as null and skips all the if loop. Hence it would also skip the IF(0=1) condition and would then SET FMTONLY OFF

On searching for SET FMTONLY OFF MSDN, says

Returns only metadata to the client. Can be used to test the format of the response without actually running the query.

It becomes a problem when you dont control the database, you have to keep telling the DBA's to add it and explain to them over and over again why is it needed in the first place.

I still dont have a clear idea why this is required. If someone can explain this a bit in detail or guide me to some link which has this topic covered would mean the world to me.

解决方案

Having IF(0=1) SET FMTONLY OFF seems like a risky thing to casually do in stored procedures read in by entity framework.

Entity Framework is the only source of this flag being set as a standard practice that I'm aware of (presumably other ORM's may use it).

the purpose (as I understand it) is to provide a way to get a procedures return schema without actually touching any data. (some stored procedures you don't want to execute just to update an orm's object model.

so unless you have a table that is counting the number of times your EF model has been updated (which might be interesting academically)

for additional information see Stored procedure returns int instead of result set

the safest way to use ftmonly with entity framework (in my mind) is.. under the following circumstances

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

4. at the beginning of the 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
   

这篇关于为什么使用实体框架时必须在存储过程中将SET FMTONLY OFF写入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！