Cannot execute stored procedure with ADO.NET Entity Framework

Question

Cannot execute stored procedure with ADO.NET Entity Framework

.net ado.net entity-framework stored-procedures vb.net

Question

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.

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()

ShawnCBerg

Popular Answer

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

Daniel O

View more on Stack Overflow

Accepted Answer

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.

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

Cannot execute stored procedure with ADO.NET Entity Framework

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related