Learning Dapper: A Lightweight ORM for .NET

17 Jul 2025

Dapper is a simple object mapper for .NET, often referred to as a "micro-ORM." It’s known for its high performance and minimal overhead, making it a popular choice for applications that need direct control over SQL queries while still benefiting from object mapping.

1. Setup
2. Querying Data with Dapper
3. Parameter Handling with Dapper
4. Pagination with Dapper
5. Conclusion

1. Setup

To get started with Dapper, a simple console application can be set up. This involves creating a new project, adding the necessary NuGet packages, defining entity classes, and preparing a database schema with some seed data.

First, create a new console project and navigate into its directory:

dotnet new console -o Learning.Dapper -f net8.0
cd Learning.Dapper/

Next, add the required NuGet packages:

dotnet add package Dapper --version 2.1.66
dotnet add package Microsoft.Data.Sqlite --version 9.0.7
dotnet add package MiniProfiler.AspNetCore --version 4.5.4

1.1. Entity Classes

Two simple entity classes, Category and Product, are defined to map to the database tables.

Learning.Dapper/Entities/Category.cs

namespace Learning.Dapper.Entities;

public class Category
{
    public int Id { get; set; }

    public required string Name { get; set; }
}

Learning.Dapper/Entities/Product.cs

namespace Learning.Dapper.Entities;

public class Product
{
    public int Id { get; set; }

    public string? Name { get; set; }

    public string? Description { get; set; }

    public decimal Price { get; set; }

    public int Stock { get; set; }

    public int CategoryId { get; set; }

    public ProductStatus Status { get; set; }

    public ProductCondition Condition { get; set; }
}

public enum ProductStatus
{
    Available,
    OutOfStock,
    Discontinued
}

public enum ProductCondition
{
    New,
    Used,
    Refurbished
}

1.2. MiniProfiler Integration

MiniProfiler is a simple yet effective mini-profiler for .NET applications. It helps in identifying performance bottlenecks by providing detailed timing information for various operations, including database queries.

To integrate MiniProfiler with Dapper, database connections are wrapped with ProfiledDbConnection. This allows MiniProfiler to automatically capture and display SQL execution times.

Learning.Dapper/Program.cs (MiniProfiler Setup)

using Dapper;
using Microsoft.Data.Sqlite;
using StackExchange.Profiling;
using StackExchange.Profiling.Data;

// Initialize MiniProfiler.
var profiler = MiniProfiler.StartNew("Learn Dapper");  // MiniProfiler.Current is the profiler now.

// Use in-memory SQLite database.
using var sqliteConnection = new SqliteConnection("Data Source=:memory:");

// Wrap connection with ProfiledDbConnection for Dapper (i.e., ADO.NET) profiling.
using var conn = new ProfiledDbConnection(sqliteConnection, profiler);

conn.Open();

Learning.Dapper/Program.cs (MiniProfiler Setup)

/// Recursively prints the timing information collected by MiniProfiler,
/// including custom SQL timings for each step.
static async void PrintTimings(IEnumerable<Timing> timings, string indent = "")
{
    foreach (var timing in timings)
    {
        // Display the name and duration of the current timing step.
        Console.WriteLine($"{indent}{timing.Name} - {timing.DurationMilliseconds} ms");

        // If custom timings (e.g., SQL queries) exist for this step, print them.
        if (timing.CustomTimings != null)
        {
            foreach (KeyValuePair<string, List<CustomTiming>> pair in timing.CustomTimings)
            {
                foreach (CustomTiming customTiming in pair.Value)
                {
                    if (customTiming.CommandString is not null)
                    {
                        string heading = $"{indent}  [SQL]:";
                        Console.WriteLine(heading);
                        string prefix = string.Join("", Enumerable.Repeat(" ", heading.Length));
                        using var reader = new StringReader(customTiming.CommandString);
                        string? line = await reader.ReadLineAsync();
                        while (line != null)
                        {
                            Console.WriteLine($"{prefix} {line}");
                            line = await reader.ReadLineAsync();
                        }
                    }
                }
            }
        }

        // Recursively print child timings to maintain the hierarchy.
        if (timing.Children != null && timing.Children.Count > 0)
        {
            PrintTimings(timing.Children, indent + "  ");
        }
    }
}

1.3. Database Setup and Data Seeding

An in-memory SQLite database is used for simplicity. The Program.cs file handles the database connection, schema creation, and data seeding.

Learning.Dapper/Program.cs

using (profiler.Step("INIT SCHEMA"))
{
    var schemaSql = await File.ReadAllTextAsync(@"sql/schema.sql");
    await conn.ExecuteAsync(schemaSql);
}

using (profiler.Step("SEED DATA"))
{
    var seedSql = await File.ReadAllTextAsync(@"sql/seed.sql");
    await conn.ExecuteAsync(seedSql);
}

// Stop MiniProfiler session.
await profiler!.StopAsync();

// Print profiling timings.
PrintTimings([profiler.Root]);

The sql directory and the schema.sql and seed.sql files also need to be created within the Learning.Dapper project.

Learning.Dapper/sql/schema.sql

