EF4 - 所选存储过程不返回列 [英] EF4 - The selected stored procedure returns no columns

问题描述

我在一个存储过程中查询了一些使用某些动态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):

• 手动定义返回的复合类型（我猜想它应该可以工作） >
  
• 使用黑客，只是添加开始时的存储过程 SET FMTONLY OFF 。这将允许您的SP的其余代码以正常方式执行。只需确保您的SP不会修改任何数据，因为这些修改将在导入期间执行！成功导入后，删除该黑客。

• 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.

这篇关于EF4 - 所选存储过程不返回列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！