EF Core developers usually reach for LINQ first, and that is the right instinct for most day to day queries. But there are situations where LINQ starts fighting you: complex reports with several joins and window functions, or operations that need proper row locking to avoid race conditions. This is where PostgreSQL stored procedures and functions come in, and EF Core actually cooperates with them quite well once you know the pattern.
In this article I will walk through calling PostgreSQL functions and procedures directly from EF Core, using SqlQuery and ExecuteSqlAsync. The examples use PostgreSQL, but the same approach works with SQL Server, MySQL and SQLite too, since the underlying EF Core APIs do not care which provider you are on. Only the SQL syntax inside the function bodies changes.
When does raw SQL actually make sense
Most of the time LINQ is good enough. EF Core translates your C# expressions into reasonably efficient SQL, and you keep type safety and refactoring support along the way. I would not reach for raw SQL as a default choice, only when a specific need comes up.
A few situations where I have genuinely needed to drop to raw SQL on real projects: complex aggregations with multiple joins or window functions that LINQ translates poorly, database specific features like PostgreSQL full text search, JSON operators or CTEs that do not map cleanly to LINQ, existing stored procedures inherited from a legacy system, atomic operations that need row level locking such as FOR UPDATE, and cases where one function call replacing five round trips genuinely matters for latency.
None of these are exotic scenarios. They show up regularly in reporting modules, inventory systems and anything with concurrent writes. The rest of this article works through each pattern with actual code.
A simple scalar function
Start with the simplest case: a function that returns a single value. Here is a PostgreSQL function that tells you how many tickets remain for a given ticket type.
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
$$;It is a query wrapped inside a function, nothing more. You call it from PostgreSQL with a plain SELECT, and EF Core lets you do the same from C# using Database.SqlQuery.
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);
});Two things matter in that call. First, the AS “Value” alias is required. When EF Core maps a query result to a primitive type like int, it looks for a column literally named Value, and the quotes keep PostgreSQL from lowercasing it into something else. Second, the interpolated string looks like string concatenation but is not one. EF Core receives it as a FormattableString, extracts the ticketTypeId as a separate parameter, and sends a parameterized query to PostgreSQL. The expected output here is a single integer, and if the ticket type id does not exist you get an empty result set rather than an exception, so you may still want a null check depending on your use case.
A table valued function
Functions become more useful once they return entire result sets instead of a single value. Here is a function that builds an order summary for a customer, joining orders with order items and aggregating quantities.
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
$$;You could write this join and aggregation in LINQ, but the SQL version is easier to read and you can test it directly in a database client before wiring it into your application. To consume it from C#, define a DTO whose property names you control explicitly, since EF Core will not build an entity graph from a raw join like this.
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 as if it were a table, aliasing each column to match the DTO property names.
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 output is a flat list of OrderSummaryDto records, one row per order. The common mistake here is forgetting an alias and expecting EF Core to match on PostgreSQL’s snake_case column names automatically. It will not, so every column needs an explicit AS clause matching your C# property name exactly, including case. If you need nested objects instead of a flat DTO, map the flat rows to a richer model afterward in C#, since EF Core cannot infer relationships from raw SQL results the way it can from tracked entities.
Functions versus procedures in PostgreSQL
PostgreSQL treats functions and procedures differently, and the distinction is worth being precise about before you pick one. Functions are meant to return values. You call them with SELECT, they run inside the calling transaction, and you can use them inside WHERE clauses and joins like any other expression. Procedures are meant for side effects: they do not return values directly, they support OUT parameters, and you invoke them with CALL. Procedures are the natural fit when an operation needs to manage its own transaction boundary or run several related updates together.
A simple rule that has served me well: reach for a function when you need to read data back, and reach for a procedure when you need to change something safely. This maps closely to how you would design service methods in C# anyway, so it is not really an unfamiliar mental model.
A procedure with validation and locking
This is where procedures earn their place. Imagine adjusting ticket inventory where you need to prevent race conditions between two concurrent purchases and validate the resulting quantity before committing.
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;
$$;- Locks the row with FOR UPDATE so a concurrent transaction cannot read and modify the same row until this one finishes
- Validates the business rule before writing anything back
- Raises a clear error message when validation fails, instead of silently corrupting the count
- Keeps the whole operation atomic within a single database round trip
You could replicate this in C# with an explicit transaction and manual locking hints, but it gets verbose fast and it is easy to get the locking wrong under load. Letting PostgreSQL own the locking logic inside the procedure is simpler and, in my experience, considerably less buggy in production. Calling it from EF Core uses ExecuteSqlAsync since the procedure has no return value.
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();
}
catch (Exception e)
{
return Results.BadRequest(e.Message);
}
});The procedure itself has no return value, so ExecuteSqlAsync just returns once the call completes. If the procedure raises an exception with RAISE EXCEPTION, PostgreSQL propagates that as a Npgsql exception on the C# side, which you can catch and turn into a proper HTTP error response. One thing to watch for in production: RAISE EXCEPTION messages end up visible to whoever reads e.Message, so avoid putting anything sensitive in them, and consider mapping known error patterns to specific HTTP status codes instead of a blanket BadRequest.
Is the string interpolation actually safe
The interpolated SQL in every example above understandably raises an eyebrow the first time you see it. Writing $”SELECT * FROM users WHERE id = {userId}” looks exactly like the textbook SQL injection mistake, so it is worth being precise about why it is not one here.
$"SELECT * FROM users WHERE id = {userId}"EF Core does not concatenate this into a string before sending it to PostgreSQL. Internally it treats the interpolated string as a FormattableString, which keeps the literal SQL text and the interpolated values separate. What actually reaches the database looks like this.
SELECT * FROM users WHERE id = @p0The value of userId is sent as a bound parameter, never as text spliced into the query. This holds for every SqlQuery and ExecuteSqlAsync call shown in this article. The one thing to genuinely avoid is calling FromSqlRaw or ExecuteSqlRaw with manually concatenated strings, since those methods do not get this automatic parameterization and will reintroduce the exact risk you are trying to avoid.
Views are functions without parameters
Database views deserve a quick mention since they follow the same calling pattern. A view is essentially a saved query with a name, and you can query it through SqlQuery exactly like a function.
var results = await dbContext.Database
.SqlQuery<ActiveCustomerDto>(
$"SELECT * FROM ticketing.active_customers")
.ToListAsync();Alternatively you can map a view to an entity type in your DbContext and get full LINQ support over it, which is worth doing if the view is queried often with varying filters. Use plain views for queries that do not need parameters, and functions when you need to parameterize the query itself.
A few production notes before you adopt this pattern
Raw SQL through functions and procedures buys you performance and expressiveness, but it comes with trade-offs worth naming honestly. Migrations become a second concern, since your EF Core migration history will not track function or procedure bodies unless you write raw SQL migrations for them, so plan for a versioning strategy for your database routines separately from your entity migrations.
Testing also changes shape. Unit tests that mock a DbContext will not exercise a stored procedure at all, so you need integration tests against a real PostgreSQL instance, for example with Testcontainers, to have any confidence in this logic. And portability drops: the moment you write PL/pgSQL with FOR UPDATE and RAISE EXCEPTION, you have coupled your application to PostgreSQL specifically, so this pattern fits best for the small number of hot paths that genuinely need it, not as a default way of writing every query.
Wrapping up
EF Core does not force a choice between LINQ and raw SQL, and this article walked through why you would want both. Functions are the right tool when you need to read data back, whether a single scalar value or a full result set. Procedures suit operations that need to change data safely, particularly when locking and validation need to happen atomically inside the database. SqlQuery and ExecuteSqlAsync give you type safety and automatic parameterization while still letting you write exactly the SQL a scenario calls for, so use LINQ for the routine queries and drop to raw SQL only where a specific performance or correctness need justifies it.
Leave a Reply