In this article, we'll walk through the process of organizing and using stored procedures in a .NET 8 Web API project. We'll cover creating a new class library for persistence, defining entity classes and configurations, and using stored procedures within the controller API. We'll be using PostgreSQL for Entity Framework Core in the examples.
---
title: How to organize and use EF Core Stored Procedures in your project
subtitle: In this article, we'll walk through the process of organizing and using stored procedures in a .NET 8 Web API project. We'll cover creating a new class library for persistence, defining entity classes and configurations, and using stored procedures within the controller API. We'll be using PostgreSQL for Entity Framework Core in the examples.
author: Jon LaBelle
date: October 8, 2024
source: https://jonlabelle.com/snippets/view/markdown/how-to-organize-and-use-ef-core-stored-procedures-in-your-project
notoc: false
---
## Solution structure
Here's the project and file structure for the `BlogApi` and `BlogApi.Persistence` projects.
### `BlogApi`
#### File structure
```plaintext
BlogApi/
├── Controllers/
│ └── UsersController.cs
├── Program.cs
├── appsettings.json
├── BlogApi.csproj
└── Properties/
└── launchSettings.json
```
#### File descriptions
- **BlogApi/**: The main Web API project.
- **Controllers/**: Contains the API controllers.
- `UsersController.cs`: The controller to handle user-related API endpoints.
- `Program.cs`: The entry point of the application.
- `appsettings.json`: Configuration file for the application.
- `BlogApi.csproj`: The project file for the Web API.
- **Properties/**: Contains project properties.
- `launchSettings.json`: Configuration for launching the application.
### `BlogApi.Persistence`
#### File structure
```plaintex
BlogApi.Persistence/
├── Data/
│ ├── Contexts/
│ │ └── BlogContext.cs
│ ├── Seeders/
│ │ └── DatabaseSeeder.cs
├── Entities/
│ ├── Role.cs
│ ├── User.cs
│ ├── UserRole.cs
│ └── StoredProcedureResults/
│ └── UserResult.cs
├── Configuration/
│ ├── RoleConfiguration.cs
│ ├── UserConfiguration.cs
│ ├── UserRoleConfiguration.cs
│ └── StoredProcedureResults/
│ └── UserResultConfiguration.cs
├── CustomMigrations/
│ ├── Operations/
│ │ └── StoredProcedureOperation.cs
│ ├── SqlGenerators/
│ │ └── NpgsqlCreateStoredProcedureOperationSqlGenerator.cs
│ └── StoredProcedures/
│ └── AddUserStoredProcedure.cs
│ └── GetUsersStoredProcedure.cs
├── Migrations/
│ ├── 20231010123456_InitialCreate.cs
│ └── 20231010123456_InitialCreate.Designer.cs
└── BlogApi.Persistence.csproj
```
#### File descriptions
- **BlogApi.Persistence/**: The class library project for persistence.
- **Data/**: Contains data-related classes.
- **Contexts/**: Contains the DbContext class.
- `BlogContext.cs`: The DbContext class for the application.
- **Seeders/**: Contains data seeding classes.
- `DatabaseSeeder.cs`: Class to seed initial data into the database.
- **Entities/**: Contains entity classes.
- `Role.cs`: Entity class for roles.
- `User.cs`: Entity class for users.
- `UserRole.cs`: Entity class for user roles.
- **StoredProcedureResults/**: Contains classes for stored procedure results.
- `UserResult.cs`: Class to map the result of the `get_users` stored procedure.
- **Configuration/**: Contains entity configuration classes.
- `RoleConfiguration.cs`: Configuration for the `Role` entity.
- `UserConfiguration.cs`: Configuration for the `User` entity.
- `UserRoleConfiguration.cs`: Configuration for the `UserRole` entity.
- **StoredProcedureResults/**: Contains configuration classes for stored procedure results.
- `UserResultConfiguration.cs`: Configuration for the `UserResult` entity.
- **CustomMigrations/**: Contains custom migration operations and SQL generators.
- **Operations/**: Contains custom migration operations.
- `StoredProcedureOperation.cs`: Abstract class for stored procedure operations.
- **SqlGenerators/**: Contains custom SQL generators.
- `NpgsqlCreateStoredProcedureOperationSqlGenerator.cs`: Custom SQL generator for creating stored procedures.
- **StoredProcedures/**: Contains stored procedure definitions.
- `AddUserStoredProcedure.cs`: Definition for the `add_user` stored procedure.
- `GetUsersStoredProcedure.cs`: Definition for the `get_users` stored procedure.
- **Migrations/**: Contains migration files.
- `20231010123456_InitialCreate.cs`: Migration file for the initial database schema.
- `20231010123456_InitialCreate.Designer.cs`: Designer file for the initial migration.
- `BlogApi.Persistence.csproj`: The project file for the class library.
## Step 1: Create the Projects
1. **Create the Web API project**:
```sh
dotnet new webapi -n BlogApi
```
2. **Create the class library project for persistence**:
```sh
dotnet new classlib -n BlogApi.Persistence
```
3. **Add the class library project reference to the Web API project**:
```sh
cd BlogApi
dotnet add reference ../BlogApi.Persistence
```
## Step 2: Define the Entity Classes and Configurations
### BlogApi.Persistence/Entities/Role.cs
```csharp
using System;
using System.Collections.Generic;
namespace BlogApi.Persistence.Entities
{
public class Role
{
public Guid Id { get; set; }
public string RoleName { get; set; }
public ICollection<UserRole> UserRoles { get; set; } = new List<UserRole>();
}
}
```
### BlogApi.Persistence/Entities/User.cs
```csharp
using System;
using System.Collections.Generic;
namespace BlogApi.Persistence.Entities
{
public class User
{
public Guid Id { get; set; }
public string Username { get; set; }
public ICollection<UserRole> UserRoles { get; set; } = new List<UserRole>();
}
}
```
### BlogApi.Persistence/Entities/UserRole.cs
```csharp
using System;
namespace BlogApi.Persistence.Entities
{
public class UserRole
{
public Guid UserId { get; set; }
public User User { get; set; }
public Guid RoleId { get; set; }
public Role Role { get; set; }
}
}
```
### BlogApi.Persistence/Entities/StoredProcedureResults/UserResult.cs
```csharp
using System;
namespace BlogApi.Persistence.Entities.StoredProcedureResults
{
public class UserResult
{
public Guid Id { get; set; }
public string Username { get; set; }
}
}
```
### BlogApi.Persistence/Configuration/RoleConfiguration.cs
```csharp
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using BlogApi.Persistence.Entities;
namespace BlogApi.Persistence.Configuration
{
public class RoleConfiguration : IEntityTypeConfiguration<Role>
{
public void Configure(EntityTypeBuilder<Role> builder)
{
builder.HasKey(r => r.Id);
builder.Property(r => r.RoleName).IsRequired().HasMaxLength(100);
}
}
}
```
### BlogApi.Persistence/Configuration/UserConfiguration.cs
```csharp
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using BlogApi.Persistence.Entities;
namespace BlogApi.Persistence.Configuration
{
public class UserConfiguration : IEntityTypeConfiguration<User>
{
public void Configure(EntityTypeBuilder<User> builder)
{
builder.HasKey(u => u.Id);
builder.Property(u => u.Username).IsRequired().HasMaxLength(100);
}
}
}
```
### BlogApi.Persistence/Configuration/UserRoleConfiguration.cs
```csharp
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using BlogApi.Persistence.Entities;
namespace BlogApi.Persistence.Configuration
{
public class UserRoleConfiguration : IEntityTypeConfiguration<UserRole>
{
public void Configure(EntityTypeBuilder<UserRole> builder)
{
builder.HasKey(ur => new { ur.UserId, ur.RoleId });
builder.HasOne(ur => ur.User)
.WithMany(u => u.UserRoles)
.HasForeignKey(ur => ur.UserId);
builder.HasOne(ur => ur.Role)
.WithMany(r => r.UserRoles)
.HasForeignKey(ur => ur.RoleId);
}
}
}
```
### BlogApi.Persistence/Configuration/StoredProcedureResults/UserResultConfiguration.cs
```csharp
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using BlogApi.Persistence.Entities.StoredProcedureResults;
namespace BlogApi.Persistence.Configuration.StoredProcedureResults
{
public class UserResultConfiguration : IEntityTypeConfiguration<UserResult>
{
public void Configure(EntityTypeBuilder<UserResult> builder)
{
builder.HasNoKey();
builder.ToTable((string)null); // No table mapping
}
}
}
```
## Step 3: Define the DbContext
### BlogApi.Persistence/Data/Contexts/BlogContext.cs
```csharp
using Microsoft.EntityFrameworkCore;
using BlogApi.Persistence.Entities;
using BlogApi.Persistence.Entities.StoredProcedureResults;
using BlogApi.Persistence.Configuration;
using BlogApi.Persistence.Configuration.StoredProcedureResults;
using BlogApi.Persistence.CustomMigrations.StoredProcedures;
namespace BlogApi.Persistence.Data.Contexts
{
public class BlogContext : DbContext
{
public BlogContext(DbContextOptions<BlogContext> options) : base(options) { }
public DbSet<User> Users { get; set; }
public DbSet<Role> Roles { get; set; }
public DbSet<UserRole> UserRoles { get; set; }
public DbSet<UserResult> UserResults { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.ApplyConfiguration(new UserConfiguration());
modelBuilder.ApplyConfiguration(new RoleConfiguration());
modelBuilder.ApplyConfiguration(new UserRoleConfiguration());
modelBuilder.ApplyConfiguration(new UserResultConfiguration());
modelBuilder.HasDbFunction(() => CreateStoredProcedures());
}
private void CreateStoredProcedures()
{
var getUsersProcedure = new GetUsersStoredProcedure();
var addUserProcedure = new AddUserStoredProcedure();
Database.ExecuteSqlRaw(getUsersProcedure.Sql);
Database.ExecuteSqlRaw(addUserProcedure.Sql);
}
}
}
```
## Step 4: Define Custom Migration Operations and SQL Generators
### BlogApi.Persistence/CustomMigrations/Operations/StoredProcedureOperation.cs
```csharp
using Microsoft.EntityFrameworkCore.Migrations.Operations;
namespace BlogApi.Persistence.CustomMigrations.Operations
{
public abstract class StoredProcedureOperation : MigrationOperation
{
public abstract string Sql { get; }
}
}
```
### BlogApi.Persistence/CustomMigrations/SqlGenerators/NpgsqlCreateStoredProcedureOperationSqlGenerator.cs
```csharp
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Operations;
using Npgsql.EntityFrameworkCore.PostgreSQL.Migrations;
using BlogApi.Persistence.CustomMigrations.Operations;
namespace BlogApi.Persistence.CustomMigrations.SqlGenerators
{
public class NpgsqlCreateStoredProcedureOperationSqlGenerator : NpgsqlMigrationsSqlGenerator
{
public NpgsqlCreateStoredProcedureOperationSqlGenerator(MigrationsSqlGeneratorDependencies dependencies)
: base(dependencies)
{
}
protected override void Generate(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
{
if (operation is StoredProcedureOperation storedProcedureOperation)
{
builder.AppendLine(storedProcedureOperation.Sql);
builder.EndCommand();
}
else
{
base.Generate(operation, model, builder);
}
}
}
}
```
### BlogApi.Persistence/CustomMigrations/StoredProcedures/GetUsersStoredProcedure.cs
```csharp
using BlogApi.Persistence.CustomMigrations.Operations;
namespace BlogApi.Persistence.CustomMigrations.StoredProcedures
{
public class GetUsersStoredProcedure : StoredProcedureOperation
{
public override string Sql => @"
CREATE OR REPLACE FUNCTION get_users()
RETURNS TABLE(id UUID, username TEXT) AS $$
BEGIN
RETURN QUERY SELECT id, username FROM ""Users"";
END;
$$ LANGUAGE plpgsql;
";
}
}
```
### BlogApi.Persistence/CustomMigrations/StoredProcedures/AddUserStoredProcedure.cs
```csharp
using BlogApi.Persistence.CustomMigrations.Operations;
namespace BlogApi.Persistence.CustomMigrations.StoredProcedures
{
public class AddUserStoredProcedure : StoredProcedureOperation
{
public override string Sql => @"
CREATE OR REPLACE FUNCTION add_user(user_id UUID, user_name TEXT)
RETURNS VOID AS $$
BEGIN
INSERT INTO ""Users"" (id, username) VALUES (user_id, user_name);
END;
$$ LANGUAGE plpgsql;
";
}
}
```
## Step 5: Create the Migration and Update the Database
Run the following commands to create the migration and update the database:
```sh
cd BlogApi.Persistence
dotnet ef migrations add InitialCreate
dotnet ef database update
```
### Example Migration Files
#### BlogApi.Persistence/Migrations/20231010123456_InitialCreate.cs
```csharp
// removed for brevity
```
#### BlogApi.Persistence/Migrations/20231010123456_InitialCreate.Designer.cs
```csharp
// This file is auto-generated and contains metadata about the migration.
```
## Step 6: Use Stored Procedures in the Controller API
Create methods in your controller to call the stored procedures and return the results.
### BlogApi/Controllers/UsersController.cs
```csharp
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using BlogApi.Persistence.Data.Contexts;
using BlogApi.Persistence.Entities;
using BlogApi.Persistence.Entities.StoredProcedureResults;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace BlogApi.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class UsersController : ControllerBase
{
private readonly BlogContext _context;
public UsersController(BlogContext context)
{
_context = context;
}
// GET: api/Users
[HttpGet]
public async Task<ActionResult<IEnumerable<UserResult>>> GetUsers()
{
var users = await _context.UserResults.FromSqlRaw("SELECT * FROM get_users()").ToListAsync();
return Ok(users);
}
// POST: api/Users
[HttpPost]
public async Task<IActionResult> AddUser(Guid userId, string userName)
{
await _context.Database.ExecuteSqlRawAsync("SELECT add_user({0}, {1})", userId, userName);
return Ok();
}
}
}
```
## Step 7: Configure the Web API Project
### BlogApi/Program.cs
```csharp
using Microsoft.EntityFrameworkCore;
using BlogApi.Persistence.Data.Contexts;
using BlogApi.Persistence.Data.Seeders;
using BlogApi.Persistence.CustomMigrations.SqlGenerators;
using Npgsql.EntityFrameworkCore.PostgreSQL.Infrastructure;
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
builder.Services.AddControllers();
builder.Services.AddDbContext<BlogContext>(options =>
options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection"),
npgsqlOptions => npgsqlOptions.MigrationsSqlGenerator<NpgsqlCreateStoredProcedureOperationSqlGenerator>()));
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
var app = builder.Build();
// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
app.UseSwagger();
app.UseSwaggerUI();
}
app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
using (var scope = app.Services.CreateScope())
{
var services = scope.ServiceProvider;
var context = services.GetRequiredService<BlogContext>();
context.Database.EnsureCreated();
DatabaseSeeder.SeedData(context);
Console.WriteLine("Database created and seeded successfully!");
}
app.Run();
```
### BlogApi/appsettings.json
```json
{
"ConnectionStrings": {
"DefaultConnection": "Host=localhost;Database=blogdb;Username=yourusername;Password=yourpassword"
},
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"AllowedHosts": "*"
}
```
## Conclusion
By following these steps, you can create a .NET 8 Web API project and a class
library project from scratch, organize your code effectively, and use stored
procedures within your API. This approach helps maintain a clean project
structure and makes it easier to manage database operations.