The Good Part

Entity Framework Core (EF Core) is a popular Object-Relational Mapper (ORM) that simplifies data access in .NET applications by allowing developers to use LINQ (Language Integrated Query) for interacting with databases. This abstraction provides strong typing, compile-time checking, and a more readable syntax than raw SQL.

The Problem

Developers find it easy to write a LINQ query to fetch results from database with the built-in IntelliSense support. However, beneath this convenience lies a critical challenge:

EF Core translates LINQ queries into SQL at runtime, often producing complex and inefficient SQL queries that can severely degrade database performance.

• EF Core LINQ queries are translated into SQL at runtime, often resulting in inefficient and bloated SQL statements.
• Developers may not realize the performance impact, especially on large datasets with complex joins.

⚡Tip: Performance Optimization with AsNoTracking()
When querying data using Entity Framework, the default behavior is to track the returned entities in the change tracker. This allows EF to detect changes and persist them back to the database. However, if you're only reading data and not modifying it, this tracking introduces unnecessary overhead. Therefore, for read-only operations use AsNoTracking() with LINQ to improve performance. e.g.

var customers = context.Customers.AsNoTracking().ToList();

Solution using Stored Procedure

Stored procedures have the advantage of being compiled once and stored in the database. This reduces the overhead of parsing and compiling SQL statements every time they are run.

Let us create two stored procedures for our example:

• A SELECT stored procedure to retrieve customers by country.

CREATE PROCEDURE GetCustomersByCountry
    @Country NVARCHAR(50)
AS
BEGIN
    SELECT * FROM Customers
    WHERE Country = @Country
END

• A NON-SELECT stored procedure to update a customer's contact name.

CREATE PROCEDURE UpdateCustomerContactName
    @CustomerID NCHAR(5),
    @ContactName NVARCHAR(100)
AS
BEGIN
    UPDATE Customers
    SET ContactName = @ContactName
    WHERE CustomerID = @CustomerID
END

EF core does not provide out-of-the-box support for stored procedures yet. Use one of the methods described below:

Method 1: Handcraft Stored Procedure Methods

• SELECT Stored Procedure using FromSqlRaw

For stored procedure returning data, create a method using FromSqlRaw to execute the stored procedure and map the results to a model.

public async Task<List<Customer>> GetCustomersByCountryAsync(string country)
{
    return await _context.Customers
        .FromSqlRaw("EXEC GetCustomersByCountry @Country = {0}", country)
        .ToListAsync();
}

• NON-SELECT Stored Procedure

public async Task<int> UpdateCustomerContactNameAsync(string customerId, string contactName)
{
    return await _context.Database.ExecuteSqlRawAsync(
        "EXEC UpdateCustomerContactName @CustomerID = {0}, @ContactName = {1}",
        customerId, contactName);
}

With this method developers will have to take care of the method creation and mappings, adding overhead.

Method 2: EF Core Power Tools Integration

Why use EF Core Power Tools?

• It automatically generates mappings for stored procedures and functions as LINQ-callable methods.
• Eliminates manual SQL strings, reducing errors and improving maintainability.
• Allows reverse engineering of complex database schemas.

How to Use EF Core Power Tools

1️⃣ Install the Extension

• Open Visual Studio, go to Extensions → Manage Extensions.
• Search for EF Core Power Tools, install it, and restart Visual Studio.

2️⃣ Reverse Engineer Your Database

• Right-click your project → EF Core Power Tools → Reverse Engineer.
• Select your SQL Server database.
• Select Tables, Views, and Stored Procedures (like GetCustomersByCountry).

3️⃣ Generated Code Example
When using EF Core Power Tools to reverse engineer stored procedures, the tool generates method stubs and result classes based on the stored procedure signatures. Here's how the methods for the two stored procedures would typically look after generation:

• SELECT Stored Procedure

public virtual async Task<List<GetCustomersByCountryResult>> GetCustomersByCountryAsync(string country)
{
    var countryParam = new SqlParameter("@Country", country ?? (object)DBNull.Value);

    return await this.Set<GetCustomersByCountryResult>()
        .FromSqlRaw("EXEC GetCustomersByCountry @Country", countryParam)
        .ToListAsync();
}

The generated mapping means you can now call:

var customers = await context.GetCustomersByCountryAsync(country);

• NON-SELECT Stored Procedure

public virtual async Task<int> UpdateCustomerContactNameAsync(string customerId, string contactName)
{
    var customerIdParam = new SqlParameter("@CustomerID", customerId ?? (object)DBNull.Value);
    var contactNameParam = new SqlParameter("@ContactName", contactName ?? (object)DBNull.Value);

    return await Database.ExecuteSqlRawAsync(
        "EXEC UpdateCustomerContactName @CustomerID, @ContactName",
        customerIdParam, contactNameParam);
}

Invoke the method:

int rowsAffected = await context.UpdateCustomerContactNameAsync(customerId, newContactName);

No manual SQL strings! Plus, EF Core can track and map results efficiently.

Visual Studio Screenshots for EF Core Power Tools

🔹 Step 1: Reverse Engineer Options

🔹 Step 2: Select Database

🔹 Step 3: Select Stored Procedures and Tables

Conclusion

🔸 EF Core LINQ is great for productivity, but not always for performance.
🔸 Stored procedures can optimize performance, especially for complex queries.
🔸 EF Core Power Tools automates the integration of stored procedures into EF Core, giving you:

• Clean LINQ-callable methods.
• Auto-generated mappings.
• Reduced manual SQL.