Using Stored Procedures and Functions With EF Core and PostgreSQL

Imagine you order a thali at your favourite restaurant. You do not walk into the kitchen, light the stove, and cook each dish yourself. You simply say "one thali, please." The kitchen already knows the recipe. It cooks everything in the right order and brings you a full plate.

A stored procedure or a database function works the same way. The recipe lives inside the database. Your C# code just says "run this recipe" and gets back a neat plate of results. You do not send a long list of instructions every time. You send one short order.

In this guide we will learn how to call PostgreSQL functions and stored procedures from EF Core. We will keep things simple and warm. By the end you will know when to use a function, when to use a procedure, and how to wire both into your .NET app.

Why put logic in the database at all?

Most of the time EF Core writes SQL for you. You write LINQ, and EF Core turns it into SQL. That is wonderful. But sometimes you want the database itself to hold a piece of logic. Here is why.

• The logic is heavy and touches many rows. Doing it inside the database is faster because the data does not travel back and forth.
• Many apps share the same database. A function keeps one single copy of the rule, so every app behaves the same.
• You need a tricky database feature, like row locking or a special PostgreSQL extension, that is hard to express in LINQ.

Think of it as moving the recipe closer to the ingredients. The kitchen is where the food is, so cooking there is quick.

Functions vs procedures: the key difference

People mix these two words a lot. In PostgreSQL they are different tools.

A function returns something. You usually call it inside a SELECT. It can return a single number, a single row, or a whole table of rows. Functions are great when you want to read data.

A procedure is called with the CALL keyword. It is built for doing things, like updating many rows or running a multi-step job. A procedure can even manage its own transaction.

Here is a quick table to keep the two clear in your head.

A simple way to remember: a function gives you a plate of food (it returns something). A procedure is a worker who does a job in the kitchen (it performs an action).

Setting up the PostgreSQL provider

Before EF Core can talk to PostgreSQL, it needs a provider. The provider is like a translator. It knows how to turn EF Core commands into the exact words PostgreSQL understands.

For PostgreSQL the provider is the open-source Npgsql package. Add it to your project.

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

Then register it in your DbContext. This tells EF Core "use PostgreSQL, and here is the connection string."

public class AppDbContext : DbContext
{
    public DbSet<Product> Products => Set<Product>();
 
    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseNpgsql("Host=localhost;Database=shop;Username=postgres;Password=secret");
    }
}

In a real app you would read the connection string from configuration, not hard-code it. But this keeps the example short and clear.

Our example database

Let us build a tiny shop. We have a products table. Each product has an id, a name, a price, and a category. We will write functions and procedures around this table.

Here is the function that returns all products in one category. Notice it returns a table of rows.

CREATE OR REPLACE FUNCTION get_products_by_category(category_name TEXT)
RETURNS TABLE (id INT, name TEXT, price NUMERIC)
AS $$
BEGIN
    RETURN QUERY
    SELECT p.id, p.name, p.price
    FROM products p
    WHERE p.category = category_name;
END;
$$ LANGUAGE plpgsql;

This is the recipe. It lives in the database. Now we want our C# app to order from it.

Calling a function that returns rows

To read the rows from get_products_by_category, we use EF Core's FromSql method. First we need a C# type to hold each row. We use a keyless entity because the result is just a read-only shape, not a table we update.

public class ProductResult
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
}

We map it as keyless in the model. Keyless means "this has no primary key, just treat it as a result."

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<ProductResult>().HasNoKey().ToView(null);
}
 
public DbSet<ProductResult> ProductResults => Set<ProductResult>();

Now the fun part. We call the function with FromSql. The $"..." is an interpolated string. EF Core is smart here: it does not paste your value straight into the SQL. It turns it into a safe parameter. That protects you from SQL injection.

var category = "Books";
 
var products = await context.ProductResults
    .FromSql($"SELECT * FROM get_products_by_category({category})")
    .ToListAsync();
 
foreach (var p in products)
{
    Console.WriteLine($"{p.Name} costs {p.Price}");
}

That is it. One short call. PostgreSQL runs the recipe and hands back the plate. EF Core turns each row into a ProductResult object.

A note on safe parameters

Why do we keep talking about safety? Because pasting raw text into SQL is dangerous. If a user typed a sneaky value, they could change your query. The interpolated FromSql blocks this by sending the value separately, as a parameter.

The table below shows the EF Core methods and when each one keeps you safe.

The rule of thumb: prefer the interpolated versions (FromSql and ExecuteSql). Use the Raw versions only when you must build the SQL text dynamically, and even then pass values as parameters.

Calling a scalar function

Some functions return a single value, not a table. For example, a function that gives the average price in a category.

CREATE OR REPLACE FUNCTION avg_price(category_name TEXT)
RETURNS NUMERIC
AS $$
    SELECT AVG(price) FROM products WHERE category = category_name;
