EF4 - 选定的存储过程不返回任何列

2021-12-02 00:00:00 sql-server temp-tables entity-framework-4

我在一个存储过程中进行了查询，该过程使用一些动态 SQL 调用了一些链接服务器.我知道 EF 不喜欢那样，所以我专门列出了将返回的所有列.然而，它仍然不喜欢那样.我在这里做错了什么?我只希望 EF 能够检测从存储过程返回的列，以便我可以创建我需要的类.

I have query in a stored procedure that calls some linked servers with some dynamic SQL. I understand that EF doesn't like that, so I specifically listed all the columns that would be returned. Yet, it still doesn't like that. What am I doing wrong here? I just want EF to be able to detect the columns returned from the stored procedure so I can create the classes I need.

请查看以下构成我的存储过程最后几行的代码:

Please see the following code that makes up the last lines of my stored procedure:

SELECT
    #TempMain.ID,
    #TempMain.Class_Data,
    #TempMain.Web_Store_Class1,
    #TempMain.Web_Store_Class2,
    #TempMain.Web_Store_Status,
    #TempMain.Cur_1pc_Cat51_Price,
    #TempMain.Cur_1pc_Cat52_Price,
    #TempMain.Cur_1pc_Cat61_Price,
    #TempMain.Cur_1pc_Cat62_Price,
    #TempMain.Cur_1pc_Cat63_Price,
    #TempMain.Flat_Length,
    #TempMain.Flat_Width,
    #TempMain.Item_Height,
    #TempMain.Item_Weight,
    #TempMain.Um,
    #TempMain.Lead_Time_Code,
    #TempMain.Wp_Image_Nme,
    #TempMain.Wp_Mod_Dte,
    #TempMain.Catalog_Price_Chg_Dt,
    #TempMain.Description,
    #TempMain.Supersede_Ctl,
    #TempMain.Supersede_Pn,
    TempDesc.Cust_Desc,
    TempMfgr.Mfgr_Item_Nbr,
    TempMfgr.Mfgr_Name,
    TempMfgr.Vendor_ID
FROM
    #TempMain
        LEFT JOIN TempDesc ON #TempMain.ID = TempDesc.ID
        LEFT JOIN TempMfgr ON #TempMain.ID = TempMfgr.ID

推荐答案

EF 不支持导入从以下位置构建结果集的存储过程:

EF doesn't support importing stored procedures which build result set from:

  • 动态查询
  • 临时表

原因是要导入过程EF必须执行它.此类操作可能很危险，因为它会触发数据库中的某些更改.因为 EF 在执行存储过程之前使用了特殊的 SQL 命令:

The reason is that to import the procedure EF must execute it. Such operation can be dangerous because it can trigger some changes in the database. Because of that EF uses special SQL command before it executes the stored procedure:

SET FMTONLY ON

通过执行此命令，存储过程将仅返回有关其结果集中列的元数据"，并且不会执行其逻辑.但是因为没有执行逻辑，所以没有临时表(或构建的动态查询)，所以元数据不包含任何内容.

By executing this command stored procedure will return only "metadata" about columns in its result set and it will not execute its logic. But because the logic wasn't executed there is no temporary table (or built dynamic query) so metadata contains nothing.

您有两种选择(除了需要重新编写存储过程才能不使用这些功能的一种):

You have two choices (except the one which requires re-writing your stored procedure to not use these features):

  • 手动定义返回的复杂类型(我想它应该可以工作)
  • 使用 hack 并仅用于添加放在其开头的存储过程SET FMTONLY OFF.这将允许您的 SP 的其余代码以正常方式执行.只需确保您的 SP 不会修改任何数据，因为这些修改将在导入期间执行！成功导入后，删除该 hack.
  • Define the returned complex type manually (I guess it should work)
  • Use a hack and just for adding the stored procedure put at its beginning SET FMTONLY OFF. This will allow rest of your SP's code to execute in normal way. Just make sure that your SP doesn't modify any data because these modifications will be executed during import! After successful import remove that hack.

相关文章