In the world of .NET programming, developing reliable applications requires effective data access. Developers frequently seek frameworks and libraries that simplify database operations while preserving flexibility and performance. Dapper relationship mapping in .NET is one such area where Dapper, a lightweight ORM (Object Relational Mapper), excels. The main reason for its popularity is its speed and straightforward implementation.
Dapper implements the IDbConnection interface and comes with its methods to send SQL queries to Database and map with C# code. While SQL offers powerful capabilities for interacting with databases, using it directly in application code can introduce complexities and inefficiencies, especially as applications grow in complexity.
This is where tools like Dapper shine, offering an efficient way to handle relationship mapping in .NET applications.
In this article, we’ll cover all types of mappings that we can do using Dapper:
- Simple queries
- One to one relationships
- One to many relationships
- Many to many relationships
Whether you are working with simple queries or complex relationship mapping, Dapper’s flexibility makes it a great choice. For professional .NET development, including Dapper integration, you can hire a .NET developer from Vivasoft Limited, where skilled developers can deliver optimized, high-performance solutions.
How Dapper Works
To implement and use Dapper, first we need to consider doing three things:
- Create an IDbConnection interface
- Write the SQL query
- Calling the method that Dapper exposes
First, let’s start with a simple mapping:
public class Product
{
public int Id { get; set; }
public string? Name { get; set; }
public decimal Price { get; set; }
public int Quantity { get; set; }
public int OrderId { get; set; }
}
We have defined a Product model. We will write a simple SQL query that fetches all the products for a particular order. The query will look like this:
SELECT Id, Name, Price, Quantity, OrderId
FROM Product
WHERE OrderId = @OrderId
Here, we are providing the OrderId value as a parameter. Using this method is a Dapper convention to prevent SQL injection attacks.
In this case, the mapping will be pretty much straight forward because we are returning a single type from the database, there is no join query.
To execute this query using Dapper, we invoke the QueryAsync method and specify the return type as Product. We also have to pass the arguments: the SQL query and OrderId parameter.
SqlConnection connection = new SqlConnection(AppConstants.ConnectionString);
const string query = @"SELECT Id, Name, Price, Quantity, OrderId
FROM Product
WHERE OrderId = @OrderId";
int orderId = 5;
var results = await connection.QueryAsync<Product>(query, new { OrderId = orderId });
One to One Relationship Mapping
Let’s consider a scenario where we need to fetch both product details and order details for a specific order. So that means now we have to return two different types from the database using a single select query.
public class Product
{
public int Id { get; set; }
public string? Name { get; set; }
public decimal Price { get; set; }
public int Quantity { get; set; }
public int OrderId { get; set; }
}
public class Order
{
public int Id { get; set; }
public string? Name { get; set; }
public string? Address { get; set; }
}
SELECT p.Id as ProductId, p.Name as ProductName, p.Price, p.Quantity, o.Id as OrderId, o.Name as OrderName, o.Address
FROM Product p
JOIN Order o ON o.Id = p.OrderId
WHERE p.OrderId=@OrderId
The query is more complicated now because we have used a JOIN statement to fetch data from both Product and Order tables. Here, we have to use Dapper’s multi mapping feature.
SqlConnection connection = new SqlConnection(AppConstants.ConnectionString);
const string query = @"SELECT p.Id as ProductId, p.Name as ProductName, p.Price, p.Quantity, o.Id as OrderId, o.Name as OrderName, o.Address
FROM Product p
JOIN Order o ON o.Id = p.OrderId
WHERE p.OrderId=@OrderId";
int orderId = 5;
var results = await connection.QueryAsync<Product, Order, Product>(
query,
(product, order) =>
{
product.Order = order;
return product;
},
new { OrderId = orderId },
splitOn: "OrderId");
In the QueryAsync method, we provide both Product and Order as return types and Product as final return type.
We have also specified how to map Product and Order from the result set into a single Product object. Additionally we have used splitOn to instruct Dapper where one object ends and next one begins.
One to Many Relationship Mapping
When we are joining two tables, the result set will contain duplicate data on the “one” side of the relationship but we have to return it into a single object model.
For example, an order can contain multiple products.
public class Order
{
public int Id { get; set; }
public string? Name { get; set; }
public string? Address { get; set; }
public List<Product>? Products { get; set; }
}
public class Product
{
public int Id { get; set; }
public string? Name { get; set; }
public decimal Price { get; set; }
public int Quantity { get; set; }
public int OrderId { get; set; }
public Order? Order { get; set; }
}
Here is the SQL query that returns all the products for a specific order.
SELECT o.Id as OrderId, o.Name as OrderName, o.Address as OrderAddress, p.Id as ProductId, p.Name as ProductName, p.Price as ProductPrice, p.Quantity as ProductQuantity
FROM Order o
JOIN Product p ON p.OrderId = o.Id
WHERE o.Id=@OrderId
From this query, we are gonna get Order data because of the join. But we need to fetch only one Order with all the related products. The Order and Product for the current row in the result set are the only information provided by the Dapper mapping function. Using a dictionary to store the Order and reuse it inside the mapping is one method to overcome this problem.
- First check if the Order is in the dictionary. If not, store it
- If it’s there, add the Product to the existing Order instance
SqlConnection connection = new SqlConnection(AppConstants.ConnectionString);
const string query = @"SELECT o.Id as OrderId, o.Name as OrderName, o.Address as OrderAddress, p.Id as ProductId, p.Name as ProductName, p.Price as ProductPrice, p.Quantity as ProductQuantity
FROM Order o
JOIN Product p ON p.OrderId = o.Id
WHERE o.Id=@OrderId";
int orderId = 5;
var ordersDictionary = new Dictionary<long, Order>();
await connection.QueryAsync<Order, Product, Order>(
query,
(order, product) =>
{
if (ordersDictionary.TryGetValue(order.OrderId, out var existingOrder))
{
order = existingOrder;
}
else
{
ordersDictionary.Add(order.OrderId, order);
}
order.Products.Add(lineItem);
return order;
},
new { OrderId = orderId },
splitOn: "ProductId");
var mappedOrder = ordersDictionary[orderId];
For many-to-many mapping we’ll need an extra dictionary to store and track unique products.
public class Product
{
public int Id { get; set; }
public string? Name { get; set; }
public decimal Price { get; set; }
public int Quantity { get; set; }
public int OrderId { get; set; }
public List<Order>? Orders { get; set; }
}
public class Order
{
public int Id { get; set; }
public string? Name { get; set; }
public string? Address { get; set; }
public List<Product>? Products { get; set; }
}
SqlConnection connection = new SqlConnection(AppConstants.ConnectionString);
const string query = @"SELECT o.Id as OrderId, o.Name as OrderName, o.Address as OrderAddress, p.Id as ProductId, p.Name as ProductName, p.Price as ProductPrice, p.Quantity as ProductQuantity
FROM Order o
JOIN Product p ON p.OrderId = o.Id
WHERE o.Id=@OrderId";
var ordersDictionary = new Dictionary<long, Order>();
var productsDictionary = new Dictionary<long, Product>();
await connection.QueryAsync<Order, Product, Order>(
query,
(order, product) =>
{
if (!ordersDictionary.TryGetValue(order.OrderId, out var existingOrder))
{
existingOrder = order;
existingOrder.Products = new List<Product>();
ordersDictionary.Add(existingOrder.OrderId, existingOrder);
}
if (!productsDictionary.TryGetValue(product.ProductId, out var existingProduct))
{
existingProduct = product;
existingProduct.Orders = new List<Order>();
productsDictionary.Add(existingProduct.ProductId, existingProduct);
}
existingOrder.Products.Add(existingProduct);
existingProduct.Orders.Add(existingOrder);
return existingOrder;
},
new { OrderId = orderId },
splitOn: "ProductId");
var mappedOrder = ordersDictionary[orderId];
Final Words
Mastering Dapper’s relationship mappings is key to simplifying data access in .NET applications. By leveraging Dapper’s capabilities to handle one-to-one and one-to-many relationships, developers can streamline their data access code, improve application performance, and focus on building innovative solutions.
With its lightweight footprint and raw performance, Dapper continues to be a go-to choice for .NET developers seeking efficient data access solutions.
