EF Function Import does not recognise columns returned by StoredProc

asp.net entity-framework sql-server-2008 visual-studio-2012

Question

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

I have a stored procedure that creates records in a temporary table, modifies them, and then SELECTS the records.

The issue is that the notice "The selected stored procedure or function returns no columns" appears in the results window when I try to construct a function import and click on "Get Column Information."

Now, if I run it directly from the database, I get the anticipated resultset back, so I KNOW for a fact that it DOES return columns.

You can summarize the saved procedure 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

Here's a (partly censored) example of the output when running the stored proc directly

1
3
5/23/2017 11:52:44 AM

Accepted Answer

I found the solution after frantically searching online: EF4 - No columns are returned by the specified stored procedure

A stored proc that makes use of dynamic queries or temporary tables cannot return metadata to EF. The workaround was to either build the intricate returning type by hand OR use

SET FMTONLY OFF

in my specification of the stored proc. Because the stored procedure will be run when Visual Studio does the metadata call in the second option, it should ideally only be utilized if the stored procedure doesn't change anything.

UPDATE: Checking that the saved process truly functions is an alternative. Another option is to create a mock stored procedure that returns the desired columns, bind to it, and then execute the actual logic from there.

10
5/23/2017 10:28:35 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow