By Csharp_Admin Advanced SQL/Database February 25, 2024

Addressing the Frustration in EF.net Core

In the realm of .NET application development, Entity Framework Core (EF Core) stands out as a highly regarded Object-Relational Mapping (ORM) tool, streamlining the process of database operations with its robust LINQ queries and automatic change tracking. However, despite its comprehensive features, EF Core often introduces a level of frustration for developers when dealing with complex SQL queries, especially those requiring custom projections or intricate joins that are not straightforward to express using LINQ. This limitation becomes apparent in scenarios where the performance of LINQ queries doesn’t meet expectations or when the SQL generated by EF Core deviates from the optimized paths developers know are possible with hand-written SQL.

One common grievance arises when attempting to map the results of raw SQL queries to Data Transfer Objects (DTOs) or view models that don’t directly align with the database schema. EF Core mandates that queries executed using FromSqlRaw or FromSqlInterpolated methods return entities that EF Core is tracking, thereby complicating scenarios where the desired outcome is a projection into a custom DTO. This constraint often leads to inefficient workarounds, such as fetching more data than necessary and then manually transforming it in-memory, which can detrimentally impact application performance and developer productivity.

Problem

The goal is to aggregate invoice data by ciient, calculating the total cost of invoices per cllient and then project this aggregated data into a DTO (Data Transfer Object) for further use, likely in a user interface or report.

Here is the database scheme:

The aim is to create a query that computes the sum of the spending of each client and map it to the class StatDto

class StatDto{
    public int ClientId;
    public String ClientName;
    public double Amount;
}

Solving with EF Core

Given the limitations of EF Core in directly executing raw SQL queries and projecting to non-entity types, a common workaround involves leveraging LINQ to perform similar operations in a way that EF Core supports. This method fully utilizes EF Core’s ability to translate LINQ queries into efficient SQL, ensuring that the operation remains within the ORM’s optimization capabilities.

Here’s how you might achieve the desired outcome using LINQ with EF Core:

var stats = dbContext.Clients
    .Select(u => new StatDto
    {
        ClientId = u.Id,
        ClientName = u.Name,
        cost = u.Invoices
            .SelectMany(i => i.InvoiceDetails)
            .Sum(id => id.Quantity * id.UnitPrice) // Aggregate function to calculate cost
    }).ToList();

Solving with Dapper

Enter Dapper, a micro ORM lauded for its simplicity and performance. Dapper extends the capabilities of .NET’s IDbConnection to execute raw SQL queries directly, offering a seamless pathway to map results to any custom object or DTO, without the overhead of additional processing or manual mapping. This feature is particularly beneficial for executing complex queries or when precise control over the SQL execution plan is crucial. Dapper shines in its ability to execute these operations with minimal overhead, translating to significantly improved performance compared to EF Core, especially in data-intensive applications.

Dapper excels in executing raw SQL and mapping the results directly to a DTO, making it a perfect fit for this scenario. Here’s how you can accomplish the task using Dapper:

using System.Data.SqlClient;
using Dapper;

var connectionString = "your_connection_string"; // Define your connection string

using (var connection = new SqlConnection(connectionString))
{
    var query = @"
        SELECT u.Id AS ClientId, u.Name AS ClientName, SUM(id.Quantity * id.UnitPrice) AS Amount
        FROM Clients u
        INNER JOIN Invoice i ON u.Id = i.ClientId
        INNER JOIN InvoiceDetails id ON i.Id = id.InvoiceId
        GROUP BY u.Id, u.Name";

    var statsDto = connection.Query<StatDto>(query).ToList();
}

The complementary strengths of EF Core and Dapper present a compelling case for their combined use in .NET projects. While EF Core excels at managing complex domain models and providing a high-level abstraction over database operations, Dapper offers a streamlined, performance-oriented approach for scenarios where custom SQL queries and mappings are necessary. This synergy allows developers to leverage the full ORM capabilities of EF Core for standard operations while utilizing Dapper for those critical areas where custom SQL queries and projections are essential, thereby mitigating frustration and enhancing the overall efficiency and performance of .NET applications.

Conclusion

In conclusion, the frustration arising from EF Core’s limitations with native custom queries and projections can be effectively addressed by integrating Dapper into the development workflow. This integration ensures that developers can enjoy the best of both worlds: the comprehensive ORM features of EF Core and the raw SQL execution prowess of Dapper. By doing so, developers can navigate the complexities of database operations with greater flexibility, performance, and ease, ultimately leading to more robust and efficient .NET applications.