C# 在实体框架中添加存储过程复杂类型

Adding stored procedures complex types in Entity Framework

提问by Kavitha

I am trying to use a stored procedure in Entity Framework that returns nothing.

我试图在实体框架中使用一个不返回任何内容的存储过程。

I did the following:

我做了以下事情：

1. Added a function (right click on stored procedure -> add -> function import-> Complex Type -> Get column information -> create New Complex-Type)

2. My function name: summarySP_Result. After building the project the entity class is not generated in Generated_code(BusinessAccount.web.g.cs)

1. 添加了一个函数（右键存储过程->添加->函数导入->复杂类型->获取列信息->创建新的复杂类型）

2. 我的函数名称：summarySP_Result. 构建项目后实体类没有在Generated_code（BusinessAccount.web.g.cs）中生成

But entity classes for tables and views are all created but nor for stored procedure.

但是表和视图的实体类都是创建的，而不是存储过程。

Can anybody give the idea why it is not generated entity class in BusinessAccount.web.g.cs?

任何人都可以给出为什么它不在 中生成的实体类的想法BusinessAccount.web.g.cs吗？

Update :

更新 ：

Let me confirm ReturnDataFromTemTable_resultentity class created in your XXXXXX.web.g.csclass.

让我确认ReturnDataFromTemTable_result在您的XXXXXX.web.g.cs课程中创建的实体类。

Like :

喜欢 ：

[DataContract(Namespace="http://schemas.datacontract.org/2004/07/BizFramework.Web.Model")]
public sealed partial class ReturnDataFromTemTable_Result : Entity
{
   -------------------
 }

采纳答案by marc_s

OK - here's the step-by-step way of doing this:

好的 - 这是执行此操作的分步方法：

(1) add your stored procedure to the EDMX file (when you first create it, or later on by using Update model from databaseand picking that stored procedure)

(1) 将您的存储过程添加到 EDMX 文件中（当您第一次创建它时，或稍后通过使用Update model from database和选择该存储过程）

(2) once you have the stored procedure in your model - use the Model Browserto add a Function Import:

(2) 一旦你的模型中有存储过程 - 使用Model Browser添加一个Function Import：

(3) the next dialog that pops up is vitally important - you need to (1) define that the stored procedure returns a collection of complex types, then you need to (2) get the column info from that stored procedure to know what columns it will return, then (3) you tell Visual Studio to generate a new complex type based on that column info:

(3) 弹出的下一个对话框非常重要 - 您需要 (1) 定义存储过程返回复杂类型的集合，然后您需要 (2) 从该存储过程中获取列信息以了解哪些列它将返回，然后 (3) 您告诉 Visual Studio 根据该列信息生成一个新的复杂类型：

(4) once you've done that - you should now see the stored procedure in your conceptual model section in the Model Browser, and the newly generated complex type should show up there, too:

(4) 完成后 - 您现在应该在模型浏览器的概念模型部分看到存储过程，并且新生成的复杂类型也应该显示在那里：

回答by Chris Amelinckx

If this is still unresolved, after you Add the Function Import, go to the Solution Explorer, right click your {name}.Context.tt file and do "Run Custom Tool". The method will now show up in your derived Context class.

如果这仍未解决，在添加函数导入后，转到解决方案资源管理器，右键单击您的 {name}.Context.tt 文件并执行“运行自定义工具”。该方法现在将显示在您的派生 Context 类中。

This seems like a bug in Visual Studio 2012, which is what I am using, I haven't applied Update 1, I will try to see if that fixes it.

这似乎是 Visual Studio 2012 中的一个错误，这是我正在使用的，我还没有应用更新 1，我会尝试看看是否可以修复它。

回答by Sandeep

This is for Ross Brigoli

这是给罗斯·布里戈利的

Try adding this line to the beginning of your stored procedure:

尝试将此行添加到存储过程的开头：

SET FMTONLY OFF You can remove this after you have finished importing.

您已完成导入后SET FMTONLY关可以删除此。

Source:- Why can't Entity Framework see my Stored Procedure's column information?

来源： - 为什么不能实体框架看我的存储过程的列信息？

回答by Sandeep

This is my SP to Implement the multiple search

这是我实现多重搜索的 SP

