Unlocking Stored Procedures in C# with Entity Framework and SQL Server

Key Insights for Stored Procedure Integration

EF Core's FromSqlRaw and FromSqlInterpolated: These methods are the primary tools for executing stored procedures that return data, mapping the results directly to your defined entities.
ExecuteSqlRaw for Non-Query Operations: For stored procedures that perform data modifications (INSERT, UPDATE, DELETE) without returning entities, ExecuteSqlRaw is the go-to method, returning the number of affected rows.
Parameterization is Crucial: Always use parameterized queries (e.g., SqlParameter objects or interpolated strings) to prevent SQL injection vulnerabilities and ensure type safety.

Understanding Stored Procedures in the Entity Framework Context

Stored procedures are pre-compiled collections of SQL statements stored in your database. They offer significant advantages such as improved performance, reduced network traffic, enhanced security, and better code modularity by encapsulating complex business logic within the database layer. Entity Framework (EF), Microsoft's object-relational mapper (ORM), allows developers to interact with databases using .NET objects, abstracting away much of the underlying SQL. When integrating stored procedures into EF applications, you bridge the gap between the ORM and direct database control, leveraging the strengths of both.

EF Core vs. EF6: A Tale of Two Frameworks

The approach to executing stored procedures in C# with SQL Server largely depends on the version of Entity Framework you are using:

Entity Framework Core (EF Core): This is the modern, lightweight, and extensible version of EF, designed for cross-platform applications and offering a more streamlined API for raw SQL execution.
Entity Framework 6 (EF6): The classic, mature version of EF, primarily used with .NET Framework applications. It offers more direct integration with stored procedures via designer-based tools (EDMX) in Database-First scenarios.

While both versions support stored procedure execution, EF Core simplifies the process through explicit methods for raw SQL queries, making it the preferred choice for new development.

Executing Stored Procedures for Data Retrieval (Query Operations)

When your stored procedure is designed to fetch data from the database, you'll use methods that can map the returned result set to your C# entities or custom data transfer objects (DTOs). The key is to ensure the shape of the result set returned by the stored procedure closely matches the properties of the entity or DTO you are mapping to.

Using `FromSqlRaw` in EF Core

The FromSqlRaw method, available on a DbSet<TEntity>, is the most common and robust way to execute stored procedures that return entity data in EF Core. It takes a raw SQL string and an array of parameters. The TEntity in DbSet<TEntity> specifies the type to which the results will be mapped.

Example: Fetching Products by Category


using Microsoft.EntityFrameworkCore;
using Microsoft.Data.SqlClient; // For SqlParameter

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public string Category { get; set; }
}

public class ApplicationDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("YourConnectionString");
    }
}

public class StoredProcedureExamples
{
    public List<Product> GetProductsByCategory(string categoryName)
    {
        using (var context = new ApplicationDbContext())
        {
            // Using positional parameters in the SQL string
            var products = context.Products
                .FromSqlRaw("EXEC dbo.GetProductsByCategory @p0", categoryName)
                .ToList();
            return products;
        }
    }

    public List<Product> GetProductsByPriceRange(decimal minPrice, decimal maxPrice)
    {
        using (var context = new ApplicationDbContext())
        {
            // Using SqlParameter objects for named parameters (more explicit)
            var minPriceParam = new SqlParameter("@MinPrice", minPrice);
            var maxPriceParam = new SqlParameter("@MaxPrice", maxPrice);

            var products = context.Products
                .FromSqlRaw("EXEC dbo.GetProductsByPriceRange @MinPrice, @MaxPrice", minPriceParam, maxPriceParam)
                .ToList();
            return products;
        }
    }
}

Important Note: When using FromSqlRaw, the column names and data types in the stored procedure's result set must exactly match the properties of the Product entity. If there's a mismatch, EF Core may fail to map the results correctly.

