How to use advanced Dapper features in ASP.NET Core

The Dapper ORM (object-relational mapper) has gained widespread popularity for working with databases in .NET because of its high speed and simplicity. We learned the basics of working with Dapper in a previous article here. We also discussed working with the Dapper Extensions library in an earlier article. In this article, we’ll take a look at some advanced features of Dapper.

To use the code examples provided in this article, you should have Visual Studio 2022 installed in your system. If you don’t already have a copy, you can download Visual Studio 2022 here.

Create an ASP.NET Core Web API project in Visual Studio 2022

First off, let’s create an ASP.NET Core 7 project in Visual Studio 2022. Follow these steps:

1. Launch the Visual Studio 2022 IDE.
2. Click on “Create new project.”
3. In the “Create new project” window, select “ASP.NET Core Web API” from the list of templates displayed.
4. Click Next.
5. In the “Configure your new project” window, specify the name and location for the new project.
6. Optionally check the “Place solution and project in the same directory” check box, depending on your preferences.
7. Click Next.
8. In the “Additional Information” window shown next, leave the “Use controllers (uncheck to use minimal APIs)” box checked. We won’t be using minimal APIs in this project. Leave the “Authentication Type” set to “None” (the default).
9. Ensure that the check boxes “Enable Open API Support,” “Configure for HTTPS,” and “Enable Docker” remain unchecked. We won’t be using those features here.
10. Click Create.

We’ll use this ASP.NET Core 7 Web API project to work with advanced features of Dapper in the sections below.

What is Dapper? Why use it?

Object-relational mappers have been used for many years to address the “impedance mismatch” between object models in programming languages and data models in relational databases. The Stack Overflow team created Dapper to be a simple ORM for .NET. Dapper is an open source project on GitHub.

Dapper is a lightweight, high performance, micro-ORM framework for .NET and .NET Core that supports a wide range of databases. These include Microsoft SQL Server, Oracle Database, MySQL, PostgreSQL, SQLite, and SQL CE. Compared to other ORMs, Dapper is lightweight, fast, and simple, designed with performance and usability in mind.

Advanced features in Dapper

In this section, we’ll examine some of the advanced features of Dapper with relevant code examples.

Multi-mapping

Dapper's multi-mapping functionality allows you to map query results to multiple objects. This type of query can be used to get relevant data from numerous tables with a single query. By passing a delegate function as an argument to the Query method or QueryAsync method, you can instruct Dapper to map the results of a query to different objects. Consider the following two classes.

public class Author
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}
public class Book
{
    public int Id { get; set; }
    public int AuthorId { get; set; }
    public string Title { get; set; }
    public string ISBN { get; set; }
}

The following code snippet illustrates how you can implement multi-mapping using Dapper.

string connectionString = "Specify the connection string to connect to your database...";
using (var connection = new SqlConnection(connectionString))
{
    string query = "SELECT * from Authors A Inner Join Books B ON A.Id = B.AuthorId";
    var authors = connection.Query<Author, Book, Author>(
                query,
                (author, book) =>
                {
                    author.Books = author.Books ?? new List<Book>();
                    author.Books.Add(book);
                },
            splitOn: "Id"
        ).Distinct().ToList();
}

Broad database support

Dapper provides support for connecting to and working with multiple types of databases such as SQL Server, Oracle Database, MySQL, and PostgreSQL. The following code snippets show how you can connect to these different databases using Dapper.

var connection = new SqlConnection(connectionString); //Connecting to SQL Server database

var connection = new OracleConnection(connectionString); //Connecting to Oracle database

var connection = new MySqlConnection(connectionString); //Connecting to MySQL database

var connection = new NpgsqlConnection(connectionString); //Connecting to PostgreSQL database

Bulk insert

Dapper provides bulk insert operations that can significantly boost performance when you’re trying to insert large volumes of data into your database. The SqlBulkCopy class in ADO.NET enables efficient data transfer between a source and a database. In Dapper, you can perform bulk inserts by passing an enumerable collection of objects to SqlMapperExtensions.Insert.

