Add stored procedure to Entity Framework

Question

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

Entity framework has the ability to call stored procedures, but it does not track them through objects the way it does for models. It is slightly unclear what you're trying to do or what error you're having. — Max, Sep 06 '16 at 19:42
@Max I am trying to write dynamicly query and because of this procedure is string and executes by sp_executesql. Problem is that entity framework can't identify this procedure, why I don't know — www1986, Sep 06 '16 at 19:56
#pleaseStopWritingDynamicSql @ sql = @ sql + 'some string' hurts my eyes — granadaCoder, Sep 06 '16 at 20:30
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Sep 06 '16 at 21:18

score 2 · Answer 1 · answered Sep 06 '16 at 19:50

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

Add stored procedure to Entity Framework

1 Answers1

Linked