无法使用 ADO.NET 实体框架执行存储过程

Cannot execute stored procedure with ADO.NET Entity Framework

提问by ShawnCBerg

I've created a simple parameterless stored procedure which I've pasted below. I've imported the stored procedure to my Entity Model and created a Function Import. The function in the model is never created and I am unable to execute this stored procedure using the ADO.NET Entity Framework. I've opened the .edmx file in XML view and have confirmed there are no errors regarding this stored procedure. What am I doing wrong? Is it really impossible to call such a simple stored procedure from the Entity Framework? I've set the return type for the import function to None seeing this stored procedure does not need to return any recordsets or values.

我创建了一个简单的无参数存储过程，粘贴在下面。我已将存储过程导入到我的实体模型并创建了一个函数导入。模型中的函数从未创建，我无法使用 ADO.NET 实体框架执行此存储过程。我已经在 XML 视图中打开了 .edmx 文件，并确认没有关于此存储过程的错误。我究竟做错了什么？从实体框架中调用这样一个简单的存储过程真的是不可能的吗？我已将导入函数的返回类型设置为 None 看到此存储过程不需要返回任何记录集或值。

Stored Procedure:

存储过程：

ALTER PROC [dbo].[Inventory_Snapshot_Create]

AS

SET NOCOUNT ON

DECLARE @Inventory_Snapshot_ID int

INSERT INTO Inventory_Snapshots (snapshot_timestamp)
VALUES (GETDATE())

SET @Inventory_Snapshot_ID = SCOPE_IDENTITY()

INSERT INTO Inventory_Snapshot_Products (inventory_snapshot_id,
    idProduct, stock)

    SELECT @Inventory_Snapshot_ID, idProduct, stock
    FROM products


SET NOCOUNT OFF

Code trying to execute stored procedure:

尝试执行存储过程的代码：

Dim db As New MilkModel

db.Inventory_Snapshot_Create()

采纳答案by ShawnCBerg

Thanks, pmarflee.

谢谢，普玛弗利。

I actually came here to post my resolution to this and saw your response at the same time. This code actually uses the entity framework's connection and executes the stored procedure I imported into the model. Microsoft keeps pushing us developers to use Entity Framework instead of LINQ to SQL and other DAL generators but EF reallyisn't where it needs to be at all. I won't be using it in future projects until it's a more complete solution.

我实际上是来这里发布我的决议的，同时也看到了你的回复。这段代码实际上使用了实体框架的连接并执行了我导入到模型中的存储过程。Microsoft 不断推动我们的开发人员使用 Entity Framework 而不是 LINQ to SQL 和其他 DAL 生成器，但 EF确实根本不是它需要的地方。在它是一个更完整的解决方案之前，我不会在未来的项目中使用它。

Here's what I ended up doing:

这是我最终做的：

Dim db As New MilkModel

'==
'Begin dirty hack to execute parameterless/resultless stored
'procedure using Entity Framework (well, sort of using EF). 
'http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/44a0a7c2-7c1b-43bc-98e0-4d072b94b2ab/
'==
Dim con As DbConnection = db.Connection

con.Open()

Dim cmd As DbCommand = con.CreateCommand()

With cmd
    .CommandType = CommandType.StoredProcedure
    .CommandText = "MilkModel.Inventory_Snapshot_Create"
    .ExecuteNonQuery()
    .Dispose()
End With

con.Dispose()
'==
'End dirty hack
'==

回答by Daniel O

First you add it to the model, then you go to the Entity Container, then the Import the function.

首先将它添加到模型中，然后转到实体容器，然后是导入函数。

Full details here:

完整的细节在这里：

http://msdn.microsoft.com/en-us/library/bb896231.aspx

http://msdn.microsoft.com/en-us/library/bb896231.aspx

回答by Niraj

Any stored procedure can be called through EF. Create a SP and add it to your model. Further go to the model browser, right click the sp, and click add function import, here select your SP, and select what type it returns, if it returns nothing then select 'None'. If it returns a sclar type then select the type, and if its returning a recordset then select complex. Click on get column information, it will show you the colums returning. click on create new complex type. and click ok. If its returning one of you entities then click on option entity and select the entity type from the list box.

任何存储过程都可以通过 EF 调用。创建一个 SP 并将其添加到您的模型中。进一步进入模型浏览器，右键单击 sp，然后单击添加函数导入，这里选择您的 SP，并选择它返回的类型，如果它什么都不返回，则选择“无”。如果它返回一个 sclar 类型，则选择该类型，如果它返回一个记录集，则选择复杂。单击获取列信息，它将显示返回的列。单击创建新的复杂类型。并单击确定。如果它返回您的实体之一，则单击选项实体并从列表框中选择实体类型。

now you call call it in your code.

现在你在你的代码中调用它。

