How to call Stored Procedures in ASP.NET Core

Let's talk about how we can call Stored Procedures from our ASP.NET Core API using Entity Framework Core with an illustrating example.

Stored Procedures encapsulate complex queries or programming logic which, when executed on a database produces data results. These programming structures help developers in reusing the scripts whenever required, while creating opportunities for separating database programming from the upper tiers.

The point I’m trying to make here is that by having a separate database tier with its scripts separated from the web-server or app-server tiers, we can have a loosely coupled system where when a db operation requires tuning, the application doesn’t need to be modified.

While writing Stored Procedures is out of scope for us atleast now, let’s look at how we can invoke existing Stored Procedures in a database from our ASP.NET Core API using Entity Framework Core. We shall also look at how we can maintain Stored Procedures inside our API code-base and add them to a new database through code-first approach.

Getting Started – API Project that uses Stored Procedures

Let’s create a new ASP.NET Core Web API that does a simple GetOrCreate functionality on a UserProfiles table in the database. We begin by creating a new webapi application via dotnet CLI.

> dotnet new webapi --name SpReadersApi

Once created, Let’s add the necessary EntityFrameworkCore nuget packages. We shall use SQL Server as our database, so let’s add necessary support for the same. The csproj looks like this (you can update your project with the contents of and do a restore):

<Project Sdk="Microsoft.NET.Sdk.Web">


    <!-- EFCore tools needed for Migrations -->
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.0">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>

    <!-- EFCore support for SQL Server -->
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="6.0.0">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    <PackageReference Include="Swashbuckle.AspNetCore" Version="6.2.3" />


Once this is done, let’s add our Code First schema, which shall be created in our database.

using System.ComponentModel.DataAnnotations;
using Microsoft.EntityFrameworkCore;

namespace SpReadersApi.Models.Context
    public class AppDbContext : DbContext
        public AppDbContext(DbContextOptions<AppDbContext> options)
             : base(options)
        public DbSet<UserProfile> UserProfiles { get; set; }

    public class UserProfile
        public int Id { get; set; }
        public string EmailAddress { get; set; }
        public string PasswordHash { get; set; }
        public string OIdProvider { get; set; }
        public string OId { get; set; }

The property Id of the UserProfile doubles as the PrimaryKey inside the database Table, and so we decorate it with [Key] attribute.

Now that we have our Model ready, let’s configure EFCore to use this DbContext while connecting to the SQL Server database inside the ConfigureServices method of the Startup class.

