📝 How to Use Stored Procedures and Functions in EF Core with PostgreSQL (C# Examples)

By /

Entity Framework Core (EF Core) has become the go-to ORM (Object-Relational Mapper) for .NET developers, offering a clean, type-safe way to interact with databases. When paired with PostgreSQL — one of the most powerful open-source relational databases — you get a robust foundation for building scalable ASP.NET applications.

📝 How to Use Stored Procedures and Functions in EF Core with PostgreSQL (C# Examples)
📝 How to Use Stored Procedures and Functions in EF Core with PostgreSQL (C# Examples)

While EF Core’s LINQ queries handle most scenarios elegantly, there are times when stored procedures and functions provide better performance, encapsulation, or compatibility with existing database logic. In this comprehensive guide, we’ll explore how to leverage stored procedures and functions in EF Core with PostgreSQL using practical C# examples.

Why Use Stored Procedures and Functions?

Before diving into implementation, let’s understand when these database-level objects make sense:

Stored Procedures are ideal for:

  • Complex business logic that involves multiple operations
  • Batch processing and bulk data modifications
  • Operations requiring elevated database permissions
  • Legacy database integration where procedures already exist

Functions excel at:

  • Reusable calculations and data transformations
  • Complex filtering logic used across multiple queries
  • Returning computed values or table-valued results
  • Maintaining consistency in business rules

Project Setup

Prerequisites

Before we begin, ensure you have:

  • .NET 6.0 SDK or later installed
  • PostgreSQL 12+ running locally or remotely
  • Basic understanding of ASP.NET Core and C#
  • A code editor (Visual Studio, VS Code, or Rider)

Installing Required Packages

Create a new ASP.NET Core Web API project or console application:

dotnet new webapi -n EFCorePostgresDemo
cd EFCorePostgresDemo

Install the necessary NuGet packages:

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design

Project Structure Overview

Your project should have the following structure:

EFCorePostgresDemo/
├── Data/
│   └── ApplicationDbContext.cs
├── Models/
│   ├── Product.cs
│   └── ProductSummary.cs
├── Controllers/
│   └── ProductsController.cs
├── Program.cs
└── appsettings.json

Configuring the Database Context

First, let’s set up our DbContext and configure PostgreSQL:

using Microsoft.EntityFrameworkCore;

namespace EFCorePostgresDemo.Data
{
    public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }

        public DbSet<Product> Products { get; set; }
        public DbSet<ProductSummary> ProductSummaries { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            
            // Configure ProductSummary as keyless for function results
            modelBuilder.Entity<ProductSummary>().HasNoKey();
        }
    }
}

Configure the connection string in appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Host=localhost;Database=productdb;Username=postgres;Password=yourpassword"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.EntityFrameworkCore": "Information"
    }
  }
}

Register the DbContext in Program.cs:

using EFCorePostgresDemo.Data;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

// Add DbContext
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")));

var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();

Creating Entity Models

Define your entity models in the Models folder:

namespace EFCorePostgresDemo.Models
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; } = string.Empty;
        public string Category { get; set; } = string.Empty;
        public decimal Price { get; set; }
        public int Stock { get; set; }
        public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    }
}
namespace EFCorePostgresDemo.Models
{
    public class ProductSummary
    {
        public string Category { get; set; } = string.Empty;
        public int TotalProducts { get; set; }
        public decimal AveragePrice { get; set; }
        public decimal TotalValue { get; set; }
    }
}

Creating Stored Procedures and Functions in PostgreSQL

Now let’s create some useful stored procedures and functions. Connect to your PostgreSQL database using pgAdmin, psql, or any database client.

Example 1: Stored Procedure for Inserting Products

This procedure inserts a new product and returns the generated ID:

