EF函数导入无法识别由StoredProc返回的列 [英] EF Function Import does not recognise columns returned by StoredProc

问题描述

可能重复：

EF无法从使用动态查询或临时表的存储过程中获取元数据。解决方案是手动创建复杂的返回类型，或者放

 设置FMTONLY OFF 
  

在我的存储过程定义中。第二种选择的危险，当然是，存储的proc将在Visual Studio执行元数据调用时执行，因此，理想情况下，只有在存储过程没有任何更改的情况下，才使用该存储过程。

更新：一种替代方法是确保存储过程确实有效。您可以做的另一件事是创建一个虚拟存储过程，该存储过程返回所需的列，并绑定到该列，然后执行实际的逻辑。

Possible Duplicate:
EF4 - The selected stored procedure returns no columns

I have a stored procedure which populates a #temp table, does things to its records and then SELECTs the records.

Problem is, when I try to create a function import and click on [Get Column Information], the results pane shows the message "The selected stored procedure or function returns no columns".

Now, I KNOW for a fact that it does indeed return columns because if I run it directly from the db, I get the expected resultset back.

The stored procedure can be summarised as follows:

SELECT P.PersonID, P.Surname, P.NickName, P.DateofBirth
INTO #SeriesCompleted   
FROM 
    Table1 T (NOLOCK)
INNER JOIN 
    Table2 P (NOLOCK) ON T.PID = P.PID
;
Select r.PID, SUM(rt.Distance) 'Distance'
INTO #Distance
FROM 
    #SeriesCompleted sc
    inner join table3 rsr (NOLOCK) on rsr.SeriesId = sc.SeriesId
    inner join table4 r (NOLOCK) on r.PID = sc.PID
    inner join table5 rt (NOLOCK) on rt.RouteID = r.RouteID
GROUP BY r.PID;

UPDATE #SeriesCompleted
SET Distance =  d.Distance
FROM #SeriesCompleted sc
INNER JOIN #Distance d on d.PID = sc.PPID;

--Here is where the result is returned.
SELECT distinct sc.PersonID, sc.NickName, sc.Surname, sc.DateofBirth, sc.NumberFinished, sc.Distance
FROM #SeriesCompleted SC

解决方案

After googling furiously, I came across the answer: EF4 - The selected stored procedure returns no columns

EF cannot get metadata from a stored proc that uses dynamic queries or temp tables. The solution was to either manually create the complex returned type OR put

SET FMTONLY OFF

in my stored proc definition. The danger with the second option, of course is that the stored proc will be executed when Visual Studio executes the metadata call, so this would ideally only be used if the stored procedure doesn't change anything.

UPDATE: An alternative is to make sure that the stored procedure actually works. Another thing you could do is to create a dummy stored procedure that returns the columns you want, bind to it and then do the actual logic.

这篇关于EF函数导入无法识别由StoredProc返回的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！