# Using Stored Procedures and Functions With EF Core and PostgreSQL

> Learn how to use PostgreSQL stored procedures and functions with EF Core to handle complex queries, atomic operations with locking, and database-specific features while keeping the type safety and convenience of EF Core.

Published: 2025-10-04. Author: Milan Jovanović.

Canonical: https://milanjovanovic.tech/blog/using-stored-procedures-and-functions-with-ef-core-and-postgresql

EF Core calls PostgreSQL functions and stored procedures without dropping down to ADO.NET.
Use `dbContext.Database.SqlQuery<T>` with an interpolated string for functions, aliasing columns to match your DTO, and `ExecuteSqlAsync` with `CALL` for procedures.
The interpolation is parameterized, so it is not a SQL injection risk.

You're building a .NET application with [EF Core](https://learn.microsoft.com/en-us/ef/core/).
Most of your queries work fine with [**LINQ**](https://milanjovanovic.tech/blog/why-i-write-tall-linq-queries), but now you're hitting scenarios where you need something more.

Maybe you have a complex report that joins five tables with aggregations and window functions.
Your LINQ query generates SQL that's slower than you'd like, and you know you could write better SQL by hand.

Or maybe you need to update inventory with proper locking to prevent race conditions.
You could manage [**transactions**](https://milanjovanovic.tech/blog/working-with-transactions-in-ef-core) and explicit locks in C#, but it feels like you're fighting the framework.

Here's what usually happens: you search for "EF Core stored procedures" and find conflicting advice.
Some articles say avoid raw SQL at all costs.
Others suggest abandoning EF entirely and writing ADO.NET.
Neither feels right.

Actually, **EF Core works great with database functions and procedures**.
You get the database's power for what it does best, and EF's convenience for everything else.
Let me show you how this actually works.

I'll use [PostgreSQL](https://www.postgresql.org/) for examples, but the same principles apply to SQL Server and other relational databases.

## When Should You Even Use Raw SQL?

Let's be honest: most of the time, **LINQ is fine**.
EF Core translates your C# into decent SQL, and you get type safety and refactoring support.

But there are times when raw SQL makes more sense:

**You need performance you can't get from LINQ**.
Complex aggregations with multiple joins, [window functions](https://www.postgresql.org/docs/current/tutorial-window.html), or reporting queries often run faster when written directly in SQL.
You can test and tune the query in your database tool before bringing it into your code.

**You're using database-specific features**.
PostgreSQL has powerful capabilities like [full-text search](https://www.postgresql.org/docs/current/textsearch.html), JSON operators,
and [common table expressions (CTEs)](https://www.postgresql.org/docs/current/queries-with.html) that don't always have clean LINQ equivalents.
Sometimes the straightest path is just writing the SQL.

**You have existing database logic**.
If your database already has stored procedures and functions (maybe from a [**legacy system**](https://milanjovanovic.tech/blog/what-rewriting-a-40-year-old-project-taught-me-about-software-development)),
calling them directly beats rewriting everything in C#.

**You need atomic operations with proper locking**.
A stored procedure that coordinates multiple updates with `FOR UPDATE` locks ([**here's a good use case**](https://milanjovanovic.tech/blog/scaling-the-outbox-pattern))
is simpler and safer than trying to manage that from application code.

**You want to reduce round trips**.
One function call that aggregates data from five tables is more efficient than five separate LINQ queries.

Now let's see how to actually do this.

## Example 1: Simple Scalar Function

Here's a straightforward function that tells you how many tickets are left:

```sql
CREATE OR REPLACE FUNCTION ticketing.tickets_left(p_ticket_type_id uuid)
RETURNS numeric
LANGUAGE sql
AS $$
  SELECT tt.available_quantity
  FROM ticketing.ticket_types tt
  WHERE tt.id = p_ticket_type_id
$$;
```

Nothing fancy, just a query wrapped in a function.

Calling it from EF Core is straightforward:

```csharp {6}
app.MapGet("ticket-types/{ticketTypeId}/available-quantity",
async (Guid ticketTypeId, EventManagementContext dbContext) =>
{
    var result = await dbContext.Database.SqlQuery<int>(
            $"""
             SELECT ticketing.tickets_left({ticketTypeId}) AS "Value"
             """)
        .FirstAsync();

    return Results.Ok(result);
});
```

Notice the `AS "Value"` alias.
When EF Core maps to a primitive type, it expects a property named `Value`.
The quotes preserve the exact casing (PostgreSQL lowercases unquoted identifiers by default).

The interpolated string syntax (`$"{ticketTypeId}"`) might look dangerous, but EF Core converts this into a parameterized query automatically.
You're not building SQL strings, you're using C# interpolation as a convenient syntax for parameters.

## Example 2: Table-Valued Function

Functions can return entire result sets, which is where they really shine:

```sql
CREATE OR REPLACE FUNCTION ticketing.customer_order_summary(p_customer_id uuid)
RETURNS TABLE (
    order_id uuid,
    created_at_utc timestamptz,
    total_price numeric,
    currency text,
    item_count numeric
)
LANGUAGE sql
AS $$
SELECT
    o.id,
    o.created_at_utc,
    o.total_price,
    o.currency,
    COALESCE(SUM(oi.quantity), 0) AS item_count
FROM ticketing.orders o
LEFT JOIN ticketing.order_items oi ON oi.order_id = o.id
WHERE o.customer_id = p_customer_id
GROUP BY o.id, o.created_at_utc, o.total_price, o.currency
ORDER BY o.created_at_utc DESC
$$;
```

This function joins orders with their items, aggregates quantities, and returns multiple rows.
You could write this in LINQ, but the SQL is clearer and you can test it directly in your database tool.

To use it from C#, create a DTO that matches the function's output:

```csharp
public class OrderSummaryDto
{
    public Guid OrderId { get; set; }
    public DateTime CreatedAtUtc { get; set; }
    public decimal TotalPrice { get; set; }
    public string Currency { get; set; }
    public int ItemCount { get; set; }
}
```

Then query the function like any other table:

```csharp {13}
app.MapGet("customers/{customerId}/order-summary",
async (Guid customerId, EventManagementContext dbContext) =>
{
    var orders = await dbContext.Database
        .SqlQuery<OrderSummaryDto>(
            $"""
             SELECT
                order_id AS OrderId,
                created_at_utc AS CreatedAtUtc,
                total_price AS TotalPrice,
                currency AS Currency,
                item_count AS ItemCount
             FROM ticketing.customer_order_summary({customerId})
             """)
        .ToListAsync();

    return Results.Ok(orders);
});
```

The key is mapping column names to your DTO properties using aliases.
EF Core handles the rest automatically.

This is a simple case without joins, but you can use this pattern in more complex queries too.
However, you will have to project into DTOs manually since EF Core can't translate joins in raw SQL into entity graphs.
Usually, you'll return a flat structure from functions anyway, and then map to richer models in C# if needed.

## Understanding PostgreSQL Functions vs Procedures

PostgreSQL distinguishes between functions and procedures in important ways:

**Functions** are designed to **return values**.
They can return scalar values, tables, or even complex JSON objects.
You call them with `SELECT` and can use them in queries like any other expression.
Functions run within a transaction and can be used in `WHERE` clauses, joins, and other query contexts.

**Procedures** are designed for **side effects**.
They don't return values directly but can modify data and have `OUT` parameters.
You call them with `CALL` and they're ideal for complex operations that need to manage transactions explicitly or perform multiple related updates.

Think of it this way: use functions when you need data, use procedures when you need to change something.

This distinction matters because it affects how you design your database logic and how you call these routines from C#.

Let's see an example of a procedure.

## Example 3: Stored Procedure with Validation

Here's where procedures really prove their worth.
Let's say you need to adjust ticket inventory, but you want to prevent [**race conditions**](https://milanjovanovic.tech/blog/solving-race-conditions-with-ef-core-optimistic-locking) and validate the operation:

```sql
CREATE OR REPLACE PROCEDURE ticketing.adjust_available_quantity(
    p_ticket_type_id uuid,
    p_delta numeric,
    p_reason text DEFAULT 'manual-adjust'
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_qty numeric;
    v_avail numeric;
    v_new_avail numeric;
BEGIN
    SELECT quantity, available_quantity
    INTO v_qty, v_avail
    FROM ticketing.ticket_types
    WHERE id = p_ticket_type_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'ticket_type % not found', p_ticket_type_id;
    END IF;

    v_new_avail := v_avail + p_delta;

    IF v_new_avail < 0 THEN
        RAISE EXCEPTION 'Cannot reduce below zero';
    END IF;

    IF v_new_avail > v_qty THEN
        RAISE EXCEPTION 'Cannot exceed quantity';
    END IF;

    UPDATE ticketing.ticket_types
    SET available_quantity = v_new_avail
    WHERE id = p_ticket_type_id;
END;
$$;
```

This procedure does several important things:

- **Locks the row** with `FOR UPDATE` so no other transaction can modify it until we're done
- **Validates business rules** before making changes
- **Provides clear error messages** when something goes wrong
- **Keeps everything atomic** in a single database round trip

You could do all this in C# with manual transaction management and explicit locking, but it's more complex and error-prone.
Let the database handle what it's good at.

Here's how you call it from EF Core:

```csharp {10}
app.MapPut("ticket-types/{ticketTypeId}/available-quantity", async (
    Guid ticketTypeId,
    int quantity,
    EventManagementContext dbContext) =>
{
    try
    {
        await dbContext.Database.ExecuteSqlAsync(
            $"""
             CALL ticketing.adjust_available_quantity({ticketTypeId},{quantity})
             """);

        return Results.Ok(result);
    }
    catch (Exception e)
    {
        return Results.BadRequest(e.Message);
    }
});
```

The procedure doesn't return a value, but if it raises an exception (with `RAISE EXCEPTION`), PostgreSQL will propagate that to your C# code.
You can catch it and return a proper error response.

## About SQL Injection (Don't Panic)

You might be looking at those interpolated strings and thinking "wait, isn't this [**SQL injection waiting to happen**](https://milanjovanovic.tech/blog/ef-core-raw-sql-queries)?"

**It's not**.

When you write:

```csharp
$"SELECT * FROM users WHERE id = {userId}"
```

EF Core doesn't concatenate strings.
It converts this into:

```sql
SELECT * FROM users WHERE id = @p0
```

**The actual value is sent as a parameter**, completely separate from the SQL text.
This works for all the examples in this article.

The interpolation syntax is just a convenient way to write parameterized queries.

The reason why is we're not actually passing in a `string` to the `SqlQuery` method,
but a [`FormattableString`](https://learn.microsoft.com/en-us/dotnet/api/system.formattablestring).
This is a special type that captures the format and arguments separately, allowing EF Core to handle parameters.

Everything in this article works with [SQL Server](https://learn.microsoft.com/en-us/sql/),
[MySQL](https://dev.mysql.com/),
[SQLite](https://www.sqlite.org/index.html), and other databases EF Core supports.
The differences are mostly syntax.

## A Quick Word on Views

Database views are like functions without parameters.
They're saved queries you can reference by name.

You can query them using `SqlQuery<T>` just like functions:

```csharp
var results = await dbContext.Database
    .SqlQuery<ActiveCustomerDto>(
        $"SELECT * FROM ticketing.active_customers")
    .ToListAsync();
```

Or you can map them to entity types in your `DbContext` for full LINQ support.

Views are great for frequently-used queries that don't need parameters.
Functions give you the flexibility of parameterization.

## Wrapping Up

We've covered how to use PostgreSQL functions and procedures with EF Core, from simple scalar functions to complex procedures with validation and locking.

You learned when to use functions (when you need data back) versus procedures (when you need to modify data).
You saw how EF Core's `SqlQuery<T>` and `ExecuteSqlAsync` give you type safety while letting you write the SQL you need.
And you learned when raw SQL makes sense: complex aggregations, database-specific features, atomic operations with locking, and reducing round trips.

**EF Core** doesn't force you to choose between LINQ and raw SQL.
You can use both.

Use functions when you need to return data, procedures when you need to modify data with complex logic,
and raw SQL queries when LINQ doesn't capture your requirements efficiently.
The combination of EF Core's convenience and the database's power gives you the flexibility to choose the right tool for each scenario.

That's all for today. Hope this was helpful.

---

## Frequently asked questions

### When should you use raw SQL instead of LINQ in EF Core?

Reach for raw SQL when you need performance LINQ cannot deliver (complex aggregations, window functions, reporting queries), database-specific features like full-text search or CTEs, existing stored procedures from a legacy system, atomic operations with proper locking, or fewer database round trips.

### What is the difference between a PostgreSQL function and a stored procedure?

Functions return values (scalars, tables, or JSON), are called with SELECT, and can be used inside queries. Procedures exist for side effects: they modify data, can have OUT parameters, and are called with CALL. Use functions when you need data and procedures when you need to change something.

### How do you call a PostgreSQL function from EF Core?

Use dbContext.Database.SqlQuery with an interpolated SQL string that selects from the function. For a scalar result, alias the column as "Value" because EF Core expects a property with that name. For table-valued functions, alias each column to match your DTO properties.

### Is string interpolation in EF Core SQL queries vulnerable to SQL injection?

No. SqlQuery and ExecuteSqlAsync accept a FormattableString, not a plain string, so EF Core captures the arguments separately and sends them as parameters (WHERE id = @p0). The values never get concatenated into the SQL text.

### How do you call a stored procedure from EF Core?

Use dbContext.Database.ExecuteSqlAsync with a CALL statement, for example CALL ticketing.adjust_available_quantity(id, delta). Procedures do not return values, but a RAISE EXCEPTION inside the procedure propagates to your C# code as an exception you can catch and turn into an error response.

### Why put locking logic in a stored procedure instead of C#?

A procedure can lock the row with FOR UPDATE, validate business rules, and apply the update atomically in a single database round trip. Managing the same transaction and explicit locks from application code is more complex and error-prone.