Entity Framework doesn't support stored procedures which build result sets from Dynamic queries or Temporary tables

Question

I've written the following stored proc, which works fine. What I want to do with it though is use it an entity data model. However using it in the entity data model maps to a return type of integer, and a value of zero.

How do I get the SP to return the actual data instead of an integer using the DataContext ?

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE TYPE = 'P' AND NAME = 'myProc') DROP PROCEDURE myProc;
GO
CREATE PROCEDURE [dbo].myProc
@START DateTime, @STOP DateTime
AS
BEGIN TRY

    CREATE TABLE #Temp (download_Pk int);
    INSERT INTO #Temp 
    SELECT      download_pk FROM t1
    UNION       ALL
    SELECT      download_pk FROM t2;    

    WITH 
    x as 
    (
        SELECT      ID as Caps_Pk,
                    rootID as [Caps_RootId],
                    Case400Series as [Case],
                    SUBSTRING(c1, CHARINDEX('_', c1,1)+1, LEN(c1)) as [Customer],
                    run as Run,                     
                    SUBSTRING(c2, 1, CHARINDEX('_', c2, 1) -1) as [Sample], 
                    SUBSTRING(c2, CHARINDEX('_', c2,1)+1, len(c2)) as [Amplification],
                    projectTitle,
                    DateAdded as [UploadTime],                  
                    UserId as [User]
        FROM        t3 
        WHERE       DateAdded >= @START AND DateAdded <= @STOP AND
                    [User] in (SELECT name FROM ViewUsers WHERE Site = 'abc' AND Role = 'def')
    )   

    SELECT      *
    FROM        x
    WHERE       Caps_Pk NOT IN (Select download_Pk from #Temp)

    DROP TABLE #Temp;

END TRY

BEGIN CATCH
    DROP TABLE #Temp;       

END CATCH

GO

Thanks in Advance.

score 1 · Answer 1 · answered Apr 02 '13 at 13:13

1

Have you looked at this Code First Stored Procudure, this works great for me, it also has a NuGet Pckage that you can install.

answered Apr 02 '13 at 13:13

J.W.

17,991
7
43
76

score 1 · Accepted Answer · edited May 23 '17 at 11:56

1

found the answer here: EF4 - The selected stored procedure returns no columns

"EF doesn't support importing stored procedures which build result set from: Dynamic queries Temporary tables The reason is that to import the procedure EF must execute it."

edited May 23 '17 at 11:56

Community

1
1

answered Apr 08 '13 at 07:42

SkeetJon

1,491
1
19
40

Entity Framework doesn't support stored procedures which build result sets from Dynamic queries or Temporary tables

2 Answers2