$$ LANGUAGE sql;

For a single scalar value, you can run a small raw command and read the result. One clean way is to use the Npgsql connection directly through the context. But many teams simply wrap the scalar in a one-column, one-row query and read it with FromSql into a tiny keyless type. Both work. Pick the one that reads clearly to your team.

Running a stored procedure

Now let us write a procedure. Procedures are for doing, not reading. Here is one that raises the price of every product in a category by a percentage.

CREATE OR REPLACE PROCEDURE raise_prices(category_name TEXT, percent NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE products
    SET price = price * (1 + percent / 100)
    WHERE category = category_name;
END;
$$;

To run a procedure from EF Core, we use ExecuteSql. We call it with the PostgreSQL CALL keyword. ExecuteSql does not return rows. It returns the number of rows affected, which is handy for logging.

var category = "Books";
decimal percent = 10m;
 
await context.Database
    .ExecuteSqlAsync($"CALL raise_prices({category}, {percent})");

Notice we use context.Database.ExecuteSqlAsync here, not a DbSet. That is the EF Core door for commands that perform an action rather than return a result set.

Why procedures are great for locking and atomic jobs

Sometimes you need a job to be all or nothing. Either every step happens, or none does. This is called an atomic operation. Procedures shine here because they can run several SQL statements as one unit, and PostgreSQL can lock the rows so two users do not clash.

Picture two people buying the last copy of a book at the same time. Without locking, both might think it is available. A procedure can lock the row, check the stock, and update it safely. The second buyer waits a moment and then sees the truth.

CREATE OR REPLACE PROCEDURE buy_product(product_id INT)
LANGUAGE plpgsql
AS $$
DECLARE
    available INT;
BEGIN
    SELECT stock INTO available
    FROM products
    WHERE id = product_id
    FOR UPDATE;
 
    IF available > 0 THEN
        UPDATE products SET stock = stock - 1 WHERE id = product_id;
    ELSE
        RAISE EXCEPTION 'Out of stock';
    END IF;
END;
$$;

The FOR UPDATE part is the lock. It says "I am working on this row, please make others wait." This keeps your data correct under pressure.

Mapping a function to LINQ with HasDbFunction

There is a neat trick for scalar functions. You can teach EF Core to recognise your function inside normal LINQ queries. This is called function mapping. You declare a C# method and tell EF Core which database function it points to.

public static class ShopFunctions
{
    public static decimal AvgPrice(string category)
        => throw new NotSupportedException();
}
 
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .HasDbFunction(typeof(ShopFunctions).GetMethod(nameof(ShopFunctions.AvgPrice))!)
        .HasName("avg_price");
}

Now you can write LINQ that calls your function, and EF Core puts the function call right into the SQL.

var cheapBooks = await context.Products
    .Where(p => p.Price < ShopFunctions.AvgPrice("Books"))
    .ToListAsync();

This is the cleanest of all. Your C# stays readable, and the database does the heavy math. The body of AvgPrice throws on purpose because it is never run in C#. It only acts as a signpost to the real database function.

A common pitfall: composing over a function

EF Core can sometimes add extra SQL around your FromSql call, like ordering or paging. PostgreSQL allows this for functions that return a table. But it does not always work for every function shape. If you hit an error, the safest move is to call FromSql and then bring the rows into memory with ToList, then sort or page in C#. For large result sets, write the sorting inside the function itself so the database does the work.

Here is a small table of what tends to work.

Quick recap

• A function returns data and is called inside a SELECT. Use FromSql to read its rows.
• A procedure performs an action and is called with CALL. Use ExecuteSql to run it.
• Install Npgsql.EntityFrameworkCore.PostgreSQL so EF Core can talk to PostgreSQL.
• Map function results to a keyless entity with HasNoKey.
• Prefer the interpolated FromSql and ExecuteSql so values become safe parameters, not pasted text.
• Procedures are great for atomic jobs and row locking with FOR UPDATE.
• Use HasDbFunction to call a scalar function straight from LINQ.
• If EF Core struggles to compose extra SQL over a function, do the sorting and filtering inside the function.

Putting logic in the database is like trusting the kitchen with the recipe. You send a short order, and a clean plate comes back. EF Core gives you a comfortable way to place that order from C#, while keeping your code safe and easy to read.

References and further reading

• EF Core SQL Queries (Microsoft Learn) - the official guide to FromSql, FromSqlRaw, and ExecuteSql.
• Npgsql Entity Framework Core Provider - the PostgreSQL provider docs, including setup and supported features.
• Using Stored Procedures and Functions With EF Core and PostgreSQL (Milan Jovanovic) - a practical community walkthrough.
• Leveraging Custom PostgreSQL Functions in EF Core (Gor Grigoryan) - more on mapping functions into LINQ.