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

Question

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

请参阅以下代码，它构成了我的存储过程的最后几行：

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

Answer 1

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

• 动态查询
• 临时表

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

SET FMTONLY ON

通过执行此命令，存储过程将仅返回有关其结果集中的列的“元数据”，并且不会执行其逻辑。但由于逻辑未执行，因此没有临时表（或构建的动态查询），因此元数据不包含任何内容。

您有两种选择（除了需要重写存储过程才能不使用这些功能的选择）：

• 手动定义返回的复杂类型（我想它应该有效）
• 使用 hack 并仅添加放在开头的存储过程设置 FMTONLY 关闭。这将允许 SP 的其余代码以正常方式执行。只需确保您的 SP 不会修改任何数据，因为这些修改将在导入期间执行！成功导入后删除该黑客行为。

Answer 2

添加这个非逻辑代码块解决了问题。即使它永远不会命中

IF 1=0 BEGIN
    SET FMTONLY OFF
END

为什么我的类型化数据集不像临时数据集表？

http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/fe76d511-64a8-436d-9c16-6d09ecf436ea/

Answer 3

或者您可以创建一个用户定义的表类型并返回它。

CREATE TYPE T1 AS TABLE 
( ID bigint NOT NULL
  ,Field1 varchar(max) COLLATE Latin1_General_CI_AI NOT NULL
  ,Field2 bit NOT NULL
  ,Field3 varchar(500) NOT NULL
  );
GO

然后在过程中：

DECLARE @tempTable dbo.T1

INSERT @tempTable (ID, Field1, Field2, Field3)
SELECT .....

....

SELECT * FROM @tempTable

现在 EF 应该能够识别返回的列类型。

Answer 4

正如其他一些人所指出的，请确保该过程实际运行。特别是，就我而言，我在 SQL Server Management Studio 中愉快地运行该过程，没有出现错误，完全忘记了我是使用管理员权限登录的。当我尝试使用应用程序的主要用户运行该过程时，我发现查询中有一个该用户无权访问的表。

Answer 5

我要补充的是：

如果存储过程有参数并且默认参数值没有返回结果集，导入也会失败。

我的存储过程有2个浮点参数，当两个参数都为0时，不会返回任何内容。

因此，为了将此存储过程添加到实体模型中，我在存储过程中设置了这些参数的值，以便保证返回一些行，无论参数实际是什么。

然后，将此存储过程添加到实体模型后，我撤消了更改。

Answer 6

有趣的旁注：有同样的问题，我首先通过使用表变量而不是临时表（仅用于导入）解决。这对我来说并不是特别直观，并且在最初观察我的两个 SPoc 时让我感到困惑：一个使用临时表，另一个使用表变量。

（SET FMTONLY OFF 对我来说从来没有用过，所以我只是暂时更改了我的 SPrcs 来获取列信息，而不是像仅供参考一样费心在 EF 端进行黑客攻击。）

我最好的选择实际上只是手动创建复杂类型和将函数导入映射到它。效果很好，唯一的区别是设计器中包含了一个用于创建属性的附加 FactoryMethod。

Answer 7

两种解决方案：
1-手动定义返回的复杂类型（我想它应该可以工作）
2-使用 hack，只是为了添加存储过程，将其放在开头 SET FMTONLY OFF。

在某些程序中不与我合作，但它与其他程序一起工作！

我的程序以这一行结束：