Leveraging `FromSqlInterpolated` in EF Core (C# 6.0+)

For a more concise syntax and enhanced readability, FromSqlInterpolated provides a way to pass parameters using C# string interpolation. EF Core internally converts these interpolated strings into parameterized queries, maintaining security.

Example: Interpolated String for Product Search


// Inside StoredProcedureExamples class
public List<Product> SearchProducts(string searchTerm)
{
    using (var context = new ApplicationDbContext())
    {
        var products = context.Products
            .FromSqlInterpolated($"EXEC dbo.SearchProducts {searchTerm}")
            .ToList();
        return products;
    }
}

Handling Non-Entity Results or DTOs

If your stored procedure returns a result set that doesn't directly map to an existing DbSet entity (e.g., a custom projection with fewer columns, or aggregate values), you can define a "keyless entity type" in EF Core or map to a custom DTO. You would typically use context.Set<TResult>() to achieve this.

Example: Custom Report Data


public class ProductSummaryDto
{
    public string CategoryName { get; set; }
    public int TotalProducts { get; set; }
    public decimal AveragePrice { get; set; }
}

// In your ApplicationDbContext, configure ProductSummaryDto as a keyless entity
// protected override void OnModelCreating(ModelBuilder modelBuilder)
// {
//     modelBuilder.Entity<ProductSummaryDto>().HasNoKey();
// }

// In StoredProcedureExamples class
public List<ProductSummaryDto> GetCategorySummaries()
{
    using (var context = new ApplicationDbContext())
    {
        var summaries = context.Set<ProductSummaryDto>()
            .FromSqlRaw("EXEC dbo.GetProductCategorySummaries")
            .ToList();
        return summaries;
    }
}

This approach allows for flexible mapping of arbitrary result sets.

Executing Stored Procedures for Data Modification (Non-Query Operations)

When a stored procedure performs operations like INSERT, UPDATE, or DELETE and does not return a result set that maps to an entity, you use different methods to execute them. These methods return the number of rows affected by the operation.

Using `ExecuteSqlRaw` in EF Core

The ExecuteSqlRaw method, called on DbContext.Database, is used for executing non-query stored procedures. It's ideal for procedures that modify data and return an integer indicating the number of rows affected.

Example: Updating Employee Salary


// In StoredProcedureExamples class
public int UpdateEmployeeSalary(int employeeId, decimal newSalary)
{
    using (var context = new ApplicationDbContext())
    {
        var rowsAffected = context.Database.ExecuteSqlRaw(
            "EXEC dbo.UpdateEmployeeSalary @EmployeeId, @NewSalary",
            new SqlParameter("@EmployeeId", employeeId),
            new SqlParameter("@NewSalary", newSalary));
        return rowsAffected;
    }
}

Similar to FromSqlRaw, ExecuteSqlInterpolated can also be used for non-query operations with C# string interpolation.

Working with Entity Framework 6 (EF6)

For applications still using EF6, the approach to stored procedures differs, especially in Database-First or Model-First scenarios where an EDMX model is used.

`Database.SqlQuery` for Querying Data

In EF6, Database.SqlQuery<TEntity> (or Database.SqlQuery<TResult>) is used to execute SQL queries, including stored procedures, and map the results to entities or scalar/non-entity types.

Example: Fetching Employees in EF6


// EF6 Context
public class LegacyDbContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }
    // Constructor and other configurations
}

public class EF6StoredProcedureExamples
{
    public List<Employee> GetEmployeesByDepartment(string departmentName)
    {
        using (var context = new LegacyDbContext())
        {
            var deptParam = new SqlParameter("@DepartmentName", departmentName);
            var employees = context.Database.SqlQuery<Employee>(
                "EXEC dbo.GetEmployeesByDepartment @DepartmentName", deptParam).ToList();
            return employees;
        }
    }
}

`Database.ExecuteSqlCommand` for Non-Query Operations

For CUD (Create, Update, Delete) operations in EF6, Database.ExecuteSqlCommand is used, similar to EF Core's ExecuteSqlRaw.

Example: Deleting a Record in EF6


// In EF6StoredProcedureExamples class
public int DeleteProduct(int productId)
{
    using (var context = new LegacyDbContext())
    {
        var idParam = new SqlParameter("@ProductId", productId);
        int affectedRows = context.Database.ExecuteSqlCommand(
            "EXEC dbo.DeleteProductById @ProductId", idParam);
        return affectedRows;
    }
}

Function Imports in EDMX Model (EF6 Database-First)

In EF6 Database-First, you can import stored procedures directly into your EDMX model. This generates strongly-typed methods on your DbContext, making calls feel like regular method invocations.

Best Practices and Important Considerations

When working with stored procedures in Entity Framework, adhering to certain best practices ensures robust, secure, and maintainable code.

An illustration suggesting the combination of Stored Procedures and Entity Framework for optimized performance.

Parameter Handling and SQL Injection Prevention