***************************************************

    CREATE PROCEDURE [dbo].[uspSEARCH_POSITIONS]
        @OBJ_TYPE_REQUEST varchar(2000),--'FIRST_NAME;SEARCHVALUE|LAST_NAME;SEARCHVALUE|JOB_DESCRIPTION;SEARCHVALUE'
        @DELIMITER varchar(10) --'| Which seperates the col;searchvalue|col;searchvalue
    AS
    BEGIN
    SET FMTONLY OFF
        DECLARE
            @lLastName varchar(100),
            @lFirstName varchar(100),
            @lPositionNumber varchar(20),
            @lJobDescription varchar(50),
            @lJobCode varchar(20),
            @lOccupancyIndicator varchar(50),
            @ldeleimitercolsearchval varchar(10)

    SET @ldeleimitercolsearchval =';'

    CREATE TABLE #TempTable (ColSearchValues VARCHAR(2000))

    INSERT INTO #TempTable 
    SELECT * FROM [dbo].[fnSplit](@OBJ_TYPE_REQUEST,@DELIMITER)--'fname;searchvalfname|lname;searchvallname|jobcode;searchvaljobcode','|')

    SELECT @lLastName=SUBSTRING(ColSearchValues,CHARINDEX(@ldeleimitercolsearchval ,ColSearchValues)+1,LEN(ColSearchValues)) from #TempTable where lower(ColSearchValues) like '%last%'
    SELECT @lFirstName =SUBSTRING(ColSearchValues,CHARINDEX(@ldeleimitercolsearchval ,ColSearchValues)+1,LEN(ColSearchValues)) from #TempTable where lower(ColSearchValues) like '%first%'
    SELECT @lPositionNumber =SUBSTRING(ColSearchValues,CHARINDEX(@ldeleimitercolsearchval ,ColSearchValues)+1,LEN(ColSearchValues)) from #TempTable where lower(ColSearchValues) like '%position%'
    SELECT @lJobDescription=SUBSTRING(ColSearchValues,CHARINDEX(@ldeleimitercolsearchval ,ColSearchValues)+1,LEN(ColSearchValues)) from #TempTable where lower(ColSearchValues) like '%jobd%'
    SELECT @lJobCode=SUBSTRING(ColSearchValues,CHARINDEX(@ldeleimitercolsearchval ,ColSearchValues)+1,LEN(ColSearchValues)) from #TempTable where lower(ColSearchValues) like '%jobc%'
    SELECT @lOccupancyIndicator=SUBSTRING(ColSearchValues,CHARINDEX(@ldeleimitercolsearchval ,ColSearchValues)+1,LEN(ColSearchValues)) from #TempTable where lower(ColSearchValues) like '%ccupancy%'

           SELECT  [PS].[POSITION_NUMBER]
          ,[PS].[COST_CENTER]
          ,[PS].[JOB_CODE]
          ,[PS].[JOB_CODE_DESCRIPTION]
          ,[PS].[SITE_CODE]
          ,[EMP].[EMPLOYEE_ID]
          ,[EMP].[EIN]
          ,[EMP].[GRADE]
          ,[EMP].[LOGIN_ID]
          ,[EMP].[FIRST_NAME]
          ,[EMP].[LAST_NAME]
          ,LTRIM(RTRIM(ISNULL([EMP].[LAST_NAME],''))) + ',' +LTRIM(RTRIM(ISNULL([EMP].[FIRST_NAME],''))) AS [FULL_NAME]      
          ,[EMP].[DISTRICT]
          ,[EMP].[SUPERVISOR_EIN]
          ,COUNT(*) OVER() AS TOTAL_RECORD_COUNT
      FROM [DBSERVER].[dbo].[uvwPOSITION_SEARCH] PS
      LEFT JOIN [DBSERVER].[dbo].[uvwEMPLOYEES] EMP
      ON PS.POSITION_NUMBER=EMP.POSITION_NUMBER
      WHERE
            (@lLastName  IS NULL OR [LAST_NAME] LIKE '%' + @lLastName + '%')
        AND (@lFirstName IS NULL OR [FIRST_NAME] LIKE '%' + @lFirstName + '%')
        AND (@lPositionNumber IS NULL OR [PS].[POSITION_NUMBER] LIKE '%' + @lPositionNumber + '%')
        AND (@lJobDescription IS NULL OR [PS].[JOB_CODE_DESCRIPTION] LIKE '%' + @lJobDescription + '%')
        AND (@lJobCode IS NULL OR [PS].[JOB_CODE] LIKE '%' + @lJobCode + '%')
        AND (@lOccupancyIndicator IS NULL OR [EMP].[FILLED_VACANT] LIKE '%' + @lOccupancyIndicator + '%')

    END

---

---

Now you can consume above SP in edmx using below

