Choosing the right data access technology is crucial for building efficient and maintainable .NET applications. Dapper, a micro-ORM, and Entity Framework (EF), a full-featured ORM, offer distinct approaches to database interaction. While Dapper shines in simplicity and performance, Entity Framework excels with features like LINQ queries and change tracking. This article explores a real-world example of data retrieval using both tools, comparing their strengths, weaknesses, and performance implications.

Using Dapper

Stored Procedure

The following code demonstrates data retrieval using Dapper and a stored procedure:

// interface
public interface ISqlDataAccess
{
    Task<IEnumerable<T>> LoadDataAsync<T, U>(string storedprocedure, U parameters, string connectionStringName = "default");
    Task SaveDataAsync<T>(string storedprocedure, T parameters, string connectionStringName = "default");
}

// class
using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System.Data;

namespace DataAccessLibrary.Data
{
    public class SqlDataAccess : ISqlDataAccess
    {
        private IConfiguration _config;
        public SqlDataAccess(IConfiguration config)
        {
            _config = config;
        }
        public async Task<IEnumerable<T>> LoadDataAsync<T, U>(string storedprocedure, U parameters, string connectionStringName = "default")
        {
            string connectionString = _config.GetConnectionString(connectionStringName)!;
            using IDbConnection connection = new SqlConnection(connectionString);
            var rows = await connection.QueryAsync<T>(storedprocedure, parameters, commandType: CommandType.StoredProcedure);
            return rows;
        }
        public async Task SaveDataAsync<T>(string storedprocedure, T parameters, string connectionStringName = "default")
        {
            string connectionString = _config.GetConnectionString(connectionStringName)!;
            using IDbConnection connection = new SqlConnection(connectionString);
            await connection.ExecuteAsync(storedprocedure, parameters, commandType: CommandType.StoredProcedure);
        }
    }
}

// implementation
public class PersonData
{
    private readonly ISqlDataAccess _dataAccess;
    public PersonData(ISqlDataAccess dataAccess)
    {
        _dataAccess = dataAccess;
    }
    public async Task<IEnumerable<Person>> GetAllPeople()
    {
        var output = await _dataAccess.LoadDataAsync<Person, dynamic>(
                "dbo.spPersonGetAll",
                new { }
        );
        return output;
    }
    public async Task UpdatePerson(Person person)
    {
        await _dataAccess.SaveDataAsync("dbo.spPersonUpdate", person);
    }
    public async Task InsertPerson(Person person)
    {
        await _dataAccess.SaveDataAsync("dbo.spPersonInsert", new { person.FirstName, person.LastName });
    }
    public async Task DeletePerson(int id)
    {
        await _dataAccess.SaveDataAsync("dbo.spPersonDelete", new { Id = id });
    }
}

Raw SQL Query

Here’s an example of how you can execute a simple SQL query using Dapper.

// class
using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System.Data;
using System.Threading.Tasks;
using System.Collections.Generic;

public class SqlDataAccess
{
    private IConfiguration _config;
    
    public SqlDataAccess(IConfiguration config)
    {
        _config = config;
    }
    
    public async Task<IEnumerable<Employee>> LoadEmployeesByDepartment(string department, string connectionStringName = "default")
    {
        string connectionString = _config.GetConnectionString(connectionStringName);
        using IDbConnection connection = new SqlConnection(connectionString);

        // Raw SQL query with parameterized input
        string query = "SELECT Id, Name, Position FROM Employees WHERE Department = @Department";

        // Execute the query and return the results as a list of Employee objects
        var employees = await connection.QueryAsync<Employee>(query, new { Department = department });

        return employees;
    }
}

Using Entity Framework and LINQ

Stored Procedure

Here’s how the same functionality can be implemented using Entity Framework:

using Microsoft.EntityFrameworkCore;

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options) { }

    public DbSet<T> Data<T>() where T : class
    {
        return Set<T>();
    }
}

public class EFDataAccess
{
    private readonly ApplicationDbContext _context;

    public EFDataAccess(ApplicationDbContext context)
    {
        _context = context;
    }

    public async Task<IEnumerable<T>> LoadData<T>(string storedProcedure, object parameters = null) where T : class
    {
        // Use FromSqlInterpolated to call the stored procedure
        return await _context.Data<T>().FromSqlInterpolated($"EXEC {storedProcedure}").ToListAsync();
    }
}

Raw SQL Query

Here’s how you can perform the same query using Entity Framework Core to retrieve employees by their department.

// model
public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Position { get; set; }
    public string Department { get; set; }
}

// dbcontext
using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }

    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
}

// execute query
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

public class SqlDataAccess
{
    private readonly AppDbContext _context;

    public SqlDataAccess(AppDbContext context)
    {
        _context = context;
    }

    public async Task<List<Employee>> LoadEmployeesByDepartment(string department)
    {
        // Use LINQ to query the Employees table and filter by Department
        var employees = await _context.Employees
                                      .Where(e => e.Department == department)
                                      .ToListAsync();
        
        return employees;
    }
}

Comparing Dapper and Entity Framework

Performance

Dapper: Faster due to minimal overhead and direct database access.

Dapper is built directly on ADO.NET and focuses on lightweight execution. It doesn’t maintain a context, track changes, or validate models. This lack of additional layers results in minimal overhead, allowing queries to execute faster and consume fewer resources. Dapper retrieves raw data and maps it to objects without adding extra processing, making it highly efficient for read-heavy applications.