Always use parameterized queries. Never concatenate user-supplied input directly into your SQL strings. EF Core's FromSqlRaw, FromSqlInterpolated, ExecuteSqlRaw, and ExecuteSqlInterpolated methods, along with SqlParameter objects, are designed to handle parameterization securely, preventing SQL injection attacks.

Result Set Matching (EF Core)

For FromSqlRaw and FromSqlInterpolated in EF Core, the columns returned by your stored procedure must precisely match the properties of the TEntity or DTO you are mapping to. This includes column names and data types. If there's a mismatch, mapping issues or runtime errors may occur.

Change Tracking (`.AsNoTracking()`)

When retrieving data via FromSqlRaw or FromSqlInterpolated and you don't intend to modify the retrieved entities, use .AsNoTracking() to disable change tracking. This can significantly improve performance by reducing the overhead of EF Core's change tracker.


var products = context.Products
    .FromSqlRaw("EXEC dbo.GetActiveProducts")
    .AsNoTracking() // Disable change tracking
    .ToList();

Error Handling

Wrap your stored procedure calls in try-catch blocks to gracefully handle database-related exceptions (e.g., DbUpdateException, SqlException). This allows you to log errors, provide user feedback, or implement retry logic.

Migrations and Stored Procedures

While EF Core migrations primarily manage database schema changes, you can include SQL scripts to create, alter, or drop stored procedures within your migrations. This ensures that your stored procedures are deployed alongside your application's database schema changes.


// Example of adding a stored procedure creation to a migration
protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(@"
        CREATE PROCEDURE dbo.GetProductsByCategory
            @CategoryId INT
        AS
        BEGIN
            SELECT ProductId, Name, Price, CategoryId
            FROM Products
            WHERE CategoryId = @CategoryId;
        END;
    ");
}

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql("DROP PROCEDURE dbo.GetProductsByCategory;");
}

Performance and Architectural Considerations

Using stored procedures can offer performance benefits, especially for complex operations or when large datasets are involved. They reduce network round trips by executing multiple SQL statements as a single batch and can be pre-compiled on the database server. However, over-reliance on stored procedures can sometimes make debugging and maintenance more complex, as logic is split between the application code and the database.

A radar chart comparing key aspects of integrating stored procedures with Entity Framework, highlighting strengths in security, control, and performance, with considerations for maintenance and flexibility.

Choosing the Right Approach

The decision to use stored procedures or pure LINQ-to-Entities often comes down to specific requirements, team expertise, and the complexity of the database operations. Stored procedures are invaluable for:

Complex reporting queries that are hard to express efficiently in LINQ.
Encapsulating sensitive business logic directly on the database.
Legacy system integration where existing stored procedures must be utilized.
Performance-critical operations requiring fine-tuned SQL execution.

For standard CRUD operations and simpler queries, LINQ-to-Entities typically offers greater productivity, type safety, and easier maintenance within the C# codebase.

An illustrative diagram demonstrating the flow and components involved when using stored procedures with Entity Framework Core.

mindmap root["Running Stored Procedures in C# with EF & SQL Server"] EF_Core_Methods["EF Core Methods"] FromSqlRaw["FromSqlRaw"] Query_Entities["Query Entities"] Parameters_Safe["Parameterized ({@p0}, SqlParameter)"] ResultSet_Match["Result Set Must Match Entity"] FromSqlInterpolated["FromSqlInterpolated"] Query_Entities_Interpolated["Query Entities (Interpolated String)"] Parameters_Direct["Parameters Handled Directly"] ResultSet_Match_Interpolated["Result Set Must Match Entity"] ExecuteSqlRaw["ExecuteSqlRaw"] Non_Query["Non-Query Operations (Insert, Update, Delete)"] Returns_AffectedRows["Returns int (Rows Affected)"] Database_Property["Used on DbContext.Database"] EF6_Methods["EF6 Methods"] Database_SqlQuery["Database.SqlQuery<T>"] Query_Entities_EF6["Query Entities/DTOs"] SqlParameter_Use["Uses SqlParameter"] Database_ExecuteSqlCommand["Database.ExecuteSqlCommand"] Non_Query_EF6["Non-Query Operations"] Returns_AffectedRows_EF6["Returns int (Rows Affected)"] Function_Imports["Function Imports (EDMX)"] Designer_Generated_Methods["Designer Generated Methods on Context"] Strongly_Typed_Calls["Strongly-Typed Calls"] Best_Practices["Best Practices"] Sql_Injection_Prevention["Prevent SQL Injection"] Parameterized_Queries["Always Use Parameterized Queries"] ResultSet_Alignment["Result Set Alignment"] Match_Properties["Match Entity/DTO Properties"] Performance_Optimization["Performance Optimization"] AsNoTracking["AsNoTracking() for Read-Only"] Error_Handling["Error Handling"] Try_Catch_Blocks["Use Try-Catch Blocks"] Migration_Integration["Migration Integration"] SQL_Scripts_in_Migrations["Include SPs in Migrations"]