if its returning a complex type use code:

如果它返回复杂类型使用代码：

MyEntities _entities = new MyEntities(); var p = from d in _entities.GetOrderInfo() select d;

MyEntities _entities = new MyEntities(); var p = from d in _entities.GetOrderInfo() select d;

if its returning nothing then use code: MyEntities _entities = new MyEntities(); _entities.Cancel_Sale(ucode, oid);

如果它什么都不返回，那么使用代码： MyEntities _entities = new MyEntities(); _entities.Cancel_Sale(ucode, oid);

for further queries visit

如需进一步查询，请访问

http://dubeyniraj.blogspot.com/

http://dubeyniraj.blogspot.com/

回答by pmarflee

I don't think you can add a stored procedure to an EF model unless it is associated with a particular CRUD operation on an entity. However you can reference the Connectionproperty of your entity container object to access the underlying ADO.NET connection object that the EF is using. You can then call your stored procedure using traditional ADO.NET code.

我认为您不能将存储过程添加到 EF 模型，除非它与实体上的特定 CRUD 操作相关联。但是，您可以引用实体容器对象的Connection属性来访问 EF 正在使用的基础 ADO.NET 连接对象。然后您可以使用传统的 ADO.NET 代码调用您的存储过程。

回答by Doug Kirschman

In case anyone else wonders about this...

万一其他人对此感到疑惑......

The main problem with stored procs and the Entity Framework is that the return types need to be definted. For example, if you have a stored proc that returns soem rows from a table, the Entity Framework needs to be told what the structure of those rows looks like. It needs this so that it can generate all the proxy classes and serialize and deserialize things.

存储过程和实体框架的主要问题是需要定义返回类型。例如，如果您有一个从表中返回 soem 行的存储过程，则需要告知实体框架这些行的结构是什么样的。它需要这样才能生成所有代理类并序列化和反序列化事物。

When you go to the model - rigkt click - inport function, you can choose a stored proc, but it expects the return types to be none, scalar, or entity types (e.e. based off of the tables in your database). You can, if you wish, add a complex type. Right click model - choose add - Complex type. You then define the columns and their data types.

当您转到模型 - rigkt 单击 - 输入函数时，您可以选择存储过程，但它期望返回类型为无、标量或实体类型（ee 基于数据库中的表）。如果愿意，您可以添加复杂类型。右键单击模型 - 选择添加 - 复杂类型。然后定义列及其数据类型。

Once this is done, the class you just created will show up under the list of entity types mentioned above.

完成此操作后，您刚刚创建的类将显示在上述实体类型列表下。

回答by Alexian

For my project I'm actually using a FirebirdSql DB, I dunno if it's really right for Entity Framework, since I used it give me some issues and it's not so performant.

对于我的项目，我实际上使用的是 FirebirdSql DB，我不知道它是否真的适合 Entity Framework，因为我使用它给我带来了一些问题，而且性能不是很好。

But anyway if I (function) import a stored procedure with some insert operations within, for some reason nothing happens. In other words if I add to my model (EDCX) such SP:

但无论如何，如果我（函数）导入一个带有一些插入操作的存储过程，由于某种原因什么也没有发生。换句话说，如果我将这样的 SP 添加到我的模型 (EDCX) 中：

create procedure insert_stuff(thing varchar(40))
returns response(50)
as
begin
    insert into stuffs(thing) values (thing);

    response = 'done!';

    suspend;
end;

and then I call it in this way:

然后我这样称呼它：

FirebirdContext.InsertStuff("Hi there!"); // it should return an ObjectResult<String>
FirebirdContext.SaveChanges();

well nothing actually happens. Nothing at all. Neither exceptions nor results nor inserts, everthing is perfect, no warnings no compile errors. The funny thing is that if I put a delete into the stored procedure script, the delete works!! But no inserts!

好吧，实际上什么也没发生。什么都没有。既没有异常也没有结果也没有插入，一切都是完美的，没有警告没有编译错误。有趣的是，如果我将删除放入存储过程脚本中，则删除有效！！但是没有插入！

The table is very simple as shown and I already imported other stored procedures and they work, but because they contain queries, updates and delete, in the case they got insert... no way! Could it be a bug in the Firebird Ado.NET Provider? Or do I have to set something I've missed?

如图所示，该表非常简单，我已经导入了其他存储过程并且它们可以工作，但是因为它们包含查询、更新和删除，在它们被插入的情况下......不可能！会不会是 Firebird Ado.NET Provider 中的一个错误？还是我必须设置一些我错过的东西？

回答by pablop

if want to avoid using a DBCommand, I made it work by making the SP return some set of columns that match with an entity definition.

如果想避免使用 DBCommand，我通过使 SP 返回一些与实体定义匹配的列集来使其工作。