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

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.

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

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

13 个解决方案

#1

136

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

EF不支持导入生成结果集的存储过程:

Dynamic queries
动态查询
Temporary tables
临时表

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:

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

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):

您有两个选择(除了需要重写存储过程以不使用这些特性的选项):

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.
使用hack，只需要在其开始设置FMTONLY的时候添加存储过程。这将允许您的SP代码以正常方式执行。只要确保SP不修改任何数据，因为这些修改将在导入期间执行!成功导入后删除该hack。

#2

Adding this Non-Logical block of code solved the problem. Even though it will never Hit

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

IF 1=0 BEGIN
    SET FMTONLY OFF
END

Why does my typed dataset not like temporary tables?

为什么我的类型化数据集不喜欢临时表?

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

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

#3

Or you can create a User-Defined Table Type and return that.

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

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

Then in the procedure:

然后在程序:

DECLARE @tempTable dbo.T1

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

....

SELECT * FROM @tempTable

Now EF should be able to recognize the returned columns type.

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

#4

As some others have noted, make sure the procedure actually runs. In particular, in my case, I was running the procedure happily without error in SQL Server Management Studio completely forgetting that I was logged in with admin rights. As soon as I tried running the procedure using my application's principal user I found there was a table in the query that that user did not have permission to access.

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

#5

What I would add is:

我想补充的是:

That the import also fails if the stored procedures has parameters and returns no result set for the default parameter values.

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

My stored procedure had 2 float parameters and would not return anything when both parameters are 0.

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

So in order to add this stored procedure to the entity model, I set the value of these parameters in the stored procedure so that it is guaranteed to return some rows, no matter what the parameters actually are.

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

Then after adding this stored procedure to the entity model I undid the changes.

然后，在将这个存储过程添加到实体模型之后，我取消了更改。

#6

Interesting side note: Had the same problem which I first solved by using Table Variables, rather than Temp Tables (just for the import). That wasn't particularly intuitive to me, and threw me off when initially observing my two SProcs: one using Temp tables and one with Table Variables.

有趣的附带说明:有相同的问题，我首先通过使用表变量而不是临时表(仅用于导入)来解决。这对我来说并不是特别直观，在开始观察我的两个sproc时，我就抛弃了我:一个使用临时表，另一个使用表变量。

(SET FMTONLY OFF never worked for me, so I just changed my SProcs temporarily to get the column info, rather than bothering with the hack on the EF side just as an FYI.)

(设置FMTONLY OFF对我没用，所以我只是暂时更改了SProcs以获取专栏信息，而不是像一个FMTONLY那样麻烦地在EF端进行黑客攻击。)

My best option was really just manually creating the complex type and mapping the function import to it. Worked great, and the only difference ended up being that an additional FactoryMethod to create the properties was included in the Designer.

我最好的选择就是手动创建复杂类型并将函数导入映射到它。工作得很好，唯一的区别是创建属性的附加FactoryMethod包含在设计器中。

#7

both solutions : 1- Define the returned complex type manually (I guess it should work) 2- Use a hack and just for adding the stored procedure put at its beginning SET FMTONLY OFF.

两种解决方案:1-手动定义返回的复杂类型(我猜它应该可以工作)2-使用一个hack，并仅仅用于添加在其开始设置FMTONLY OFF处的存储过程。

not working with me in some procedure however it worked with other one!

不与我一起工作在某些程序，但它与另一个!

my procedure ends with this line:

我的程序以这一行结尾:

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

Thanks

谢谢

#8

In addition to what @tmanthley said, be sure that your stored procedure actually works by running it first in SSMS. I had imported some stored procedures and forgot about a couple dependent scalar functions, which caused EF to determine that the procedure returned no columns. Seems like a mistake I should have caught earlier on, but EF doesn't give you an error message in that case.

