CODEWITHSUNDEEP
c#sql-serverentity-frameworkt-sql
Johnny
Johnny

Reputation: 601

EF6 fails to import stored procedure

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

Upvotes: 4

Views: 1108

Answers (2)

Simon Dowdeswell
Simon Dowdeswell

Reputation: 1081

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

Upvotes: 0

Johnny
Johnny

Reputation: 601

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 !!!

Upvotes: 1

Related Questions