How to call Stored Procedure in Entity Framework 6 (Code-First)?

Calling Stored Procedures in Entity Framework 6 (Code-First)

Learn how to execute stored procedures in Entity Framework 6 Code-First projects, covering various scenarios from simple execution to mapping complex results.

Entity Framework (EF) Code-First provides a powerful way to interact with your database using C# classes. While EF excels at generating and managing database schemas, there are often scenarios where you need to leverage existing stored procedures for performance, complex logic, or integration with legacy systems. This article will guide you through the process of calling stored procedures using EF6 Code-First, covering different return types and parameter handling.

Understanding Stored Procedure Execution in EF6

When working with stored procedures in Entity Framework, the primary method for execution is DbContext.Database.SqlQuery<TElement>() or DbContext.Database.ExecuteSqlCommand(). The choice between these two depends on whether your stored procedure returns data or simply performs an action (like INSERT, UPDATE, DELETE) without returning a result set. For stored procedures that return data, you'll typically map the results to a C# class, which doesn't necessarily need to be part of your DbContext's DbSets.

flowchart TD
    A[Application Code] --> B{Call Stored Procedure?}
    B -->|Returns Data| C[Use DbContext.Database.SqlQuery<T>()]
    B -->|No Data Returned| D[Use DbContext.Database.ExecuteSqlCommand()]
    C --> E{Map Results to C# Class}
    D --> F[Execute Action]
    E --> G[Process Data]
    F --> H[Operation Complete]

Decision flow for calling stored procedures in Entity Framework 6.

Calling Stored Procedures with No Return Value

For stored procedures that perform actions like inserting, updating, or deleting data and do not return a result set, you should use the DbContext.Database.ExecuteSqlCommand() method. This method returns the number of rows affected by the command. It's ideal for operations where you only need to confirm execution or check the impact on the database.

public void UpdateProductStatus(int productId, string newStatus)
{
    using (var context = new MyDbContext())
    {
        // Stored procedure: EXEC UpdateProductStatus @ProductId, @NewStatus
        context.Database.ExecuteSqlCommand(
            "EXEC UpdateProductStatus @p0, @p1", 
            productId, 
            newStatus
        );
    }
}

// Example Stored Procedure (SQL Server)
/*
CREATE PROCEDURE UpdateProductStatus
    @ProductId INT,
    @NewStatus NVARCHAR(50)
AS
BEGIN
    UPDATE Products
    SET Status = @NewStatus
    WHERE ProductId = @ProductId;
END;
*/

Executing a stored procedure without a return value using ExecuteSqlCommand().

Calling Stored Procedures Returning Scalar Values

If your stored procedure returns a single value (e.g., a count, a sum, or an ID), you can still use SqlQuery<TElement>() and specify the scalar type as TElement. The result will be a collection containing a single item of that type.

public int GetProductCountByStatus(string status)
{
    using (var context = new MyDbContext())
    {
        // Stored procedure: SELECT COUNT(*) FROM Products WHERE Status = @Status
        var count = context.Database.SqlQuery<int>(
            "EXEC GetProductCountByStatus @p0", 
            status
        ).FirstOrDefault();
        return count;
    }
}

// Example Stored Procedure (SQL Server)
/*
CREATE PROCEDURE GetProductCountByStatus
    @Status NVARCHAR(50)
AS
BEGIN
    SELECT COUNT(*)
    FROM Products
    WHERE Status = @Status;
END;
*/

Retrieving a scalar value from a stored procedure.

Calling Stored Procedures Returning Complex Types

For stored procedures that return a result set with multiple columns, you'll need to define a C# class to map these results. This class does not need to be part of your DbContext's DbSets, but its properties must match the column names and types returned by the stored procedure. This is a common and powerful way to leverage complex database logic while still working within the EF context.

public class ProductReportItem
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public decimal Price { get; set; }
    public string Status { get; set; }
}

public List<ProductReportItem> GetProductsByPriceRange(decimal minPrice, decimal maxPrice)
{
    using (var context = new MyDbContext())
    {
        // Stored procedure: SELECT ProductId, ProductName, Price, Status FROM Products WHERE Price BETWEEN @MinPrice AND @MaxPrice
        var products = context.Database.SqlQuery<ProductReportItem>(
            "EXEC GetProductsByPriceRange @p0, @p1", 
            minPrice, 
            maxPrice
        ).ToList();
        return products;
    }
}

// Example Stored Procedure (SQL Server)
/*
CREATE PROCEDURE GetProductsByPriceRange
    @MinPrice DECIMAL(18, 2),
    @MaxPrice DECIMAL(18, 2)
AS
BEGIN
    SELECT ProductId, ProductName, Price, Status
    FROM Products
    WHERE Price BETWEEN @MinPrice AND @MaxPrice;
END;
*/

Mapping stored procedure results to a custom C# class.

Handling Output Parameters

While SqlQuery and ExecuteSqlCommand are great for input parameters and result sets, handling output parameters requires a slightly different approach, often involving SqlParameter objects. This gives you more granular control over parameter direction and type.

using System.Data.SqlClient;

public string GetProductDescription(int productId)
{
    using (var context = new MyDbContext())
    {
        var productIdParam = new SqlParameter("@ProductId", productId);
        var descriptionParam = new SqlParameter
        {
            ParameterName = "@Description",
            SqlDbType = System.Data.SqlDbType.NVarChar,
            Size = 255, // Important: Set size for string output parameters
            Direction = System.Data.ParameterDirection.Output
        };

        context.Database.ExecuteSqlCommand(
            "EXEC GetProductDescription @ProductId, @Description OUT", 
            productIdParam, 
            descriptionParam
        );

        return descriptionParam.Value.ToString();
    }
}

// Example Stored Procedure (SQL Server)
/*
CREATE PROCEDURE GetProductDescription
    @ProductId INT,
    @Description NVARCHAR(255) OUTPUT
AS
BEGIN
    SELECT @Description = Description
    FROM Products
    WHERE ProductId = @ProductId;
END;
*/

Using SqlParameter to handle output parameters from a stored procedure.