除了@tmanthley所说的之外，请确保您的存储过程实际上是通过在SSMS中首先运行它来工作的。我已经导入了一些存储过程，并忘记了几个相关的标量函数，这导致EF决定过程不返回列。这似乎是我之前应该注意到的一个错误，但在这种情况下，EF不会给您一条错误消息。

#9

In my case adding SET NOCOUNT ON; at the top of the procedure fixed the problem. It's best practice anyway.

在我的例子中，添加SET NOCOUNT ON;在程序的顶端解决了这个问题。这是最佳实践。

#10

In my case SET FMTONLY OFF did not work. The method I followed is, I took backup of original stored procedure and replace with only column name like the below query.

在我的例子中，FMTONLY OFF不起作用。我所遵循的方法是，我对原始存储过程进行了备份，并只替换了如下查询的列名。

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

After this change, create new function import, complex type in entity framework. Once the function import and complex type is created, replace the above query with your original stored procedure.

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

#11

SET FMTONLY OFF

worked for me for one of the procedure but failed for other procedure. Following steps helps me to resolve my problem

为我做过一个手术，但在其他手术中失败了。下面的步骤可以帮助我解决我的问题

Within a stored procedure, I have created temporary table with the same column type and inserted all the data returned by dynamic query to temp table. and selected the temp table data.

在存储过程中，我创建了具有相同列类型的临时表，并将dynamic query返回的所有数据插入到临时表中。并选择temp表数据。
```
Create table #temp
(
   -- columns with same types as dynamic query    
)

EXEC sp_executeSQL @sql 

insert into #temp 
    Select * from #temp 

drop table #temp
```
Deleted existing complex type, import function and stored procedure instance for old stored procedure and updated entity model for current new procedure.

删除旧存储过程的现有复杂类型、导入函数和存储过程实例，更新当前新过程的实体模型。
Edit the imported Function in entity modal for desired complex type, you will get all the column information there which is not getting for previous stored procedure.

在实体模式中编辑导入的函数，以获得所需的复杂类型，您将得到所有的列信息，而这些信息并不是针对以前的存储过程。
once you have done with the type creation you can delete the temporary table from stored procedure and then refresh Entity Framework.

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

#12

In Entity framework, while getting column information the sql executes the procedure with passing null values in parameter. So I handled null case differently by creating a temp table with all the required columns and returning all the columns with no value when null is passed to the procedure.

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

In my procedure there was dynamic query, something like

在我的过程中有动态查询，类似于

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

I was not getting the column information in my case after using the set FMTONLY OFF trick.

在使用set FMTONLY OFF技巧后，我没有得到我的示例中的列信息。

This is temp table I created to get the blank data. Now I am getting the column info

这是我创建的获取空白数据的临时表。现在我得到了列信息

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

#13

Entity Framework will try to get the columns by executing your stored procedure, passing NULL for every argument.

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

Please make sure that the stored procedure will return something under all the circumstances. Note it may have been smarter for Entity Framework to execute the stored proc with default values for the arguments, as opposed to NULLs.

请确保存储过程在所有情况下都会返回一些内容。注意，对于实体框架来说，使用默认值来执行存储的proc(而不是null)可能更聪明。
ER does the following to get the metadata of the table:

ER执行以下操作以获取表的元数据:

SET FMTONLY ON

设置FMTONLY
This will break your stored procedure in various circumstances, in particular, if it uses a temporary table.

这将在各种情况下破坏存储过程，特别是在使用临时表的情况下。
So to get a result as complex type; please try by adding

得到复杂类型的结果;请尝试通过添加

SET FMTONLY OFF;

设置FMTONLY;

This worked for me - hope it works for you too.

这对我很有效——希望对你也有用。

Referred from 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

提到从https://social.msdn.microsoft.com/forums/en us/e7f598a2 - 6827 4 - b27 a09d aefe733b48e6/entity -模型-添加-函数-进口-存储过程返回columns?forum=adodotnetentityframework——没有

#1

136