Entity Framework: Slower due to change tracking, model validation, and more.

Entity Framework, on the other hand, includes several powerful but resource-intensive features such as change tracking, identity resolution, and model validation. These features enable EF to automatically synchronize database and object states, enforce data integrity, and manage complex relationships. However, they add significant overhead during query execution, especially for large datasets or highly concurrent applications. This can make EF slower compared to Dapper in scenarios that demand high-performance data access.

Ease of Use

Dapper: Simple and lightweight, no model tracking or state management.

Dapper is designed to be simple and lightweight. It doesn’t require a DbContext, change tracking, or state management. You execute raw SQL or stored procedures and map the results to objects with minimal configuration. This simplicity makes it easy to get started and maintain, especially for straightforward CRUD operations or applications where you want complete control over SQL.

Entity Framework: More complex with configurations and setup.

Entity Framework introduces more complexity due to its abstraction layers. It requires a DbContext for managing database interactions and involves configurations for relationships, data annotations, migrations, and change tracking. While these features add flexibility and reduce boilerplate for larger projects, they come with a steeper learning curve and additional setup effort compared to Dapper.

Flexibility

Dapper: Works well with raw SQL and stored procedures.

Dapper is highly flexible, allowing you to write raw SQL queries and call stored procedures directly. This gives you complete control over query structure, performance optimizations, and database-specific features. It’s ideal for developers who are comfortable with SQL and need precise execution plans or custom logic in the database layer.

Entity Framework: Strongly tied to LINQ and object-relational mapping.

Entity Framework is strongly tied to LINQ and its object-relational mapping (ORM) approach. While LINQ simplifies querying and makes code more readable, it limits you to EF’s abstractions. Custom SQL or stored procedure support is available but not as straightforward as in Dapper, and some database-specific optimizations may be harder to achieve.

Learning Curve

Dapper: Easier for developers familiar with SQL.

Dapper has a shallow learning curve for developers already familiar with SQL. Since it relies on raw SQL queries and stored procedures, there’s little to learn beyond its simple API for mapping query results to objects. This makes it quick to adopt, especially for teams comfortable working directly with databases.

Entity Framework: Steeper due to abstraction layers and conventions.

Entity Framework has a steeper learning curve due to its abstraction layers and conventions. Developers need to understand concepts like DbContext, change tracking, migrations, and LINQ-to-SQL translation. While these abstractions reduce the need for raw SQL, they require more upfront learning to use effectively.

Model State Management

Dapper: Does not track model changes.

Dapper does not handle model state management or change tracking. It simply maps query results to objects and relies on the developer to manage any updates or changes explicitly. This lightweight approach avoids overhead but requires manual handling of data consistency and persistence.

Entity Framework:Tracks changes and can automatically persist them.

Entity Framework automatically tracks changes to models through the DbContext. When you modify an entity, EF detects the changes and can automatically persist them to the database during a SaveChanges() call. This feature simplifies state management and reduces manual effort but adds overhead to query execution and memory usage.

SQL Injection Protection

Dapper: Uses parameterized queries.

Dapper mitigates SQL injection risks by using parameterized queries. When executing raw SQL or stored procedures, Dapper securely binds input parameters, ensuring they are properly escaped and treated as data rather than executable code. However, developers must ensure parameters are used consistently and avoid concatenating raw SQL strings manually.

Entity Framework: Automatically protects via LINQ and SQL interpolation.

Entity Framework provides built-in protection against SQL injection by default. LINQ queries are translated into parameterized SQL statements, and SQL interpolation methods, such as FromSqlInterpolated, automatically handle parameterization. This abstraction reduces the likelihood of injection vulnerabilities, even for developers less familiar with SQL security best practices.

Migration Support

Dapper: No built-in migration system.

Dapper does not include a built-in migration system for managing database schema changes. Developers need to handle migrations manually or use external tools like FluentMigrator or Flyway. This provides flexibility but requires more effort and discipline to track and apply schema updates consistently.

Entity Framework: Robust migration support for schema changes.

Entity Framework includes robust migration support built into its tooling. It allows developers to define schema changes in code, generate migration scripts, and apply them automatically. EF migrations simplify managing database versioning, especially in projects with frequent schema changes or multiple team members.

Strengths and Weaknesses

Dapper Strengths:

• Ideal for microservices or applications needing high-performance data retrieval.
• Works seamlessly with stored procedures and raw SQL.
• Lightweight and easy to integrate into existing projects.

Dapper Weaknesses:

• Lacks features like automated migrations and change tracking.
• Requires more boilerplate code for CRUD operations.

Entity Framework Strengths:

• Simplifies complex data manipulations with LINQ.
• Integrated with migrations and database schema management.
• Excellent for applications with many related entities and relationships.

Entity Framework Weaknesses:

• Higher memory and CPU overhead.
• Can lead to “hidden” SQL calls due to its abstraction.

Dapper and Entity Framework each have their place in the .NET developer’s toolbox. Dapper is ideal for developers who prioritize performance, simplicity, and direct control over database queries. Conversely, Entity Framework shines in scenarios where data modeling, complex relationships, and built-in migrations are critical. By understanding the trade-offs and strengths of each tool, you can select the right approach for your project’s unique requirements. For read-heavy or performance-critical applications, Dapper is a strong contender. For feature-rich, maintainable solutions, Entity Framework is the better choice.