Cannot execute stored procedure with ADO.NET Entity Framework

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

Answer 1

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.

Answer 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.

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

Answer 3

This kind of solution doesn't work with ria service ?

Answer 4

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

Answer 5

That Don't work, some one has better idea of dong this