The following code example shows how you can perform bulk inserts using Dapper in your ASP.NET Core 7 application.

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    var orders = GetOrders();
    var transaction = connection.BeginTransaction();
    try
    {
        connection.Execute("INSERT INTO Orders (Id, ProductId, Amount)
        Values (@Id, @ProductId, @Amount)", orders);
        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
  return;
}

Stored procedures

You can use the Query, QueryFirstOrDefault, Execute, and similar methods to invoke stored procedures in Dapper. Dapper also supports interaction with the output parameters and return values of stored procedures through the DynamicParameters class, which simplifies how you can work with parameters.

The code example given below shows how you can work with stored procedures using Dapper in your ASP.NET Core 7 application.

using (var connection = new SqlConnection(connectionString))
{
    var parameters = new DynamicParameters();
    int customerId = 27;
    string orderStatus = "Pending";
    parameters.Add("@CustomerId", customerId, DbType.Int32);
    parameters.Add("@OrderStatus", orderStatus, DbType.String);
    var orders = connection.Query<Order>("GetOrdersByStatus", parameters,
        commandType: CommandType.StoredProcedure);
    // Write your code here to process the retrieved orders
    return orders.ToList();
}

Query pagination

When dealing with large result sets, pagination is critical to improving performance and efficiency. The Skip and Take methods in Dapper can be used to perform query pagination. By combining these methods with the SQL clauses OFFSET and FETCH NEXT, you can efficiently retrieve a subset of data from the database.

Below is a code example that illustrates how query pagination can be used in Dapper.

string connectionString = "Specify the connection string to connect to your database...";
using (var connection = new SqlConnection(connectionString))
{
    var pageNumber = 1;
    var pageSize = 10;
    var offset = (pageNumber - 1) * pageSize;
    var query = @"
        SELECT *
        FROM Orders
        ORDER BY OrderId
        OFFSET @Offset ROWS
        FETCH NEXT @PageSize ROWS ONLY";
    var parameters = new
    {
        Offset = offset,
        PageSize = pageSize
    };
    return connection.Query<Order>(query, parameters).ToList();
}

Query caching

This is a great feature in Dapper that improves performance considerably, especially if queries are run frequently. You can cache queries and results in Dapper. When you execute the query for the first time, the results upon execution of the query are stored in the cache. Whenever the same query is executed again and the data is available in the cache, the cached data is returned instead of retrieving the data from an API call or a database.

The following code example shows how query caching can be used.

var sql = "SELECT * FROM Authors WHERE AuthorId = @AuthorId";
var author = connection.QueryFirstOrDefault<Author>(sql, new { AuthorId = authorId });
// When the query is executed the next time, it will use the cached result
var cachedAuthor = connection.QueryFirstOrDefault<Author>(sql, new { AuthorId = authorId });

Dynamic parameters

Dapper allows parameter values to be passed to queries using anonymous types or dictionaries. This feature is useful when the number of parameters or their values are unknown at compile time. The following code snippet illustrates how you can use this feature in Dapper.

using (var connection = new SqlConnection(connectionString))
{
    var parameters = new { CustomerId = 1, OrderStatus = "Shipped" };
    var query = "Select * From Orders Where CustomerId = @CustomerId AND Status = @OrderStatus";
    var orders = connection.Query<Order>(query, parameters).ToList();
    // Write your code here to process the retrieved orders
    return orders.ToList();
}

Dapper is a lightweight and customizable ORM focusing on performance and simplicity. Dapper's sophisticated features can significantly improve data access capabilities and enable developers to interact more effectively with databases in ASP.NET Core applications.

Although Dapper has fairly rich functionality, it does not provide all of the features of more powerful ORMs such as Entity Framework. Therefore, it is essential to analyze your application’s requirements and choose the suitable data access technology.