Working with Stored Procedures in Queries

In this lesson, you will learn how to bridge the gap between EF Core’s object-oriented LINQ syntax and custom procedural SQL logic stored directly on your database.

The Role of Stored Procedures in EF Core

While LINQ is excellent for standard CRUD operations, some business logic is best kept inside the database for performance, security, or consistency. EF Core provides a powerful gateway to these database-level procedures using the FromSqlRaw method. This allows you to execute a stored procedure and map the results directly to your entity types, treating them just like any other DbSet.

Executing Stored Procedures

To execute a stored procedure, your entity must be part of your DbContext. Once defined, you can call the procedure as if it were a standard LINQ query.

Imagine you have a stored procedure named GetHighValueOrders that accepts a decimal parameter. You can invoke it like this:

decimal threshold = 500.00m;

// Using FromSqlRaw to execute the procedure
var highValueOrders = context.Orders
    .FromSqlRaw("EXEC GetHighValueOrders @Threshold = {0}", threshold)
    .ToList();

Important Considerations

When integrating stored procedures, keep these two rules in mind:

1. Mapping Requirements: The result set returned by your stored procedure must include all the columns that correspond to the properties of your entity type. If the procedure returns extra columns, EF Core will simply ignore them; if it is missing columns, the query will throw an exception.
2. Compositionality: Because FromSqlRaw returns an IQueryable<T>, you can chain standard LINQ methods after the procedure execution (e.g., .Where(o => o.Status == "Active")). EF Core will wrap your procedure call in a subquery, allowing you to further filter the results in memory or on the database side.

Be careful with raw string concatenation. Always use the {0} parameter syntax shown above to protect your application against SQL injection attacks, ensuring that your inputs are treated as data, not executable code.

Key Takeaway: Use FromSqlRaw to execute stored procedures within EF Core, treating the database output as a standard IQueryable that can be further refined with LINQ.