现在您可以使用以下方法在 edmx 中消耗以上 SP

Adding stored procedures complex types in Entity Framework

在实体框架中添加存储过程复杂类型

Why can't Entity Framework see my Stored Procedure's column information?

为什么实体框架看不到我的存储过程的列信息？

And in case you have to update your SP below worked for me. Updating Complex Type if Stored Procedure Updates How Do I Get Entity Framework To Update Complex Types?

如果您必须更新下面的 SP 对我有用。如果存储过程更新，则更新复杂类型 如何获取实体框架以更新复杂类型？

回答by Jeff Moretti

For me, Im having problems where importing my Stored Procedure into EF is not generating the Complex Entity return object (automatically). I found however, after commenting out sections of my sproc (aka stored procedure), that when I then re-imported the stored procedure back in (ie refreshed using the Get Column Information button in the Function Import Edit screen), that the Complex type could then be generated!

对于我而言，我有地方进行导入我的存储过程到EF不会产生复杂的实体返回对象（自动）的问题。然而，我发现，在注释掉我的 sproc（又名存储过程）的部分之后，当我重新导入存储过程时（即使用函数导入编辑屏幕中的获取列信息按钮刷新），复杂类型然后可以产生！

In short, there could be a where clause (or maybe something else) causing EF to not generate the Complex Type. Try commenting out sections of your sproc and re-importing the sproc to

简而言之，可能有一个 where 子句（或者可能是其他东西）导致 EF 不生成复杂类型。尝试注释掉您的 sproc 部分并将 sproc 重新导入到

UPDATE:

更新：

Further to my investigation above, I found that the reason the Complex Entity was not being generated was because my sproc was using a view (instead of a typical table). For curiosity sake, I changed the view to another table just to see what would happen, and the complex entity generated.

进一步进行上述调查，我发现未生成复杂实体的原因是因为我的 sproc 正在使用视图（而不是典型的表）。出于好奇，我将视图更改为另一个表，只是为了看看会发生什么，并生成复杂的实体。

So, in short, it looks like Complex Entities might not generate automatically if you have a view. To try, I ripped out the view temporarily, re-import the sproc, generated the Complex Entity, then put the view back in. But now my code gives exceptions.

因此，简而言之，如果您有视图，则看起来复杂实体可能不会自动生成。为了尝试，我暂时删除了视图，重新导入 sproc，生成复杂实体，然后将视图放回去。但现在我的代码给出了异常。

Will update on this later when I learn more =)

稍后我会在了解更多信息时对此进行更新 =)

UPDATE:

更新：

Fixed the issue. Really silly mistake! The viewname that I was using was not spelled right =D. Im sort of angry that an error wasnt thrown by Sql Server when I created the sproc..... I guess that is life :) Alas, problem now fixed!

修复了问题。真是愚蠢的错误！我使用的视图名称拼写不正确 =D。我有点生气，当我创建 sproc 时，Sql Server 没有抛出错误......我想这就是生活:) 唉，问题现在解决了！

回答by Verard Sloggett

EF doesn't support importing stored procedures which build result set from:

EF 不支持导入从以下位置构建结果集的存储过程：

• Dynamic queries
• Temporary tables

• 动态查询
• 临时表

Re-write your stored procedure to use a table variable instead. remember to drop the stored procudure and function import from your model before updating as it wont generate the complex type unless it also adds the stored procedure. or go to function import properties and use the get column information feature after updating your stored procedure.

重新编写您的存储过程以改用表变量。请记住在更新之前从模型中删除存储过程和函数导入，因为它不会生成复杂类型，除非它还添加了存储过程。或转到函数导入属性并在更新存储过程后使用获取列信息功能。

回答by Ambala Chandrashekar

create procedure [dbo].[usp_InsertOrUpdate]
/*if your table(tbl_InsertOrUpdate) as 3 columns like uniqueid,col1,col2*/
@uniqueId bigint NULL,/*if insert send value as null or 0*/
@col1 bigint null,
@col2 [varchar](500) NULL
as
begin
set nocount ON
SET FMTONLY OFF
/* for giving result which column updated(uniqueId) and is it insert or update(IsInsert)*/
declare @varResult table (uniqueId bigint ,IsInsert bit )
/*create a var table before inserting original table*/

