EF6 fails to import stored procedure

Question

This is a simplified version of a stored procedure

ALTER PROCEDURE [dbo].[StoredProc1]
(
   @PageIndex INT = 1,
   @RecordCount INT = 20,
   @Gender NVARCHAR(10) = NULL
)
AS 
BEGIN
    SET NOCOUNT ON ;

WITH tmp1 AS
(   
    SELECT u.UserId, MIN(cl.ResultField) AS BestResult
      FROM [Users] u
        INNER JOIN Table1 tbl1 ON tbl1.UserId = u.UserId
     WHERE (@Gender IS NULL OR u.Gender = @Gender)
             GROUP BY u.UserID
     ORDER BY BestResult
       OFFSET @PageIndex * @RecordCount ROWS 
       FETCH NEXT @RecordCount ROWS ONLY
)       
SELECT t.UserId, t.BestResult, AVG(cl.ResultField) AS Average
INTO #TmpAverage
FROM tmp1 t 
  INNER JOIN Table1 tbl1 ON tbl1.UserId = t.UserId
GROUP BY t.UserID, t.BestResult
 ORDER BY Average

SELECT u.UserId, u.Name, u.Gender, t.BestResult, t.Average
  FROM #tmpAverage t
    INNER JOIN Users u on u.UserId = t.UserId

DROP TABLE #TmpAverage
END

When I use EF6 to load the stored procedure, and then go to the "Edit Function Import" dialog, no columns are displayed there. Even after I ask to Retrieve the Columns, I get the message that the SP does not return columns. When I execute the SP from SMMS, I get the expected [UserId, Name, Gender, BestResult, Average] list of records.

Any idea how can I tweak the stored procedure or EF6 to make it work? Thanks in advance

@marc_s What I'm talking about is the "Returns Collection of" dialog that even when clicking on the "Get Columns Info" button, it says that the stored procedure returns no columns. If I replace the stored proc content with a simple select field1, field2, field3 from table1 ...those do show on that dialog. I'll update the question to be more clear. Thanks. — Johnny, Dec 23 '13 at 15:42
I think the problem is in TEMP table. See [this](http://stackoverflow.com/a/7131344/1387612) — janisz, Dec 23 '13 at 15:45
It doesn't cope with things like `#temp` tables well. I'd just temporarily change the definition to select a dummy resultset of the desired column names and dataypes. Import to EF then replace with the proper definition. — Martin Smith, Dec 23 '13 at 15:46
Why are you putting the values into a `#temp` table? it seems to me like you could just join to users in the first place then simply select that instead. — Zane, Dec 23 '13 at 15:52
@Zane I'm trying to select a restrict number of users ordered by their lowest field value, then make sure I get the right pageIndex and RecordCount, to finally add that to their average. Is there a better way? — Johnny, Dec 23 '13 at 15:55
@MartinSmith that type solution does work, but carries the problem that any time I do a refresh the Database MDX, it will get overridden with the NO columns factor, unless it's i keep it "fake" till the DB is completed. — Johnny, Dec 23 '13 at 15:58
Does that refresh the stored procedure metadata as well then? In that case I guess try the solutions in @janisz's link. — Martin Smith, Dec 23 '13 at 16:01

Johnny · Answer 1 · 2013-12-23T17:59:08.643

Thanks to the comments above, the answer is that unfortunately EF6 does not cope well with TMP tables on stored procedures.

One way around is the following:
1) Comment out all temp table calls inside the Stored Procedure.

2) Change the Stored Procedure to return a Fake a result with the same exact column's names that match the expected result

3) Import the Stored Procedure into EF6

4) Double Click on the Function Imports/Stored procedure name

5) On the Edit Function Import dialog, retrieve the Columns and the create a New Complex Type that will match the fake columns

6) CTRL+Save in order to generate all the C# code

7) Re-Update the Stored Procedure by removing the fake result set and un-comment the code with the Temp tables.

That should do the job.

P.S. Special thanks for the helpers that pointed me to the right place !!!

score 0 · Answer 2 · answered Mar 05 '14 at 02:43

0

Sometimes it works to add the following statement to the stored proc:

set fmtonly off

But still, dont leave this statement in - only use it while generating the result set

answered Mar 05 '14 at 02:43

Simon Dowdeswell

1,001
11
19

EF6 fails to import stored procedure

2 Answers2