Using LINQ with Stored Procedures and Projections

Blending LINQ Queries with Database Logic

While LINQ offers a rich, expressive way to query data in C#, there are times when you want to leverage existing stored procedures for performance, legacy integration, or complex business logic already encapsulated in the database.

Entity Framework (EF) allows developers to seamlessly integrate stored procedures into LINQ workflows, combining the power of precompiled SQL with the flexibility of object-oriented querying and projections.

🔹 What Are Stored Procedures in EF Context

A stored procedure is a pre-defined SQL routine stored in the database. It can perform operations like:

• Fetching filtered or joined data sets.
• Executing inserts, updates, or deletes.
• Running complex business logic that would be inefficient to write in LINQ.

Entity Framework supports calling these stored procedures and mapping their results directly to entities, anonymous types, or custom projections. This means you can use them within your application without breaking the LINQ-based workflow.

🧱 Executing Stored Procedures with EF

Entity Framework Core provides several ways to execute stored procedures:

• Using FromSqlRaw() or FromSqlInterpolated() for queries.
• Using ExecuteSqlRaw() or ExecuteSqlInterpolated() for non-query operations.

Example 1: Returning Entities

var highValueOrders = context.Orders
    .FromSqlRaw("EXEC GetHighValueOrders @MinAmount = {0}", 1000)
    .ToList();

✅ Explanation:

• FromSqlRaw() executes the stored procedure.
• EF maps the result set directly to the Order entity type.
• The query results can still be extended with LINQ methods like .Where() or .Select().

Example with interpolation (safer for readability and parameters):

var highValueOrders = context.Orders
    .FromSqlInterpolated($"EXEC GetHighValueOrders @MinAmount = {minAmount}")
    .ToList();

🔹 Mapping Stored Procedure Results to Custom Types

Not all stored procedures return a result that maps neatly to an entity.
In such cases, you can define a custom class or projection type to receive the data.

Example 2: Custom Result Projection

public class OrderSummary
{
    public string CustomerName { get; set; }
    public int TotalOrders { get; set; }
    public decimal TotalAmount { get; set; }
}

var summaries = context.OrderSummaries
    .FromSqlRaw("EXEC GetOrderSummaries")
    .ToList();

✅ Behind the Scenes:

• EF matches the stored procedure’s column names with the properties of OrderSummary.
• The result is a strongly typed list of summary objects, ready for reporting or API use.

You can even create DbQuery<OrderSummary> or configure it with the DbContext to represent a database view or a procedure’s output.

🔹 Combining LINQ with Stored Procedure Results

Once data is retrieved via a stored procedure, it can still be queried, filtered, and projected using LINQ — just like a normal DbSet.

Example 3: Filtering Stored Procedure Results with LINQ

var recentSummaries = context.OrderSummaries
    .FromSqlRaw("EXEC GetOrderSummaries")
    .Where(s => s.TotalAmount > 5000)
    .OrderByDescending(s => s.TotalAmount)
    .ToList();

✅ Explanation:
Although the base query comes from SQL, the LINQ operators are applied in-memory after EF materialises the results — perfect for additional filtering or sorting logic that isn’t performance-critical.

🔹 Using Stored Procedures for CRUD Operations

Entity Framework can also map stored procedures to Insert, Update, and Delete operations when working with entities.

Example 4: Configuring CRUD Stored Procedures (EF6 or EF Core with Fluent API)

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>()
        .InsertUsingStoredProcedure("sp_InsertOrder")
        .UpdateUsingStoredProcedure("sp_UpdateOrder")
        .DeleteUsingStoredProcedure("sp_DeleteOrder");
}

✅ Benefit:

• Your entity operations automatically trigger the respective stored procedures.
• Maintains database-side business logic and auditing.

🔹 Advanced Projections with LINQ

Once stored procedure data is materialised, you can reshape it using LINQ projections — transforming it into DTOs, anonymous objects, or hierarchies.

Example 5: LINQ Projection

var report = context.OrderSummaries
    .FromSqlRaw("EXEC GetOrderSummaries")
    .Select(s => new
    {
        Customer = s.CustomerName,
        Orders = s.TotalOrders,
        Revenue = $"£{s.TotalAmount:F2}"
    })
    .ToList();

✅ Explanation:

• LINQ projects data into a custom structure.
• You can apply formatting, computed fields, or even nested projections.
• Perfect for preparing API responses or dashboard reports.

⚙️ Handling Parameters Safely

When passing parameters to stored procedures, never concatenate strings — always use parameterised methods or interpolated SQL.

✅ Best Practice:
Always prefer FromSqlInterpolated() for clarity and SQL injection protection.

🔍 When to Use Stored Procedures with LINQ

EF gives you the best of both worlds — stored procedures for optimised operations, LINQ for flexible application logic.

📚 Summary

✅ Best Practices

• Use stored procedures for complex, performance-sensitive queries.
• Keep projection classes lightweight and named to reflect report intent.
• Always use parameterised execution to avoid SQL injection.
• Combine stored procedure calls with LINQ projections for maximum flexibility.
• Profile and compare execution plans — don’t assume stored procedures are always faster.

💡 Conclusion

By combining LINQ’s expressive querying with the raw power of stored procedures, developers can achieve a perfect balance between maintainability and performance.

Entity Framework makes this integration smooth and secure — allowing you to use prebuilt database logic while retaining the clarity and type safety of C#.

When applied thoughtfully, this hybrid approach delivers efficient, readable, and scalable data access strategies — ideal for modern enterprise applications.