Executing Stored Procedures

In this lesson, you will learn how to trigger stored procedures from EF Core and map the returned result sets directly into your strongly-typed model entities.

While EF Core is famous for abstracting SQL through LINQ, sometimes performance requirements or legacy database constraints dictate that we use stored procedures. In EF Core, if you want to execute a stored procedure that returns data belonging to an entity type, you use the FromSql method.

Mapping to Entities

For EF Core to map the result of a stored procedure to an entity, the result set must contain all the columns mapped to that entity's properties. If your procedure returns a subset of columns, the mapping will fail or leave properties at their default values.

Imagine you have a Product entity and a stored procedure named GetDiscontinuedProducts. You can execute it and treat the results as a DbSet like this:

// Executing the procedure and mapping to Product entities
var discontinuedProducts = context.Products
    .FromSqlRaw("EXEC GetDiscontinuedProducts")
    .ToList();

Important Considerations

When using FromSqlRaw, keep the following in mind:

1. Entity Tracking: Just like standard LINQ queries, the entities returned by your stored procedure are tracked by the ChangeTracker by default. If you update a property on one of these objects and call SaveChanges(), EF Core will attempt to issue an UPDATE statement.
2. Parameters: Never concatenate strings into your SQL string to avoid SQL injection. Always use the SqlParameter class:

var categoryParam = new SqlParameter("@Category", "Electronics");

var products = context.Products
    .FromSqlRaw("EXEC GetProductsByCategory @Category", categoryParam)
    .ToList();

3. Constraints: The stored procedure must return a result set that matches the entity's schema. You cannot use FromSql for procedures that return multiple result sets or scalar values (like a single int count); for those scenarios, you would drop down to raw ADO.NET using context.Database.GetDbConnection().

By using FromSql, you gain the performance benefits of a pre-compiled database procedure while maintaining the convenience of having your data surfaced as fully-functional, tracked domain objects.

Key Takeaway: Use the FromSql method to map stored procedure output directly to your entities, ensuring the result set matches your model schema and using parameterized inputs to maintain security.