Video Deep Dive: Stored Procedures with EF Core in .NET 8

To further illustrate the concepts discussed, this video provides a practical demonstration of integrating stored procedures with Entity Framework Core in a modern .NET 8 environment. It covers the fundamentals of what stored procedures are, why they are used, and step-by-step examples of how to implement and call them from your C# application.

This video explains how to work with stored procedures in Entity Framework Core within a .NET 8 application. It covers their definition, usage scenarios, and practical implementation, including parameter passing and result mapping.

Summary Table of Stored Procedure Execution Methods

The table below provides a concise summary of the primary methods used to execute stored procedures in both Entity Framework Core and Entity Framework 6, outlining their purpose and common usage scenarios.

Entity Framework Version	Purpose	Method Name	Description	Returns	Parameter Handling
EF Core	Query (Return Entities)	`DbSet<TEntity>.FromSqlRaw()`	Executes raw SQL query/stored procedure and maps results to `TEntity`.	`IQueryable<TEntity>`	Positional placeholders (`@p0`) or `SqlParameter` objects.
EF Core	Query (Return Entities, Interpolated)	`DbSet<TEntity>.FromSqlInterpolated()`	Executes raw SQL query/stored procedure with C# string interpolation for parameters.	`IQueryable<TEntity>`	C# string interpolation (`${param}`).
EF Core	Non-Query (CUD Operations)	`DbContext.Database.ExecuteSqlRaw()`	Executes SQL command/stored procedure that doesn't return entities.	`int` (rows affected)	Positional placeholders (`@p0`) or `SqlParameter` objects.
EF Core	Non-Query (CUD Operations, Interpolated)	`DbContext.Database.ExecuteSqlInterpolated()`	Executes SQL command/stored procedure with C# string interpolation.	`int` (rows affected)	C# string interpolation (`${param}`).
EF6	Query (Return Entities/DTOs)	`DbContext.Database.SqlQuery<TEntity>()`	Executes raw SQL query/stored procedure and maps results to `TEntity`.	`DbRawSqlQuery<TEntity>`	`SqlParameter` objects.
EF6	Non-Query (CUD Operations)	`DbContext.Database.ExecuteSqlCommand()`	Executes SQL command/stored procedure that doesn't return entities.	`int` (rows affected)	`SqlParameter` objects.
EF6 (Database-First)	Query/Non-Query (Function Import)	Auto-generated method (e.g., `context.MyStoredProcedure()`)	Method generated by the EDMX designer after importing a stored procedure.	Varies (`ObjectResult<T>` or `int`)	Direct method parameters.

Frequently Asked Questions

Can I use LINQ directly on the results of a stored procedure called with FromSqlRaw?

Yes, after calling FromSqlRaw, you can compose LINQ queries on the returned IQueryable<TEntity>. EF Core will treat the stored procedure's output as a subquery, allowing you to filter, order, or project further using LINQ.

What if my stored procedure returns multiple result sets?

Entity Framework Core does not natively support mapping multiple result sets from a single stored procedure call. For such scenarios, you might need to resort to raw ADO.NET for more fine-grained control over data readers or consider refactoring your stored procedure to return a single result set if possible.

How do I pass output parameters or return values from a stored procedure in Entity Framework?

For output parameters or return values, you typically need to use SqlParameter objects and configure their direction (ParameterDirection.Output or ParameterDirection.ReturnValue). After executing the command, you can then retrieve the values from these SqlParameter objects.

Conclusion

Running stored procedures in C# with Entity Framework and SQL Server is a common and effective practice, offering a blend of ORM convenience and direct database power. Whether you're working with the modern EF Core or the established EF6, the framework provides robust methods to execute your stored procedures for both data retrieval and modification. By prioritizing parameterized queries, understanding result set mapping, and applying best practices, you can seamlessly integrate these powerful database objects into your .NET applications, leading to optimized performance and improved data management.