EF Function Import does not recognise columns returned by StoredProc

Question

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

Answer 1

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.