Add stored procedure to Entity Framework [duplicat

Question

This question already has an answer here:

• EF4 - The selected stored procedure returns no columns 13 answers

I have a stored procedure which generate itself dynamically, in detail I am adding to where, order by clauses dynamically.

Here is my stored procedure:

ALTER PROCEDURE [dbo].[sp_GetConsultants]
    @SearchQuery VARCHAR(MAX) = NULL,
    @SortDataField VARCHAR(100),
    @SortOrder VARCHAR(4),
    @PageNum INT,
    @PageSize INT,
    @sql NVARCHAR(MAX) = NULL OUTPUT
AS
BEGIN
    SET @sql = N'

    WITH cte AS
    (
        SELECT
            ID, [NO], Firstname, Lastname, ReferanceID,
            CAST('''' AS VARCHAR(MAX)) AS ReferanceNO
        FROM 
            dbo.Consultants 
        WHERE 
            ReferanceID IS NULL

        UNION ALL

        SELECT
            c.ID, c.[NO], c.Firstname, c.Lastname, c.ReferanceID,
            CASE
               WHEN ct.ReferanceNO = ''''
                  THEN CAST(ct.[NO] AS VARCHAR(MAX))
               ELSE CAST(ct.[NO] AS VARCHAR(MAX))
            END
        FROM 
            dbo.Consultants c
        INNER JOIN 
            cte ct ON ct.ID = c.ReferanceID
    )
    SELECT * 
    FROM cte '
+ @SearchQuery
+ ' ORDER BY '
+ @SortDataField + ' ' + @SortOrder
+ ' OFFSET '+ CAST(@PageNum AS VARCHAR(20)) + ' ROW FETCH NEXT ' +CAST(@PageSize AS VARCHAR(20)) + ' ROWS ONLY'

EXEC sp_executesql @sql, N'@SearchQuery VARCHAR(MAX), @SortDataField VARCHAR(100), @SortOrder VARCHAR(4), @PageNum INT, @PageSize INT', @SearchQuery, @SortDataField, @SortOrder, @PageNum, @PageSize
END

I am trying to add this stored procedure to Entity Framework, but without success. Entity Framework can't create a complex type for my stored procedure, I click on the "Get Column Information" button, but the text box below says "The selected stored procedure returns no columns".

Do you know what is the problem?

P.S. It works if I remove parameters from @sql string

Answer 1

Probably because there is no proper SQL Statement in your procedure through which Entity Framework can detect the resulting columns.

A workaround is, to put a SQL Select statement at the end of the procedure which should tell Entity Framework about the result, for example just put following statement at the end of the procedure and update it

SELECT
    CAST(1 AS int) AS  ID
    ,CAST(1 AS int) AS [NO]
    ,N'Fist Name' AS Firstname
    ,N'Lasat Name' AS Lastname       
    ,CAST(1 AS int) AS ReferanceID
    ,CAST('' AS VARCHAR(MAX))  AS ReferanceNO

After this, go in Entity Framework and import the procedure, it should show you the columns properly. Once you imported the SP in Entity Framework, go back in Procedure and comment out the last SELECT statement which we just added. This last statement is just for Entity Framework to understand what SP is going to return.

P.S. I don't know exactly the column types, so you should better change the column types in the select statement