CREATE OR REPLACE PROCEDURE insert_product(
    p_name VARCHAR(200),
    p_category VARCHAR(100),
    p_price DECIMAL(10, 2),
    p_stock INT,
    OUT p_id INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO products (name, category, price, stock, created_at)
    VALUES (p_name, p_category, p_price, p_stock, NOW())
    RETURNING id INTO p_id;
END;
$$;

Example 2: Stored Procedure for Updating Stock

This procedure updates product stock with validation:

CREATE OR REPLACE PROCEDURE update_product_stock(
    p_product_id INT,
    p_quantity INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Check if product exists
    IF NOT EXISTS (SELECT 1 FROM products WHERE id = p_product_id) THEN
        RAISE EXCEPTION 'Product with ID % does not exist', p_product_id;
    END IF;
    
    -- Update stock
    UPDATE products
    SET stock = stock + p_quantity
    WHERE id = p_product_id;
    
    -- Prevent negative stock
    IF (SELECT stock FROM products WHERE id = p_product_id) < 0 THEN
        RAISE EXCEPTION 'Insufficient stock for product ID %', p_product_id;
    END IF;
END;
$$;

Example 3: Function Returning Table Results

This function returns products by category with filtering:

CREATE OR REPLACE FUNCTION get_products_by_category(
    p_category VARCHAR(100),
    p_min_price DECIMAL(10, 2) DEFAULT 0
)
RETURNS TABLE (
    id INT,
    name VARCHAR(200),
    category VARCHAR(100),
    price DECIMAL(10, 2),
    stock INT,
    created_at TIMESTAMP
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT p.id, p.name, p.category, p.price, p.stock, p.created_at
    FROM products p
    WHERE p.category = p_category
      AND p.price >= p_min_price
    ORDER BY p.price DESC;
END;
$$;

Example 4: Function Returning Product Summary

This function calculates aggregate statistics by category:

CREATE OR REPLACE FUNCTION get_product_summary()
RETURNS TABLE (
    category VARCHAR(100),
    total_products BIGINT,
    average_price DECIMAL(10, 2),
    total_value DECIMAL(12, 2)
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        p.category,
        COUNT(*)::BIGINT as total_products,
        ROUND(AVG(p.price), 2) as average_price,
        ROUND(SUM(p.price * p.stock), 2) as total_value
    FROM products p
    GROUP BY p.category
    ORDER BY total_value DESC;
END;
$$;

Example 5: Scalar Function

This function calculates total inventory value for a specific product:

CREATE OR REPLACE FUNCTION calculate_product_value(p_product_id INT)
RETURNS DECIMAL(12, 2)
LANGUAGE plpgsql
AS $$
DECLARE
    product_value DECIMAL(12, 2);
BEGIN
    SELECT price * stock INTO product_value
    FROM products
    WHERE id = p_product_id;
    
    RETURN COALESCE(product_value, 0);
END;
$$;

Calling Stored Procedures and Functions from EF Core

Now comes the exciting part — invoking these database objects from C# code using EF Core.

Calling Stored Procedures with ExecuteSqlRaw

Create a service or controller method to call the stock update procedure:

using EFCorePostgresDemo.Data;
using EFCorePostgresDemo.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Npgsql;

namespace EFCorePostgresDemo.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class ProductsController : ControllerBase
    {
        private readonly ApplicationDbContext _context;

        public ProductsController(ApplicationDbContext context)
        {
            _context = context;
        }

        [HttpPost("update-stock")]
        public async Task<IActionResult> UpdateStock(int productId, int quantity)
        {
            try
            {
                var productIdParam = new NpgsqlParameter("p_product_id", productId);
                var quantityParam = new NpgsqlParameter("p_quantity", quantity);

                await _context.Database.ExecuteSqlRawAsync(
                    "CALL update_product_stock(@p_product_id, @p_quantity)",
                    productIdParam,
                    quantityParam
                );

                return Ok(new { message = "Stock updated successfully" });
            }
            catch (PostgresException ex)
            {
                return BadRequest(new { error = ex.MessageText });
            }
        }
    }
}

Calling Procedures with Output Parameters

For procedures that return values via OUT parameters, you need to handle them differently:

[HttpPost("create")]
public async Task<IActionResult> CreateProduct([FromBody] Product product)
{
    var nameParam = new NpgsqlParameter("p_name", product.Name);
    var categoryParam = new NpgsqlParameter("p_category", product.Category);
    var priceParam = new NpgsqlParameter("p_price", product.Price);
    var stockParam = new NpgsqlParameter("p_stock", product.Stock);
    
    var idParam = new NpgsqlParameter("p_id", NpgsqlTypes.NpgsqlDbType.Integer)
    {
        Direction = System.Data.ParameterDirection.Output
    };

    await _context.Database.ExecuteSqlRawAsync(
        "CALL insert_product(@p_name, @p_category, @p_price, @p_stock, @p_id)",
        nameParam, categoryParam, priceParam, stockParam, idParam
    );

    var generatedId = (int)idParam.Value!;
    
    return CreatedAtAction(nameof(GetProduct), new { id = generatedId }, 
        new { id = generatedId, message = "Product created successfully" });
}

Calling Table-Valued Functions with FromSqlRaw

To retrieve data from functions that return tables, use FromSqlRaw:

[HttpGet("by-category/{category}")]
public async Task<ActionResult<List<Product>>> GetProductsByCategory(
    string category, 
    decimal minPrice = 0)
{
    var products = await _context.Products
        .FromSqlRaw(
            "SELECT * FROM get_products_by_category(@p0, @p1)",
            category,
            minPrice
        )
        .ToListAsync();

    return Ok(products);
}

Calling Functions with Custom DTOs

When the function returns a structure that doesn’t match your entity, use a DTO:

[HttpGet("summary")]
public async Task<ActionResult<List<ProductSummary>>> GetProductSummary()
{
    var summary = await _context.ProductSummaries
        .FromSqlRaw("SELECT * FROM get_product_summary()")
        .ToListAsync();

    return Ok(summary);
}

Calling Scalar Functions

For functions that return single values, use ExecuteSqlRaw with a query:

[HttpGet("{id}/value")]
public async Task<ActionResult<decimal>> GetProductValue(int id)
{
    var valueParam = new NpgsqlParameter("p_product_id", id);
    
    var result = await _context.Database
        .SqlQueryRaw<decimal>(
            "SELECT calculate_product_value(@p_product_id)",
            valueParam
        )
        .FirstOrDefaultAsync();

    return Ok(new { productId = id, totalValue = result });
}

Working with Parameters and Return Values

Parameter Binding Best Practices

Always use parameterized queries to prevent SQL injection:

// ✅ GOOD - Parameterized
await _context.Database.ExecuteSqlRawAsync(
    "CALL my_procedure(@p0, @p1)", 
    param1, 
    param2
);

// ❌ BAD - String interpolation (vulnerable to SQL injection)
await _context.Database.ExecuteSqlRawAsync(
    $"CALL my_procedure({userInput}, {otherInput})"
);

Handling NULL Values

When working with nullable parameters:

var categoryParam = new NpgsqlParameter("p_category", 
    (object?)product.Category ?? DBNull.Value);

Using FormattableString with FromSql

EF Core 7+ supports interpolated strings safely:

string category = "Electronics";
decimal minPrice = 100m;

var products = await _context.Products
    .FromSql($"SELECT * FROM get_products_by_category({category}, {minPrice})")
    .ToListAsync();

This is automatically parameterized by EF Core, making it both safe and readable.

Working with Complex Return Types

For functions returning JSON or complex types:

public class ComplexResult
{
    public string Data { get; set; } = string.Empty;
}

var results = await _context.Database
    .SqlQueryRaw<string>("SELECT my_json_function()::text")
    .ToListAsync();

// Deserialize if needed
var complexResults = results
    .Select(json => JsonSerializer.Deserialize<ComplexResult>(json))
    .ToList();

Best Practices and Tips

When to Use Stored Procedures vs LINQ

Use Stored Procedures when:

  • You need to execute complex multi-step transactions
  • Performance is critical and you need fine-tuned query optimization
  • Working with legacy databases that already use procedures extensively
  • Implementing database-level security and access control
  • Performing bulk operations that would generate too many round trips

Use LINQ when:

  • Queries are simple to moderately complex
  • You want type safety and compile-time checking
  • The query logic changes frequently
  • You prefer keeping business logic in your application layer
  • You need better testability and maintainability

Handling Migrations with Database Objects

EF Core migrations don’t automatically track stored procedures and functions. You’ll need to manage them manually:

public partial class AddProductProcedures : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"
            CREATE OR REPLACE PROCEDURE update_product_stock(
                p_product_id INT,
                p_quantity INT
            )
            LANGUAGE plpgsql
            AS $$
            BEGIN
                UPDATE products
                SET stock = stock + p_quantity
                WHERE id = p_product_id;
            END;
            $$;
        ");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("DROP PROCEDURE IF EXISTS update_product_stock;");
    }
}

Performance Optimization Tips

  1. Use Async Methods: Always prefer ExecuteSqlRawAsync and FromSqlRaw(...).ToListAsync() for better scalability
  2. Connection Pooling: Npgsql handles this automatically, but ensure your connection string is consistent
  3. Avoid N+1 Queries: When calling functions in loops, consider batch operations
  4. Index Your Tables: Ensure proper indexing on columns used in WHERE clauses within your functions
  5. Monitor Query Performance: Use PostgreSQL’s EXPLAIN ANALYZE to profile your procedures

Error Handling

Implement robust error handling for database exceptions:

try
{
    await _context.Database.ExecuteSqlRawAsync(
        "CALL risky_procedure(@p0)", 
        parameter
    );
}
catch (PostgresException ex) when (ex.SqlState == "23505")
{
    // Handle unique constraint violation
    return Conflict(new { error = "Duplicate entry" });
}
catch (PostgresException ex) when (ex.SqlState == "P0001")
{
    // Handle RAISE EXCEPTION from procedure
    return BadRequest(new { error = ex.MessageText });
}
catch (DbUpdateException ex)
{
    // Handle other database update errors
    return StatusCode(500, new { error = "Database error occurred" });
}

Testing Strategies

Create integration tests for your stored procedures:

[Fact]
public async Task UpdateStock_ShouldIncreaseProductQuantity()
{
    // Arrange
    var options = new DbContextOptionsBuilder<ApplicationDbContext>()
        .UseNpgsql("your-test-connection-string")
        .Options;
    
    using var context = new ApplicationDbContext(options);
    
    // Create test product
    var product = new Product 
    { 
        Name = "Test", 
        Category = "Test", 
        Price = 10, 
        Stock = 5 
    };
    context.Products.Add(product);
    await context.SaveChangesAsync();
    
    // Act
    await context.Database.ExecuteSqlRawAsync(
        "CALL update_product_stock(@p0, @p1)", 
        product.Id, 
        10
    );
    
    // Assert
    var updated = await context.Products.FindAsync(product.Id);
    Assert.Equal(15, updated!.Stock);
}

Maintainability Recommendations

  1. Version Control Database Scripts: Keep your SQL files in source control alongside your C# code
  2. Use Meaningful Names: Follow PostgreSQL naming conventions (snake_case for database objects)
  3. Document Your Procedures: Add comments explaining parameters and business logic
  4. Centralize Database Logic: Consider creating a repository pattern to encapsulate all database calls
  5. Keep Procedures Focused: Each procedure should have a single, well-defined responsibility

✍️Wrapping Up

Combining EF Core with PostgreSQL’s stored procedures and functions gives you the best of both worlds: the elegance and productivity of an ORM with the power and performance of database-level programming.

Key Takeaways:

  • Use ExecuteSqlRaw and ExecuteSqlRawAsync for procedures that don’t return data
  • Use FromSqlRaw and FromSql for functions that return queryable results
  • Always parameterize your queries to prevent SQL injection
  • Handle database exceptions appropriately with try-catch blocks
  • Manage stored procedures in migrations using migrationBuilder.Sql()
  • Choose between LINQ and stored procedures based on your specific use case

Stored procedures and functions are powerful tools in your ASP.NET development arsenal. They’re particularly valuable for complex business logic, performance-critical operations, and scenarios where you need to leverage PostgreSQL’s advanced features. By following the patterns and best practices outlined in this guide, you’ll be able to seamlessly integrate database-level programming into your EF Core applications.

Now it’s your turn — experiment with these techniques in your own projects and discover how stored procedures can enhance your PostgreSQL and EF Core workflow!

👋Ultimate Collection of .NET Web Apps for Developers and Businesses
🚀 My YouTube Channel
💻 Github

Here are three ways you can help me out:
Please drop me a follow →👍 R M Shahidul Islam Shahed
Receive an e-mail every time I post on Medium → 💌 Click Here
Grab a copy of my E-Book on OOP with C# → 📚 Click Here

Have questions or suggestions? Share your experiences with EF Core and PostgreSQL in the comments below!

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top