services.AddScoped<IUserRepository, UserRepository>();
// normal style (until .NET 5)
services.AddDbContext<AppDbContext>(options =>

// .NET 6 Style

I’ve also added another Service UserRepository which encapsulates the database logic which we shall see in a moment.

The IUserRepository declares a single method that validates if a user exists in the database and if not exists just creates it and returns the Id.

namespace SpReadersApi.Providers.Repositories
    public interface IUserRepository
        Task<int> GetOrCreateUserAsync(UserProfile id);

The implementation of this interface IUserRepository internally calls two Stored Procedures sp_GetUser and sp_CreateUser to query user records from the database table and run an INSERT command into the UserProfiles table.

The Stored Procedure sp_GetUser looks like below:


CREATE OR ALTER PROCEDURE sp_GetUsers(@emailAddress VARCHAR(100), @passwordHash VARCHAR(100))
SELECT * FROM UserProfiles WHERE EmailAddress = @emailAddress AND PasswordHash = @passwordHash;

Ignoring the typical syntax of a Stored Procedure in PL/SQL, what we would like to observe is the parameters we’re passing into the procedure – emailAddress, passwordHash; and what happens inside the Stored Procedure – a SELECT query which shall return matching rows for the passed emailAddress and passwordHash combination. If none matched, no rows shall be returned from the database.

Since we’re dealing with Stored Procedures and by definition Stored Procedures won’t have a return type in their syntax – this doesn’t return anything specifically

Now let’s see how we shall call this Stored Procedure inside UserRepository.

Invoking a Stored Procedure that executes a Query

To execute a Stored Procedure and read the results of the query it executes inside it, we use a specific function on the Model which it is related to. In this case, we’re invoking on the UserProfiles entity and so we shall run it on the same.

The Stored Procedure is executed as below:

var emailAddressParam = new SqlParameter("@emailAddress", id.EmailAddress);
var passwordParam = new SqlParameter("@passwordHash", id.PasswordHash);

var users = context
            .FromSqlRaw("exec sp_GetUsers @emailAddress, @passwordHash", emailAddressParam, passwordParam)

In EntityFrameworkCore we have two methods for executing Stored Procedures –

  1. FromSqlRaw() – used to run query statements that return records from the database
  2. ExecuteSqlRaw() / ExecuteSqlRawAsync() – executes a command that can modify data on the database (typically DML commands like INSERT, UPDATE or DELETE)

In the above case, since we’re executing a Stored Procedure that queries for results, we use the former – FromSqlRaw().

The method FromSqlRaw() takes two parameters –

  1. An SQL string – in our case, it shall be the “exec sp_GetUser along with parameters” SQL statement to execute the Stored Procedure
  2. A series of the parameters to pass with this SQL statement – we need to pass objects of type SqlParameter() which map between the parameters we’re passing from our project to the actual parameter names which are defined in the SQL.

Note: Be careful about matching the parameter names and their types exactly between the Stored Procedure SQL definition and the names we’re passing in our code.

Our UserRepository code for fetching users from database looks like this:

public class UserRepository : IUserRepository
    private readonly AppDbContext context;

    public UserRepository(AppDbContext context)
        this.context = context;

    public async Task<int> GetOrCreateUserAsync(UserProfile id)
        if (id != null)
            // create parameters to pass to the exec Stored Procedure statement
            // and bind the parameters with the values passed
            var emailAddressParam = new SqlParameter("@emailAddress", id.EmailAddress);
            var passwordParam = new SqlParameter("@passwordHash", id.PasswordHash);

            // run the statement to exec Stored Procedure inside the database
            // and capture the return values
            var users = context
                        .FromSqlRaw("exec sp_GetUsers @emailAddress, @passwordHash", 
                            emailAddressParam, passwordParam)

            // The above Stored Procedure does the same job 
            // as what the below code does
            var users = context
                .Where(x => x.EmailAddress == id.EmailAddress 
                    && x.PasswordHash == id.PasswordHash).ToList(); 

            if (users.Count > 0)
                return users.First().Id;
                // Create a New User with the passed Details
            return id;

        return -1;

In our UserRepository, we still need to fill in the scenario where the user doesn’t exist and we need to insert a new user into the table. We do this by creating a Stored Procedure which takes in the passed values and inserts a new row in the table.

But we need to find out the Id of the newly created user, How?

Answer is, Output parameters.

Invoking a Stored Procedure with Output Parameters

Stored Procedures can’t return values by definition but we can get a value from the Stored Procedure by means of output parameters – whose state is modified after a Stored Procedure is executed and we can read the changed state of the identifier passed.

The Stored Procedure looks like below:


    @emailAddress VARCHAR(100), 
    @passwordHash VARCHAR(100), 
    @oidProvider VARCHAR(100), 
    @oid VARCHAR(100), 
    @Id int out)

