无法使用ADO.NET Entity Framework执行存储过程

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.

我创建了一个简单的无参数存储过程,我在下面粘贴了它。我已将存储过程导入到我的实体模型并创建了一个Function Import。永远不会创建模型中的函数,我无法使用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()

7 个解决方案

#1

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

首先将它添加到模型中,然后转到Entity Container,然后转到Import the function。

Full details here:

详情如下:

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

#2

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 really isn'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
'==

#3

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,然后单击add function import,在这里选择你的SP,然后选择它返回的类型,如果它没有返回任何内容,则选择'None'。如果它返回一个sclar类型,则选择类型,如果它返回一个记录集,则选择complex。点击获取列信息,它会显示返回的列。单击“创建新的复杂类型”。然后单击确定。如果它返回其中一个实体,则单击选项实体,然后从列表框中选择实体类型。

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 _ in _sities.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/

#4

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 Connection property 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代码调用存储过程。

#5

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 click - inport函数时,你可以选择一个存储过程,但是它要求返回类型为none,标量或实体类型(例如,基于数据库中的表)。如果您愿意,可以添加复杂类型。右键单击模型 - 选择添加 - 复杂类型。然后,您可以定义列及其数据类型。

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

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

#6

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数据库,我不知道它是否真的适用于实体框架,因为我使用它给我一些问题并且它不是那么高效。

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:

但无论如何,如果我(函数)导入一个带有一些插入操作的存储过程,由于某种原因没有任何反应。换句话说,如果我添加到我的模型(EDCX)这样的SP:

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!

实际上什么都没发生。什么都没有。既不是例外也不是结果也不是插入,everthing是完美的,没有警告没有编译错误。有趣的是,如果我删除存储过程脚本,删除工作!但没有插入!

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提供程序中的错误吗?或者我必须设置我错过的东西?

#7

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返回一组与实体定义匹配的列来使其工作。

#1