You can call Stored Procedure and Views using Entity Framework, I recently had to use one and didn’t find any good article describing this, so I am writing this one down.
Below is a Stored Procedure I have prepared for this example. As you can see in this stored procedure, there is 1 input parameter and 1 output parameter. I have tried to keep the stored proc as simple as possible 🙂
CREATE PROCEDURE SP_TEST_EF
@total INT OUTPUT
SELECT * FROM dbo.Users WHERE username = @username
SET @total = (SELECT COUNT(*) FROM dbo.Users)
Now that we have defined our stored proc, let move towards our next step.
Open your .edmx file and right click on the designer and select the “Update Model From Database”
then select the new stored procedure added from the list and select “Finish”.
Now right click on the designer again, and select the “Add -> Function Import” option this time.
Select the stored procedure from the list of Stored Procedures, give a nice function name I have given mine “Test_EF” now as I have my stored proc return a value I have used Complex Type. To create a complex type simply click on the “Create a New Complex Type” and Visual Studio will create one for you. Click ok.
And so if all is ok, you should be able to see the function added to your model browser as shown in the screenshot below.
And now you can use the stored procedure like any other table call,
using(var context = new SimplePmEntities())
var data = context.Test_EF("yasser", 0);
Hope you found this useful ! Cheers !