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

The database

We will refer to a very simple database structure.

robot edmx

Create the Stored Procedure

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

query on db

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:

update stored procedure

Now, if you look in your context class:

context

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<RobotDogLocation>("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<int>("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<int>("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.

Do you still use them sometimes?