INSERT INTO UserProfiles(
    VALUES(@emailAddress, @passwordHash, @oidProvider, @oid);

-- the state of the output param is set to the Id of the created user record
SELECT @Id = Id FROM UserProfiles WHERE EmailAddress = @emailAddress;


When we execute this Stored Procedure with some parameters to test, we can print the output parameter passed to see the value.


declare @userId as int;
exec sp_CreateUser '[email protected]', 'xHefhRAndftKdhdtc', 'Legacy', NULL, @userId out
select @userId

To call this Stored Procedure in our code, we repeat the same steps we followed before:

  1. We create SqlParameters, define the SQL Statement that executes the Stored Procedure and finally
  2. Call the ExecuteSqlRaw() method instead of the FromSqlRaw() method which is for SELECT queries.

What changes is how we handle the output parameter we need to pass in our SQL Statement for the Stored Procedure and how we read it.

The code snippet looks like this:

#GetOrCreateUserAsync() method#

if (users.Count > 0)
    return users.First().Id;
        // The above Stored Procedure does the same job 
        // as what the below code does
        var user = id;
        await context.UserProfiles.AddAsync(user);
        await context.SaveChangesAsync();
        return user.Id;

    // the emailAddress and passwordHash params are reused 
    // from their previous declaration in the method

    // define SqlParameters for the other two params to be passed
    var oidProviderParam = new SqlParameter("@oidProvider", id.OIdProvider);
    var oidParam = new SqlParameter("@oid", string.IsNullOrEmpty(id.OId) ? "" : id.OId);

    // define the output parameter that needs to be retained
    // for the Id created when the Stored Procedure executes 
    // the INSERT command
    var userIdParam = new SqlParameter("@Id", SqlDbType.Int);

    // the direction defines what kind of parameter we're passing
    // it can be one of:
    // Input
    // Output
    // InputOutput -- which does pass a value to Stored Procedure and retains a new state
    userIdParam.Direction = ParameterDirection.Output;
    // we can also use context.Database.ExecuteSqlCommand() or awaitable ExecuteSqlCommandAsync()
    // which also produces the same result - but the method is now marked obselete
    // so we use ExecuteSqlRawAsync() instead

    // we're using the awaitable version since GetOrCreateUserAsync() method is marked async
    await context.Database.ExecuteSqlRawAsync(
            "exec sp_CreateUser @emailAddress, @passwordHash, @oidProvider, @oid, @Id out", 
    // the userIdParam which represents the Output param
    // now holds the Id of the new user and is an Object type
    // so we convert it to an Integer and send
    return Convert.ToInt32(userIdParam.Value);

Finally, we have our API controller that is the entry point for this functionality as below:

public class UsersController : ControllerBase
    private readonly IUserRepository _repository;
    public UsersController(IUserRepository repository)
        _repository = repository;

    [HttpPost, Route("GetOrCreate")]
    public async Task<IActionResult> GetOrCreate([FromBody]UserProfile id)
        return Ok(await _repository.GetOrCreateUserAsync(id));

When we test this setup with some sample payload, we get the result as below:

POST /api/users/getorcreate HTTP/1.1
Host: localhost:5001
Content-Type: application/json

    "emailAddress":"[email protected]",



Pushing Stored Procedures via Code-First approach

In general scenarios, Stored Procedures are completely database related entities and are maintained at the database side. The API is aware of only the Stored Procedure names and its signature, so that it can pass parameters accordingly and look for results.

But scenarios of a CodeFirst approach, where the database is freshly created (although this is true for Proof-of-Concept kind of applications) from the application, we can push Stored Procedures via Migrations.

To do this, let’s take the InitialCreate migration in our API which is created when we first pushed our model into creating a database table.

> dotnet ef migrations add InitialCreate

We can then add our Stored Procedure script inside the migration as an entry in the Up() method.

public partial class InitialCreate : Migration
    private readonly string sp_GetUsers = @"CREATE OR ALTER PROCEDURE sp_GetUsers(
        @emailAddress VARCHAR(100), @passwordHash VARCHAR(100))
        SELECT * FROM UserProfiles 
        WHERE EmailAddress = @emailAddress AND PasswordHash = @passwordHash;

    protected override void Up(MigrationBuilder migrationBuilder)
            name: "UserProfiles",
            columns: table => new
                Id = table.Column<int>(nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                EmailAddress = table.Column<string>(nullable: true),
                PasswordHash = table.Column<string>(nullable: true),
                OIdProvider = table.Column<string>(nullable: true),
                OId = table.Column<string>(nullable: true)
            constraints: table =>
                table.PrimaryKey("PK_UserProfiles", x => x.Id);
        // runs an SQL statement while applying the Migration
        // in this case - creates a new Stored Procedure

    protected override void Down(MigrationBuilder migrationBuilder)
        name: "UserProfiles");

        // runs an SQL statement while applying the Migration
        // in this case - drops the existing Stored Procedure
        migrationBuilder.Sql("drop procedure dbo.sp_GetUsers");

To apply this migration we just run –

> dotnet ef database update

Found this article helpful? Please consider supporting!

Conclusion –

Stored Procedures are well known programming structures in the world of PL/SQL which encapsulate a set of SQL statements and provide re-usability. We have seen how we can execute an already existing Stored Procedure in the database our application connects to by using the features of EFCore and also looked at how we can use output parameters while invoking them. Finally we’ve seen how to create Stored Procedures along side tables while applying a migration using the Code First approach.

Some thoughts I wanted to put forward –

  1. Stored Procedures are ideal choice for scenarios where you need to run a complex SQL query or command on the database, where working with LINQ seems difficult and tedious.
  2. They also offer a better performance over LINQ-to-SQL approach that EFCore applies when we use LINQ syntax for database operations.
  3. These are typically under a Database developer’s bucket – so an API developer doesn’t need to worry about any performance aspects of a Stored Procedure’s execution and it also provides a clean separation of responsibilities between the domain layer and the application.
  4. On the other hand, LINQ provides a quick solution for scenarios where we use simpler SQL statements and queries in our application and one application entity relates to only one domain entity – although we still have a little performance disadvantage over having the database run the SQL queries through Stored Procedures.

What do you think of calling Stored Procedures via Entity Framework Core?

Let me know via comments below.

Official Documentation – Raw SQL Queries

Default image
Sriram Mannava

I'm a full-stack developer and a software enthusiast who likes to play around with cloud and tech stack out of curiosity.

Leave a Reply

Your email address will not be published.