.NET MVC: Using Stored Procedure in Entity Framework

Usually, one of the key point of using Entity Framework is to reduce the need to write SQL.
Sometimes, however, you want to use stored procedures, returning to write in SQL.

Maybe you are working with a legacy database or maybe you simple like this approach.

In this tutorial we will see how to implement a stored procedure, used for CRUD operations, and access the return value of the operation.

Contents for .NET MVC: Using Stored Procedure in Entity Framework

1 The database
2 Create the Stored Procedure
3 Import the procedure
- 3.1 Database First
- 3.2 Code first
4 Return a scalar value
5 CRUD Operations
6 Conclusion

The database

We will refer to a very simple database structure.

Create the Stored Procedure

Open the connection to your database in Server Explorer and select New Query

Now, it’s time to create the stored procedure using SQL

CREATE PROCEDURE [dbo].[GetRobotDogsByLocation]
-- Parameters
@Location nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;

SELECT d.RobotDogId, d.Name, d.Armed, f.Location
FROM RobotDogs d
JOIN RobotFactories f ON d.RobotFactoryId = f.RobotFactoryId
WHERE f.Location LIKE @Location
END

Execute the query and the procedure will be created.

Import the procedure

Now, the stored procedure is in the database, and we want to use it in our context class.
Let’s consider two different methods, using the database first or the code first approach.

Database First

Open the edmx file and Right click -> Update Model from Database..

(If you don’t have an edmx file yet, you should create a ADO.NET object first)

Select Stored Procedures and Functions and click Finish:

Now, if you look in your context class:

You can see that a new method has been added:

public virtual ObjectResult GetRobotDogsByLocation(string location)
{
var locationParameter = location != null ?
new ObjectParameter("Location", location) :
new ObjectParameter("Location", typeof(string));

return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("GetRobotDogsByLocation", locationParameter);
}

You can also find a new file, GetRobotDogsByLocation_Result, which reflects the return type of the stored procedure (in this case we just returned the RobotDog fields).

This newly created stored procedure trembles to be used :

using (RobotIndustryEntities context = new RobotIndustryEntities())
{
var dogs = context.GetRobotDogsByLocation("Texas");
}

Code first

When we call the stored procedure, we get a return value. We need a class that maps to that return value. In the database first approach that class is automatically generated (GetRobotDogsByLocation_Result).

In the code first approach we have to create a class for that purpouse.

public class RobotDogLocation
{
public int RobtDogID { get; set; }
public string Name { get; set; }
public bool Armed { get; set; }
public string Location { get; set; }
}

And we are ready to use the stored procedure!

using (var context = new RobotContext())
{
var locationParameter = new SqlParameter("@Location", "Texas");
var dogs = context.Database.SqlQuery("GetRobotDogsByLocation", locationParameter);
}

Return a scalar value

Not all the stored procedures may return an entity type.
For example, suppose that you want to return the number of robot dogs that are armed.

A simple stored procedure may be:

CREATE PROCEDURE [dbo].[GetArmedRobotDogs]
AS
BEGIN
SET NOCOUNT ON;

SELECT COUNT(*)
FROM RobotDogs
WHERE Armed = 1
END

Repeat the previous steps to import this powerful procedure in your code.
Now, returning a scalar value (an int in this case) is really simple:

using (var context = new RobotContext())
{
var armedDogs = context.Database.SqlQuery("GetArmedRobotDogs");
}

The SqlQuery method takes a Type which states the return type of the query.

CRUD Operations

While in the previous procedures we only returned some data, we can also use them for other CRUD operations (Create, Read, Update, Delete).

Let’s make some examples.

Create procedure

CREATE PROCEDURE [dbo].[CreateRobotDog]
-- Parameters
@Name VARCHAR(50),
@Armed bit,
@RobotFactoryId INT
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO RobotDogs([Name], [Armed], [RobotFactoryId])
VALUES(@Name, @Armed, @RobotFactoryId)

SELECT SCOPE_IDENTITY() AS RobotDogId
END

The SCOPE_IDENTITY function returns the last identity value that has been inserted. In this case it’s the RobotDogId, which is automatically generated.
This may be useful if we want to use the object inserted for further operations.

Update procedure

CREATE PROCEDURE [dbo].[UpdateRobotDog]
-- Parameters
@RobotDogId INT,
@Name VARCHAR(50),
@Armed bit,
@RobotFactoryId INT
AS
BEGIN
SET NOCOUNT ON;

UPDATE RobotDogs
SET Name = @Name, Armed = @Armed, RobotFactoryId = @RobotFactoryId
WHERE RobotDogId = @RobotDogId
END

For updating the fierce beast we need a way to identify it.
For that, we pass to the procedure the RobotDogId, which is the primary key for the RobotDogs table.

Delete procedure

CREATE PROCEDURE [dbo].[DeleteRobotDog]
-- Parameters
@RobotDogId INT
AS
BEGIN
SET NOCOUNT ON;

DELETE FROM RobotDogs
WHERE RobotDogId = @RobotDogId
END

We identify the dog to be removed passing the RobotDogId key.

Using the CRUD operations

Database First Approach

After importing the procedures (using the Update Model from Database as before), we are ready to use them with the Contexts methods, passing the desired parameters.

using (var context = new RobotContext())
{
//This creates the new entity and returns the generated Id
var studentId = context.CreateRobotDog("Marbot", true, 2);

//This deletes the entity.
context.DeleteRobotDog(robotId);
}

DeleteRobotDog return an int, which indicates the number of rows affected by the query.

Code First Approach

We don’t have the methods in our context class, but we can directly call the function, passing the parameters:

using (var context = new RobotContext())
{
db.Database.SqlQuery("CreateRobotDog @Name, @Armed, @RobotFactoryId",
new SqlParameter("Name", "Marbot"),
new SqlParameter("Armed", true),
new SqlParameter("RobotFactoryId", 2));
}

Conclusion

That was just a quick glance in the world of Stored Procedures.
Most of the times you would rather use something like LINQ to make your job easier, but sometimes you still have to use stored procedures.
In any case, they could be a powerful ally in the database management.