将存储过程添加到实体框架 [英] Add stored procedure to Entity Framework

问题描述

我有一个动态生成自己的存储过程，详细地我将动态添加到order by子句。

这是我的存储过程：

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

我试图将这个存储过程添加到实体框架，但没有成功。实体框架无法为我的存储过程创建一个复杂的类型，我点击获取列信息按钮，但下面的文本框表示所选的存储过程不返回列。

你知道有什么问题吗？

如果我从 @sql 中删除​​参数起作用，

解决方案

可能是因为您的过程中没有适当的SQL语句，Entity Framework可以通过该SQL语句检测结果列。

解决方法是在过程结束时放置SQL Select语句这应该告诉实体框架的结果，例如在程序结束时放入以下语句并更新它

  SELECT 
 CAST（1 AS int）AS ID 
，CAST（1 AS int）AS [NO] 
，N'Fist名称'AS名字
，N'Lasat名称'AS姓氏
，CAST（1 AS int）AS ReferanceID 
，CAST（'AS VARCHAR（MAX））AS ReferonsNO 
  

之后，进入实体框架并导入过程，它应该正确显示列。一旦您在实体框架中导入了SP，请返回到过程并注释掉我们刚刚添加的最后一个 SELECT 语句。最后一个声明只是为了实体框架来理解SP将要返回的。

P.S。我不清楚列类型，因此您应该更好地更改选择语句

中的列类型

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

解决方案

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

这篇关于将存储过程添加到实体框架的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！