declare @varInsertOrUpdate table (
uniqueId bigint ,
col1 [bigint] ,
col2 [varchar]
)
/*default we are feel as update only*/
insert into @varResult (uniqueId,IsInsert) values (@uniqueId,0)
/*insert into var table*/
INSERT INTO @varInsertOrUpdate (uniqueId,col1,col2)
VALUES
(@uniqueId,@col1,@col2)
/*Insert into original table with where condition without if else*/
 INSERT INTO tbl_InsertOrUpdate (col1,col2)
 select col1,col2 from @varInsertOrUpdate
 where uniqueId!=0;
/*if its insert updating result returning table*/
 update @varResult set 
 uniqueId=IDENT_CURRENT('tbl_InsertOrUpdate'),
IsInsert=1 where @uniqueId=0;
/*updating table where @uniqueid is  null or empty*/
UPDATE tbl_InsertOrUpdate
SET col1=@col1, 
col2=@col2,
WHERE uniqueId=@uniqueId and @uniqueId!=0

select *  from @varResult

end

回答by Markus

As Sandeep said, EF doesn't support importing stored procedures which build result set from Dynamic queries or Temporary tables.

正如 Sandeep 所说，EF 不支持导入从动态查询或临时表构建结果集的存储过程。

But you don't have to rewrite your whole SP.

但是您不必重写整个 SP。

Just write another one, with the same name, that returns the correct row format without using dynamic SQL or a temp table. Then use the EF SP adding function, which will now auto generate the complex type.

只需编写另一个具有相同名称的函数，它在不使用动态 SQL 或临时表的情况下返回正确的行格式。然后使用 EF SP 添加功能，该功能现在将自动生成复杂类型。

Edit: It's actually easier to make a comment at the top of the SP that immediately selects the desired row with all the data types specified with CASTS. When you need to import the SP into EF, just uncomment the code.

编辑：在 SP 顶部进行评论实际上更容易，该评论立即选择具有 CASTS 指定的所有数据类型的所需行。当您需要将 SP 导入 EF 时，只需取消注释代码即可。

e.g.

例如

CREATE PROCEDURE myProc ()
AS
BEGIN
-- uncomment the following row to import:
-- SELECT CAST( 0 AS int) AS column1Name, CAST( 'a' AS varchar(50)) AS clumn2name

CREATE PROCEDURE myProc ()
AS
BEGIN
-- 取消注释以下要导入的行：
-- SELECT CAST( 0 AS int) AS column1Name, CAST('a' AS varchar(50)) AS clumn2name

-- comment out the SP content when you want to import it.
< proper SP content >
END

-- 要导入时将 SP 内容注释掉。
<适当的SP内容>
完

Then drop the stored proc and create the original.
Save this temporary importing SP you have made in case you need it again, though.

然后删除存储的过程并创建原始过程。
不过，请保存您制作的这个临时导入 SP，以防您再次需要它。

回答by Thilini

To add complex type correctly, go Model browser, right click on function, then display edit, click edit an fill the dialog box. The name of the function should be same as name of the stored procedure. Click OK button. Now function is created. Then right click on the created function and go edit again.There is a update button aside of complex type button.Update it using that update button. Now complex type is created completely.

要正确添加复杂类型，请转到模型浏览器，右键单击功能，然后显示编辑，单击编辑并填充对话框。函数名称应与存储过程名称相同。单击确定按钮。现在函数已创建。然后右键单击创建的函数并再次编辑。复杂类型按钮旁边有一个更新按钮。使用该更新按钮更新它。现在复杂类型已完全创建。

回答by Tapan

The issue of complex type not appearing may happen due to a different reason as well which is what I faced in our case. The issue was due to a syntax error in the SPROC where temp table was defined as below - create table #temp( col1 int, col2 nvarchar(100), col3 nvarchar(100), -- Notice the comma in the end ); Surprisingly, SQL Server doesn't throw any error when you compile the sproc. Removing the comma fixed the problem for us.

没有出现复杂类型的问题也可能由于不同的原因而发生，这就是我在我们的案例中遇到的。问题是由于 SPROC 中的语法错误，其中临时表定义如下 - create table #temp( col1 int, col2 nvarchar(100), col3 nvarchar(100), -- 请注意末尾的逗号 ); 令人惊讶的是，当您编译 sproc 时，SQL Server 不会抛出任何错误。删除逗号为我们解决了问题。

In short, while some of the above solutions might work depending on the specific issue, my suggestion is to check your sproc for such syntactical errors that SQL might ignore but could be the underlying reason for this problem. Thanks.

简而言之，虽然上述一些解决方案可能会根据具体问题起作用，但我的建议是检查您的 sproc 是否存在 SQL 可能忽略但可能是此问题的根本原因的语法错误。谢谢。