CREATE TABLE IF NOT EXISTS Categories (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS Products (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT,
    Description TEXT,
    Price REAL NOT NULL,
    Stock INTEGER NOT NULL,
    CategoryId INTEGER NOT NULL,
    Status INTEGER NOT NULL, -- Mapped from ProductStatus enum
    Condition INTEGER NOT NULL, -- Mapped from ProductCondition enum
    FOREIGN KEY (CategoryId) REFERENCES Categories(Id)
);

Learning.Dapper/sql/seed.sql

INSERT INTO Categories (Name) VALUES ('Electronics');
INSERT INTO Categories (Name) VALUES ('Books');
INSERT INTO Categories (Name) VALUES ('Home & Kitchen');
INSERT INTO Categories (Name) VALUES ('Sports & Outdoors');

INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('Laptop', 'Powerful laptop for work and gaming', 1200.00, 50, 1, 0, 0);
INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('Smartphone', 'Latest model smartphone with advanced features', 800.00, 150, 1, 0, 0);
INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('Headphones', 'Noise-cancelling over-ear headphones', 150.00, 100, 1, 0, 0);
INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('Smart TV', '4K UHD Smart TV with HDR', 750.00, 30, 1, 0, 0);

INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('The Hitchhiker''s Guide to the Galaxy', 'A comedic science fiction series', 15.50, 200, 2, 0, 0);
INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('1984', 'Dystopian social science fiction novel', 12.00, 180, 2, 0, 0);
INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('To Kill a Mockingbird', 'Classic American novel', 10.00, 250, 2, 0, 0);

INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('Coffee Maker', 'Automatic drip coffee maker', 45.00, 70, 3, 0, 0);
INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('Blender', 'High-speed professional blender', 90.00, 60, 3, 0, 0);
INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('Toaster', '2-slice stainless steel toaster', 30.00, 90, 3, 0, 0);

INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('Yoga Mat', 'Non-slip yoga mat for all types of yoga', 25.00, 120, 4, 0, 0);
INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('Dumbbell Set', 'Adjustable dumbbell set (5-25 lbs)', 100.00, 40, 4, 0, 0);
INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('Camping Tent', 'Lightweight 2-person camping tent', 120.00, 25, 4, 0, 0);

1.4. Console Output

After running dotnet run in the Learning.Dapper directory, output similar to this should appear, indicating the schema initialization and data seeding were successful:

Learn Dapper - 189.45 ms
  [SQL]:
         Connection Open()
  INIT SCHEMA - 103.35 ms
    [SQL]:
           CREATE TABLE IF NOT EXISTS Categories (
               Id INTEGER PRIMARY KEY AUTOINCREMENT,
               Name TEXT NOT NULL
           );

           CREATE TABLE IF NOT EXISTS Products (
               Id INTEGER PRIMARY KEY AUTOINCREMENT,
               Name TEXT NOT NULL,
               Description TEXT NULL,
               Price REAL NOT NULL,
               Stock INTEGER NOT NULL,
               CategoryId INTEGER NOT NULL,
               Status INTEGER NOT NULL,
               CONDITION TEXT NOT NULL,
               FOREIGN KEY (CategoryId) REFERENCES Categories (Id)
           );
  SEED DATA - 2.97 ms
    [SQL]:
           INSERT INTO Categories (Name) VALUES ('Electronics');
           INSERT INTO Categories (Name) VALUES ('Books');
           INSERT INTO Categories (Name) VALUES ('Home & Kitchen');
           INSERT INTO Categories (Name) VALUES ('Sports & Outdoors');

           INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('Laptop', 'Powerful laptop for work and gaming', 1200.00, 50, 1, 0, 'New');
           INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('Smartphone', 'Latest model smartphone with advanced features', 800.00, 150, 1, 0, 'New');
           INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('Headphones', 'Noise-cancelling over-ear headphones', 150.00, 100, 1, 0, 'New');
           INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('Smart TV', '4K UHD Smart TV with HDR', 750.00, 30, 1, 0, 'New');

           INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('The Hitchhiker''s Guide to the Galaxy', 'A comedic science fiction series', 15.50, 200, 2, 0, 'New');
           INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('1984', 'Dystopian social science fiction novel', 12.00, 180, 2, 0, 'New');
           INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('To Kill a Mockingbird', 'Classic American novel', 10.00, 250, 2, 0, 'New');

           INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('Coffee Maker', 'Automatic drip coffee maker', 45.00, 70, 3, 0, 'New');
           INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('Blender', 'High-speed professional blender', 90.00, 60, 3, 0, 'New');
           INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('Toaster', '2-slice stainless steel toaster', 30.00, 90, 3, 0, 'New');

           INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('Yoga Mat', 'Non-slip yoga mat for all types of yoga', 25.00, 120, 4, 0, 'New');
           INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('Dumbbell Set', 'Adjustable dumbbell set (5-25 lbs)', 100.00, 40, 4, 0, 'New');
           INSERT INTO Products (Name, Description, Price, Stock, CategoryId, Status, Condition) VALUES ('Camping Tent', 'Lightweight 2-person camping tent', 120.00, 25, 4, 0, 'New');

2. Querying Data with Dapper

Dapper provides a rich set of methods for querying data, from single scalar values to multiple result sets. Let’s explore some of the most commonly used querying methods.

2.1. Querying Scalar Values

Dapper provides methods to retrieve a single value (a scalar) from a database query. This is useful for operations like getting a count, sum, or any single piece of data.

The primary method for querying scalar values is:

ExecuteScalarAsync<T>(): Executes a command and returns the first column of the first row in the result set returned by the query as a T type. Additional columns or rows are ignored. This is typically used for SELECT COUNT(*), SELECT MAX(Id), etc.

using (profiler.Step("Dapper Query Scalar Values"))
{
    // Get the total count of products
    var productCount = await conn.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM Products");
    Console.WriteLine($"Total Products: {productCount}");

    // Get the maximum product price
    var maxPrice = await conn.ExecuteScalarAsync<decimal>("SELECT MAX(Price) FROM Products");
    Console.WriteLine($"Maximum Product Price: {maxPrice:C}");

    // Get the name of a specific category (e.g., CategoryId = 1)
    var categoryName = await conn.ExecuteScalarAsync<string>("SELECT Name FROM Categories WHERE Id = @Id", new { Id = 1 });
    Console.WriteLine($"Category Name for Id 1: {categoryName}");

    // Attempt to get a non-existent category name (will return null/default)
    var nonExistentCategoryName = await conn.ExecuteScalarAsync<string>("SELECT Name FROM Categories WHERE Id = @Id", new { Id = 999 });
    Console.WriteLine($"Category Name for Id 999: {nonExistentCategoryName ?? "Not Found"}");
}

2.2. Querying Single Row

When expecting a query to return at most one row, Dapper provides several convenient methods to map that single row to a C# object:

QueryFirstOrDefaultAsync<T>(): Executes a query and maps the first row to a T type. If no rows are returned, it returns default(T). If multiple rows are returned, it still returns only the first one without throwing an exception. This is generally the safest option when you’re unsure if a row will be found.
QuerySingleOrDefaultAsync<T>(): Executes a query and maps exactly one row to a T type. If no rows are returned, it returns default(T). If more than one row is returned, it throws an InvalidOperationException. Use this when you strictly expect zero or one result.
QueryFirstAsync<T>(): Executes a query and maps the first row to a T type. If no rows are returned, it throws an InvalidOperationException. If multiple rows are returned, it still returns only the first one. Use this when you strictly expect at least one result.
QuerySingleAsync<T>(): Executes a query and maps exactly one row to a T type. If no rows are returned or if more than one row is returned, it throws an InvalidOperationException. Use this when you strictly expect exactly one result.

Let’s see these in action:

using (profiler.Step("Dapper Query Single Row"))
{
    // Query a product by Id using QueryFirstOrDefaultAsync
    var product1 = await conn.QueryFirstOrDefaultAsync<Product>("SELECT * FROM Products WHERE Id = @Id", new { Id = 1 });
    Console.WriteLine($"Product 1 (QueryFirstOrDefault): {product1?.Name ?? "Not Found"}");

    // Query a non-existent product using QueryFirstOrDefaultAsync
    var product999 = await conn.QueryFirstOrDefaultAsync<Product>("SELECT * FROM Products WHERE Id = @Id", new { Id = 999 });
    Console.WriteLine($"Product 999 (QueryFirstOrDefault): {product999?.Name ?? "Not Found"}");

    // Query a product by Id using QuerySingleOrDefaultAsync (expecting 0 or 1 result)
    var product2 = await conn.QuerySingleOrDefaultAsync<Product>("SELECT * FROM Products WHERE Id = @Id", new { Id = 2 });
    Console.WriteLine($"Product 2 (QuerySingleOrDefault): {product2?.Name ?? "Not Found"}");

    // Example of QuerySingleAsync (expecting exactly one result)
    // This will throw an exception if the query returns zero or more than one result.
    try
    {
        var product3 = await conn.QuerySingleAsync<Product>("SELECT * FROM Products WHERE Id = @Id", new { Id = 3 });
        Console.WriteLine($"Product 3 (QuerySingle): {product3.Name}");
    }
    catch (InvalidOperationException ex)
    {
        Console.WriteLine($"Error QuerySingle (Product 3): {ex.Message}");
    }

    // Demonstrate QuerySingleOrDefaultAsync throwing an exception for multiple results
    try
    {
        // This query will return multiple products, causing QuerySingleOrDefaultAsync to throw
        var multipleProducts = await conn.QuerySingleOrDefaultAsync<Product>("SELECT * FROM Products WHERE CategoryId = @CategoryId", new { CategoryId = 1 });
        Console.WriteLine($"Multiple Products (QuerySingleOrDefault): {multipleProducts?.Name ?? "Not Found"}");
    }
    catch (InvalidOperationException ex)
    {
        Console.WriteLine($"Error QuerySingleOrDefault (Multiple Products): {ex.Message}");
    }
}

Querying Single Values vs. Single Rows

While QueryFirstAsync, QueryFirstOrDefaultAsync, QuerySingleAsync, and QuerySingleOrDefaultAsync can technically retrieve a single value if the SQL query selects only one column, ExecuteScalarAsync<T>() is the preferred and most semantically appropriate method for such scenarios.

ExecuteScalarAsync<T>() is optimized for retrieving a single, scalar value (e.g., COUNT(*), MAX(Price)). It efficiently fetches only the first column of the first row and ignores all other data, even if the SQL query selects multiple columns.
The QueryXAsync methods are primarily designed for mapping a single row of data to a C# object. When used to retrieve a single column (or when mapping to a primitive type from a multi-column query), they also extract the value from the first column of the first row. However, this usage can lead to data loss if other columns are intended to be used.

For clarity, performance, and to express intent, use ExecuteScalarAsync<T>() when expecting a single value, and the QueryXAsync methods when mapping a single row to a structured object. If a QueryXAsync method is used with a multi-column query and mapped to a primitive type, only the first column’s value will be retrieved.

using (profiler.Step("Querying Single Values vs. Single Rows Example"))
{
    // SQL query selecting multiple columns
    var sqlMultiColumn = "SELECT Price, Name, Description FROM Products ORDER BY Id LIMIT 1;";

    // Using ExecuteScalarAsync<T> with a multi-column query:
    // Only the first column (Price) will be retrieved.
    var priceScalar = await conn.ExecuteScalarAsync<decimal>(sqlMultiColumn);
    Console.WriteLine($"ExecuteScalarAsync<decimal> (multi-column, first column): {priceScalar:C}");

    // Using QueryFirstAsync<T> with a multi-column query mapped to a primitive type:
    // Only the first column (Price) will be retrieved.
    var priceQueryFirst = await conn.QueryFirstAsync<decimal>(sqlMultiColumn);
    Console.WriteLine($"QueryFirstAsync<decimal> (multi-column, first column): {priceQueryFirst:C}");

    // Using QuerySingleAsync<T> with a multi-column query mapped to a primitive type:
    // Only the first column (Price) will be retrieved. This will throw an exception if more than one row is returned.
    try
    {
        var priceQuerySingle = await conn.QuerySingleAsync<decimal>(sqlMultiColumn);
        Console.WriteLine($"QuerySingleAsync<decimal> (multi-column, first column): {priceQuerySingle:C}");
    }
    catch (InvalidOperationException ex)
    {
        Console.WriteLine($"Error QuerySingleAsync<decimal> (multi-column): {ex.Message}");
    }

    // Using QueryFirstAsync<T> with a multi-column query mapped to an object:
    // All columns will be mapped to the Product object.
    var productObject = await conn.QueryFirstAsync<Product>(sqlMultiColumn);
    Console.WriteLine($"QueryFirstAsync<Product> (multi-column): {productObject.Name}, Price: {productObject.Price:C}, Description: {productObject.Description}");
}

2.3. Querying Multiple Rows

When a query is expected to return multiple rows, Dapper’s QueryAsync<T>() method is used. This method executes the SQL query and maps each returned row to an instance of the specified C# type T. The results are returned as an IEnumerable<T>, allowing for flexible iteration and manipulation.

using (profiler.Step("Dapper Query Multiple Rows"))
{
    // Query all products
    var allProducts = await conn.QueryAsync<Product>("SELECT * FROM Products");
    Console.WriteLine("\n--- All Products ---");
    foreach (var product in allProducts)
    {
        Console.WriteLine($"- {product.Name} (Price: {product.Price:C}, Stock: {product.Stock})");
    }

    // Query products by CategoryId
    var electronicsProducts = await conn.QueryAsync<Product>("SELECT * FROM Products WHERE CategoryId = @CategoryId", new { CategoryId = 1 });
    Console.WriteLine("\n--- Electronics Products ---");
    foreach (var product in electronicsProducts)
    {
        Console.WriteLine($"- {product.Name}");
    }

    // Query products with a price greater than a certain value
    var expensiveProducts = await conn.QueryAsync<Product>("SELECT * FROM Products WHERE Price > @MinPrice", new { MinPrice = 100.00m });
    Console.WriteLine("\n--- Expensive Products (> $100) ---");
    foreach (var product in expensiveProducts)
    {
        Console.WriteLine($"- {product.Name} (Price: {product.Price:C})");
    }
}

In these examples:

QueryAsync<Product>("SELECT * FROM Products") retrieves all products and maps them to a collection of Product objects.
Parameterized queries, such as SELECT * FROM Products WHERE CategoryId = @CategoryId, are used to filter results. Dapper automatically handles parameter mapping, preventing SQL injection vulnerabilities.
The results are iterated over using a foreach loop, demonstrating how to access the mapped C# objects.

2.4. Querying Multiple Results

Dapper’s QueryMultipleAsync method allows executing multiple SQL queries in a single round trip to the database and mapping them to different object types. This is highly efficient for related data.

This feature relies on the underlying database driver’s ability to return multiple result sets from a single command execution. Many relational database management systems (RDBMS) support this, including SQL Server, MySQL, and SQLite (as demonstrated here). It is particularly useful for scenarios where fetching related data from different tables might not be easily joined, or when optimizing network round trips.

When using QueryMultipleAsync, Dapper returns an SqlMapper.GridReader object, which acts as a cursor over the multiple result sets. Each result set must be read in the order returned by the SQL query.

The GridReader provides several Read and ReadAsync methods to consume the results:

ReadAsync<T>(): Reads all rows from the current result set and maps them to a collection of type T.
ReadFirstAsync<T>(): Reads the first row from the current result set and maps it to type T. Throws an exception if no rows are returned.
ReadFirstOrDefaultAsync<T>(): Reads the first row from the current result set and maps it to type T. Returns default(T) if no rows are returned.
ReadSingleAsync<T>(): Reads exactly one row from the current result set and maps it to type T. Throws an exception if no rows or more than one row are returned.
ReadSingleOrDefaultAsync<T>(): Reads exactly one row from the current result set and maps it to type T. Returns default(T) if no rows are returned, throws if more than one row.

using (profiler.Step("Dapper Query Multiple Results"))
{
    using (var multi = await conn.QueryMultipleAsync("SELECT Id, Name, Description, Price, Stock, CategoryId, Status, Condition FROM Products; SELECT Id, Name FROM Categories;"))
    {
        // Read the first result set (Products)
        var products = await multi.ReadAsync<Product>();

        // Read the second result set (Categories)
        var categories = await multi.ReadAsync<Category>();

        Console.WriteLine("\n--- All Products (from multi-query) ---");
        foreach (var product in products)
        {
            Console.WriteLine($"- {product.Name} (Price: {product.Price:C}, CategoryId: {product.CategoryId})");
        }

        Console.WriteLine("\n--- All Categories (from multi-query) ---");
        foreach (var category in categories)
        {
            Console.WriteLine($"- {category.Name} (Id: {category.Id})");
        }
    }
}

3. Parameter Handling with Dapper

Dapper excels at mapping query parameters to C# objects. Let’s look at how to handle different types of parameters, including enums and IN clauses.

3.1. Anonymous Parameters

Dapper supports passing parameters to SQL queries using anonymous objects. This is a common and convenient way to provide values for placeholders in SQL statements. Dapper automatically maps properties of the anonymous object to parameters in the query.

using (profiler.Step("Dapper Anonymous Parameters"))
{
    // Single parameter
    var product = await conn.QueryFirstOrDefaultAsync<Product>("SELECT * FROM Products WHERE Id = @ProductId", new { ProductId = 1 });
    Console.WriteLine($"Product (Anonymous Parameter): {product?.Name ?? "Not Found"}");

    // Multiple parameters
    var products = await conn.QueryAsync<Product>("SELECT * FROM Products WHERE CategoryId = @CategoryId AND Stock > @MinStock", new { CategoryId = 1, MinStock = 10 });
    Console.WriteLine("\n--- Products with Anonymous Parameters (CategoryId = 1, Stock > 10) ---");
    foreach (var p in products)
    {
        Console.WriteLine($"- {p.Name}");
    }

    // Parameter with a different name than the property (Dapper matches by name)
    var category = await conn.QueryFirstOrDefaultAsync<Category>("SELECT * FROM Categories WHERE Name = @CategoryName", new { CategoryName = "Books" });
    Console.WriteLine($"Category (Anonymous Parameter): {category?.Name ?? "Not Found"}");
}

In the example above:

An anonymous object { ProductId = 1 } is used to pass a single parameter.
An anonymous object { CategoryId = 1, MinStock = 10 } is used for multiple parameters.
Dapper matches the property names of the anonymous object (ProductId, CategoryId, MinStock, CategoryName) to the parameter placeholders in the SQL query (@ProductId, @CategoryId, @MinStock, @CategoryName).

This approach offers a concise syntax for parameterization, enhancing readability and preventing SQL injection by ensuring values are properly escaped.

When passing enum values as parameters, it is crucial to ensure they are converted to a type that the database understands (e.g., TEXT or INTEGER). If not handled correctly, Dapper might pass the integer representation of the enum, which could lead to unexpected results if the database expects a string.

using (profiler.Step("Dapper Enum Parameter"))
{
    // Example with ProductStatus enum
    // WRONG: INTEGER (if database expects string)
    await conn.QueryAsync("SELECT Id, Name FROM Products WHERE Status = @Status", new { Status = ProductStatus.Available });

    // GOOD: TEXT (if database expects string)
    await conn.QueryAsync("SELECT Id, Name FROM Products WHERE Status = @Status", new { Status = ProductStatus.OutOfStock.ToString() });
}

Explicitly converting the enum to a string using .ToString() ensures that the correct text value is passed to the database.

3.2. Dynamic Parameters

For more complex scenarios or when parameter names and values are determined at runtime, Dapper provides the DynamicParameters class. This class allows for programmatic construction of parameter collections.

using (profiler.Step("Dapper Dynamic Parameters"))
{
    // Single parameter
    // 1.
    //   var parameters = new DynamicParameters({ ProductId = 1 });
    // 2.
    //   var dictionary = new Dictionary<string, object>
    //   {
    //       { "@ProductId", 1 }
    //   };
    //   var parameters = new DynamicParameters(dictionary);
    // 3.
    //   var template = new Product { ProductId = 1 };
    //   var parameters = new DynamicParameters(template);
    // 4.
    //   var singleParam = new DynamicParameters();
    //   singleParam.Add("ProductId", 1);
    var singleParam = new DynamicParameters();
    singleParam.Add("@ProductId", 1);
    var product = await conn.QueryFirstOrDefaultAsync<Product>("SELECT * FROM Products WHERE Id = @ProductId", singleParam);
    Console.WriteLine($"Product (Dynamic Parameter): {product?.Name ?? "Not Found"}");

    // Multiple parameters
    var multipleParams = new DynamicParameters();
    multipleParams.Add("CategoryId", 1);
    multipleParams.Add("MinStock", 10);
    multipleParams.Add("Status", ProductStatus.Available); // Dapper handles enum to underlying type (int) conversion automatically
    multipleParams.Add("Condition", ProductCondition.New.ToString()); // Convert enum to string for database if column is TEXT
    var products = await conn.QueryAsync<Product>("SELECT * FROM Products WHERE CategoryId = @CategoryId AND Stock > @MinStock AND Status = @Status AND Condition = @Condition", multipleParams);
    Console.WriteLine("\n--- Products with Dynamic Parameters (CategoryId = 1, Stock > 10) ---");
    foreach (var p in products)
    {
        Console.WriteLine($"- {p.Name}");
    }
}

In this example:

An instance of DynamicParameters is created.
Parameters are added using the Add method, specifying the parameter name (including the optional @ prefix) and its value.
For multiple parameters, all parameters are added to the same DynamicParameters object.

DynamicParameters offers greater control over parameter types, directions (input, output, return value), and sizes, making it suitable for advanced scenarios like stored procedures with output parameters.

3.3. WHERE IN Parameters

Dapper simplifies handling WHERE IN clauses by allowing collections to be passed directly as parameters.

Dapper will automatically expand the collection into a list of parameters.

Method 1: Using an Anonymous Object (Recommended)

// C# Code
var ids = new[] { 1, 2, 3, 4, 5 };

var sql = "SELECT * FROM YourTable WHERE Id IN @Ids";

// Dapper will expand the 'ids' array automatically
var results = connection.Query<YourType>(sql, new { Ids = ids });

Dapper translates this into a parameterized query similar to:

-- SQL generated by Dapper (conceptual)
SELECT * FROM YourTable WHERE Id IN (@Ids1, @Ids2, @Ids3, @Ids4, @Ids5)

Method 2: Using DynamicParameters

The principle is the sam to build parameters dynamically by using DynamicParameters.
```
// C# Code
var ids = new[] { 1, 2, 3, 4, 5 };

var sql = "SELECT * FROM YourTable WHERE Id IN @Ids";

var parameters = new DynamicParameters();
parameters.Add("@Ids", ids);

var results = connection.Query<YourType>(sql, parameters);
```
In both cases, the key is to pass an IEnumerable<T> (like an array T[] or a List<T>) as the value for the parameter used in the IN clause. Dapper handles the rest.

using (profiler.Step("Dapper WHERE IN Parameters"))
{
    using (profiler.Step("Dapper Anonymous Parameters (IN)"))
    {
        var productNames = new string[] { "Laptop", "Smartphone" };
        var productsByName = await conn.QueryAsync<Product>("SELECT * FROM Products WHERE Name IN @ProductNames", new { ProductNames = productNames });
        Console.WriteLine("\n--- Products by Name (Laptop, Smartphone) ---");
        foreach (var p in productsByName)
        {
            Console.WriteLine($"- {p.Name}");
        }
    }

    using (profiler.Step("Dapper Dynamic Parameters (IN)"))
    {
        var parameters = new DynamicParameters();
        parameters.Add("@ProductIds", new int[] { 1, 3 });
        var productsById = await conn.QueryAsync<Product>("SELECT * FROM Products WHERE Id IN @ProductIds", parameters);
        Console.WriteLine("\n--- Products by Id (1, 3) ---");
        foreach (var p in productsById)
        {
            Console.WriteLine($"- {p.Name}");
        }
    }

    using (profiler.Step("Dapper Anonymous Parameters (Enum IN)"))
    {
        // WRONG: INTEGER (if database expects string)
        await conn.QueryAsync("SELECT * FROM Products WHERE Status IN @Statuses", new { Statuses = new ProductStatus[] { ProductStatus.Available, ProductStatus.Discontinued } });

        // GOOD: TEXT (if database expects string)
        var productStatuses = new string[] { ProductStatus.Available.ToString(), ProductStatus.OutOfStock.ToString() };
        var productsByStatus = await conn.QueryAsync<Product>("SELECT * FROM Products WHERE Status IN @Statuses", new { Statuses = productStatuses });
        Console.WriteLine("\n--- Products by Status (Available, OutOfStock) ---");
        foreach (var p in productsByStatus)
        {
            Console.WriteLine($"- {p.Name} (Status: {p.Status})");
        }
    }
}

Both anonymous objects and DynamicParameters can be used. For enum collections in IN clauses, the same principle applies: convert them to strings to match the database’s expected type.

  Dapper WHERE IN Parameters - 191.37 ms
    Dapper Anonymous Parameters (IN) - 169.38 ms
      [SQL]:
             SELECT * FROM Products WHERE Name IN ('Laptop', 'Smartphone')
    Dapper Dynamic Parameters (IN) - 10.87 ms
      [SQL]:
             SELECT * FROM Products WHERE Id IN (1, 3)
    Dapper Anonymous Parameters (Enum IN) - 10.95 ms
      [SQL]:
             SELECT * FROM Products WHERE Status IN (0, 2)
      [SQL]:
             SELECT * FROM Products WHERE Status IN ('Available', 'OutOfStock')

3.4. Best Practices for Optional Filters

When building queries with optional search criteria, DynamicParameters combined with conditional logic provides a clean and flexible approach. This avoids string concatenation, which can be error-prone and lead to SQL injection vulnerabilities.

using (profiler.Step("Best Practices for Optional Filters"))
{
    // Simulate a search request with optional parameters
    string? searchName = "Laptop"; // Can be null or empty
    decimal? minPrice = 500.00m; // Can be null
    ProductStatus? productStatus = ProductStatus.Available; // Can be null
    ProductCondition? productCondition = ProductCondition.New; // Can be null

    var sql = new StringBuilder("SELECT * FROM Products WHERE 1=1");
    var parameters = new DynamicParameters();

    if (!string.IsNullOrWhiteSpace(searchName))
    {
        sql.Append(" AND Name LIKE @Name");
        parameters.Add("@Name", $"%{searchName}%");
    }

    if (minPrice.HasValue)
    {
        sql.Append(" AND Price >= @MinPrice");
        parameters.Add("@MinPrice", minPrice.Value);
    }

    if (productStatus.HasValue)
    {
        sql.Append(" AND Status = @Status");
        parameters.Add("@Status", productStatus.Value.ToString()); // Convert enum to string
    }

    if (productCondition.HasValue)
    {
        sql.Append(" AND Condition = @Condition");
        parameters.Add("@Condition", productCondition.Value.ToString()); // Convert enum to string
    }

    var filteredProducts = await conn.QueryAsync<Product>(sql.ToString(), parameters);

    Console.WriteLine("\n--- Filtered Products ---");
    foreach (var product in filteredProducts)
    {
        Console.WriteLine($"- {product.Name} (Price: {product.Price:C}, Status: {product.Status}, Condition: {product.Condition})");
    }
}

In this example:

A base WHERE 1=1 clause is used to allow easy appending of AND conditions.
StringBuilder dynamically constructs the SQL query based on the presence of optional parameters.
DynamicParameters is used to add parameters conditionally, ensuring proper parameterization and preventing SQL injection.
Enum values are converted to strings before being added as parameters, assuming the database stores them as text.

4. Pagination with Dapper

Pagination is a common requirement for displaying large datasets. Dapper, combined with SQL’s LIMIT (or TOP in SQL Server) and OFFSET clauses, makes implementing pagination straightforward. It’s often efficient to retrieve both the paginated results and the total count in a single database call using QueryMultipleAsync.

To encapsulate pagination logic and optional filtering, request and result classes are defined:

Learning.Dapper/Paginator/PaginationRequest.cs

using System.ComponentModel.DataAnnotations;

namespace Learning.Dapper.Paginator;

public class PaginationRequest
{
    [Range(1, int.MaxValue, ErrorMessage = "{0} must be greater than or equal to {1}.")]
    public int? PageNumber { get; set; } // 1-based

    [Range(1, int.MaxValue, ErrorMessage = "{0} must be greater than or equal to {1}.")]
    public int? PageSize { get; set; }

    [MinLength(1, ErrorMessage = "At least one {0} is required.")]
    public IReadOnlyList<SortedField> SortFields { get; set; } = new List<SortedField>();

    public PaginationQuery AsQuery()
    {
        return new PaginationQuery(this);
    }
}

public class SortedField
{
    public string? Name { get; set; }

    public SortedDirection Direction { get; set; }
}

public enum SortedDirection
{
    Asc = 0,
    Desc = 1
}

Learning.Dapper/Paginator/PaginationQuery.cs

using System.Collections.Generic;

namespace Learning.Dapper.Paginator;

public class PaginationQuery
{
    public int PageNumber { get; }

    public int PageSize { get; }

    public int Offset { get; } // 0-based

    public int Limit { get; }

    public IReadOnlyList<SortedField> SortedFields { get; } = [];

    public PaginationQuery(PaginationRequest request)
    {
        PageNumber = request.PageNumber ?? 1;
        PageSize = request.PageSize ?? 10;
        Offset = (PageNumber - 1) * PageSize;
        Limit = PageSize;
        SortedFields = request.SortFields ?? new List<SortedField>();
    }

    public string AsOrderBy(IEnumerable<string>? allowedFields)
    {
        if (SortedFields == null || !SortedFields.Any())
        {
            return string.Empty;
        }

        var validSortFields = SortedFields.Where(f => allowedFields?.Contains(f.Name, StringComparer.OrdinalIgnoreCase));

        if (!validSortFields.Any())
        {
            return string.Empty;
        }

        var orderByClauses = validSortFields.Select(f => $"{f.Name} {f.Direction.ToString().ToUpper()}");
        return $"ORDER BY {string.Join(", ", orderByClauses)}";
    }
}

Learning.Dapper/Paginator/PaginationResult.cs

namespace Learning.Dapper.Paginator;

public class PaginationResult<T>
{
    public IEnumerable<T> Items { get; }

    public int TotalCount { get; }

    public int PageNumber { get; }

    public int PageSize { get; }

    public int TotalPages => (int)Math.Ceiling((double)TotalCount / PageSize);

    public bool HasPreviousPage => PageNumber > 1;

    public bool HasNextPage => PageNumber < TotalPages;

    public PaginationResult(IEnumerable<T> items, int totalCount, int pageNumber, int pageSize)
    {
        Items = items ?? new List<T>();
        TotalCount = totalCount;
        PageNumber = pageNumber;
        PageSize = pageSize;
    }
}

Learning.Dapper/Requests/ProductListRequest.cs

using Learning.Dapper.Paginator;

namespace Learning.Dapper.Models;

public sealed class ProductListRequest
{
    public string? Keyword { get; set; } // search for Id or name

    public decimal? MinPrice { get; set; }

    public ProductStatus? Status { get; set; }

    public ProductCondition? Condition { get; set; }

    public PaginationRequest Pagination { get; set; } = new PaginationRequest();
}

Integration into the Program.cs example demonstrates fetching paginated products with optional filters.

Learning.Dapper/Program.cs (Pagination Example)

using (profiler.Step("Pagination with Dapper"))
{
    var request = new ProductListRequest
    {
        Keyword = "phone",
        MinPrice = 10.0m,
        Status = ProductStatus.Available,
        Condition = ProductCondition.New,

        Pagination = new PaginationRequest
        {
            PageNumber = 1,
            PageSize = 10,
            SortFields = new List<SortedField>
            {
                new SortedField { Name = "Name", Direction = SortedDirection.Asc }
            }
        }
    };

    var paginationQuery = request.Pagination.AsQuery();
    var allowedSortFields = new List<string> { "Name", "Id" };
    var orderByClause = paginationQuery.AsOrderBy(allowedSortFields);
    var whereClause = new StringBuilder("WHERE 1=1");
    var parameters = new DynamicParameters();

    if (!string.IsNullOrEmpty(request.Keyword))
    {
        whereClause.AppendLine(" AND (Id LIKE @Keyword OR Name LIKE @Keyword)");
        parameters.Add("Keyword", $"%{request.Keyword}%");
    }
    if (request.MinPrice.HasValue)
    {
        whereClause.AppendLine(" AND Price >= @MinPrice");
        parameters.Add("MinPrice", request.MinPrice.Value);
    }
    if (request.Status.HasValue)
    {
        whereClause.AppendLine(" AND Status = @Status");
        parameters.Add("Status", request.Status.Value); // Cast enum to integer for database
    }
    if (request.Condition.HasValue)
    {
        whereClause.AppendLine(" AND Condition = @Condition");
        parameters.Add("Condition", $"{request.Condition.Value}"); // Cast enum to text for database
    }

    var sql = new StringBuilder();
    sql.AppendLine("SELECT *");
    sql.AppendLine("FROM Products");
    sql.AppendLine(whereClause);
    sql.AppendLine(orderByClause!); // orderByClause may be NULL here!!!
    sql.AppendLine($"LIMIT {paginationQuery.Limit}");
    sql.AppendLine($"OFFSET {paginationQuery.Offset};");

    sql.AppendLine("SELECT COUNT(*)");
    sql.AppendLine("FROM Products");
    sql.AppendLine(whereClause);
    sql.AppendLine(";");

    using var multi = await conn.QueryMultipleAsync(sql.ToString(), parameters);

    // Read the first result set into a list of Product entities.
    var products = multi.Read<Product>().ToList();

    // Read the second result set, which is the total count of products.
    var totalCount = multi.Read<int>().Single();

    // Create a PaginationResult object to encapsulate the paginated data and metadata.
    var paginationResult = new PaginationResult<Product>(products, totalCount, paginationQuery.PageNumber, paginationQuery.PageSize);

    Console.WriteLine($"\n--- Paginated Products (Page {paginationResult.PageNumber} of {paginationResult.TotalPages}, Total: {paginationResult.TotalCount}) ---");
    Console.WriteLine($"Page Number: {paginationResult.PageNumber}");
    Console.WriteLine($"Page Size: {paginationResult.PageSize}");
    Console.WriteLine($"Total Pages: {paginationResult.TotalPages}");
    Console.WriteLine($"Has Previous Page: {paginationResult.HasPreviousPage}");
    Console.WriteLine($"Has Next Page: {paginationResult.HasNextPage}");

    foreach (var product in paginationResult.Items)
    {
        Console.WriteLine($"- {product.Name} (Price: {product.Price:C}, Status: {product.Status}, Condition: {product.Condition})");
    }
}

5. Conclusion

Dapper offers a powerful yet lightweight solution for interacting with databases in .NET applications. Its high performance and minimal overhead make it an excellent choice for scenarios where fine-grained control over SQL and efficient data access are critical. This document has demonstrated Dapper’s versatility in handling various querying methods, from scalar values to multiple result sets, and its robust parameter handling capabilities, including anonymous and dynamic parameters, as well as support for 'IN' clauses. Furthermore, the implementation of optional filters and comprehensive pagination logic showcases Dapper’s flexibility in building efficient and scalable data access layers.