SELECT machineId, production [AProduction]
        , (select production FROM #ShiftBFinalProd WHERE machineId = #ShiftAFinalProd.machineId) [BProduction]
        , (select production FROM #ShiftCFinalProd WHERE machineId = #ShiftAFinalProd.machineId) [CProduction]
     FROM #ShiftAFinalProd
     ORDER BY machineId

谢谢

Answer 8

除了 @tmanthley 所说的之外，请先在 SSMS 中运行您的存储过程，以确保它确实有效。我导入了一些存储过程，但忘记了几个相关标量函数，这导致 EF 确定该过程没有返回任何列。这似乎是一个我应该早点发现的错误，但在这种情况下 EF 不会向您提供错误消息。

Answer 9

实体框架将尝试通过执行存储过程来获取列，为每个参数传递 NULL。

1. 请确保存储过程在所有情况下都会返回一些内容。请注意，实体框架使用参数的默认值（而不是 NULL）执行存储过程可能更聪明。

2. ER 执行以下操作来获取表的元数据：

   设置FMTONLY打开

3. 这会在各种情况下破坏您的存储过程，特别是如果它使用临时表。

4. 因此要得到复杂类型的结果；请尝试添加

   设置 FMTONLY 关闭；

这对我有用 - 希望它也对你有用。

引用自 https://social.msdn.microsoft.com/Forums/en-US/e7f598a2-6827-4b27-a09d-aefe733b48e6/entity-model-add-function-import-stored-procedure-returns-no-columns ？forum=adodotnetentityframework

Answer 10

就我而言，在过程顶部添加 SET NOCOUNT ON; 解决了问题。无论如何，这是最佳实践。

Answer 11

就我而言，SET FMTONLY OFF 不起作用。我遵循的方法是，我备份了原始存储过程并仅替换为列名，如下面的查询。

Select Convert(max,'') as Id,Convert(max,'') as Name

进行此更改后，在实体框架中创建新的函数导入、复杂类型。
创建函数导入和复杂类型后，将上述查询替换为原始存储过程。

Answer 12

SET FMTONLY OFF 

其中一项程序为我工作，但其他程序失败。以下步骤帮助我解决我的问题

1. 在存储过程中，我创建了具有相同列类型的临时表，并将动态查询返回的所有数据插入到临时表中。
   并选择临时表数据。

   创建表#temp
   （
      -- 与动态查询类型相同的列    
   ）
   
   EXEC sp_executeSQL @sql 
   
   插入#temp 
       从 #temp 中选择 * 
   
   删除表#temp
   

2. 删除了旧存储过程的现有复杂类型、导入函数和存储过程实例，并更新了当前新过程的实体模型。

3. 在实体模态中编辑导入的函数以获得所需的复杂类型，您将获得先前存储过程未获得的所有列信息。

4. 完成类型创建后，您可以从存储过程中删除临时表，然后刷新实体框架。

   完成类型

Answer 13

在实体框架中，在获取列信息时，sql 会在参数中传递空值来执行过程。因此，我以不同的方式处理 null 情况，方法是创建一个包含所有必需列的临时表，并在将 null 传递给过程时返回所有没有值的列。

在我的程序中存在动态查询，就像

declare @category_id    int
set @category_id = (SELECT CATEGORY_ID FROM CORE_USER where USER_ID = @USER_ID)
declare @tableName varchar(15)
declare @sql VARCHAR(max)     
declare  @USER_IDT  varchar(100)    
declare @SESSION_IDT  varchar(10)

 IF (@category_id = 3)     
set @tableName =  'STUD_STUDENT'
else if(@category_id = 4)
set @tableName = 'STUD_GUARDIAN'


if isnull(@tableName,'')<>'' 
begin

set @sql  = 'SELECT  [USER_ID], [FIRST_NAME], SCHOOL_NAME, SOCIETY_NAME, SCHOOL_ID,
SESSION_ID, [START_DATE], [END_DATE]
from  @tableName
....
EXECUTE   (@sql)
END

ELSE
BEGIN
SELECT  * from #UserPrfTemp
END

我没有获取列信息 一样
我使用设置 FMTONLY OFF 技巧后的情况。

这是我创建的临时表来获取空白数据。
现在我正在获取专栏信息

Create table #UserPrfTemp
(
[USER_ID] bigint, 
[FIRST_NAME] nvarchar(60),
SCHOOL_NAME nvarchar(60),
SOCIETY_NAME nvarchar(200)
.....
}

Answer 14

我解决了这个问题，创建一个表变量，然后从中返回。

DECLARE @VarTable TABLE (
    NeededColumn1                       VARCHAR(100),
    NeededColumn2                       INT,
    NeededColumn3                       VARCHAR(100)
)

...

--Fetch Data from Linked server here

...

INSERT INTO @VarTable (NeededColumn1,NeededColumn2,NeededColumn3)
SELECT Column1, Column2, Column3
FROM #TempTable

SELECT * FROM @VarTable.

通过这种方式，您的 SP 结果将绑定到 EF 可以访问的表变量。

Answer 15

我发现了一种可以帮助大多数人解决所发生问题的方法。

启动您最喜欢的 SQL 客户端并运行您尝试使用每个参数 = null 更新的过程。当 SET FMTONLY ON 时，Visual Studio 实际上正在尝试执行此操作。运行跟踪。你会看到的。

您可能会收到错误或意外的数据。解决这个问题，你的问题就解决了。

就我而言，该函数读取 JSON 并失败，因为 JSON 字符串为空。

我只是提出了类似的内容

IF(@FooJSON IS NULL)
BEGIN
  SELECT 1 VAR1, 2 VAR2;
END
ELSE
--OTHER LOGIC

这可能是一个丑陋的解决方案，但我继承了这个烂摊子，我们不会进入Ravenholm。

Answer 16

使用 使用 SQL 表达式