Dapper One to Many Mapping Logic

user_5853685 0
Viewed 463

The dapper tutorial gives this example to help a user with Multi Mapping (One to Many) While this works I am curious why they have you store the orders in the dictionary but then in the end they use a linq.Distinct() and return from the list. It seems like it would be cleaner to just return the ordersDictionary.Values as the dictionary logic ensures no duplicates.

//Tutorial
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{            
    Dictionary<int,Order> orderDictionary = new Dictionary<int, Order>();
    List<Order> list = connection.Query<Order, OrderDetail, Order>(sql, (order, orderDetail) =>
    {
        if (!orderDictionary.TryGetValue(order.OrderID, out Order orderEntry))
        {
            orderEntry = order;
            orderEntry.OrderDetails = new List<OrderDetail>();
            orderDictionary.Add(orderEntry.OrderID, orderEntry);
        }
        orderEntry.OrderDetails.Add(orderDetail);
        return orderEntry;
    }, splitOn: "OrderID")
    .Distinct()
    .ToList();
    return list;
}

//my suggestion
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{            
    Dictionary<int,Order> orderDictionary = new Dictionary<int, Order>();
    //change 1 no need to store into list here
    connection.Query<Order, OrderDetail, Order>(sql, (order, orderDetail) =>
    {
        if (!orderDictionary.TryGetValue(order.OrderID, out Order orderEntry))
        {
            orderEntry = order;
            orderEntry.OrderDetails = new List<OrderDetail>();
            orderDictionary.Add(orderEntry.OrderID, orderEntry);
        }
        orderEntry.OrderDetails.Add(orderDetail);
        return orderEntry;
    }, splitOn: "OrderID"); //change 2 remove .Distinct().ToList()
    return orderDictionary.Values.ToList(); //change 3 return dictionaryValues
}
dapper
1 Answers

I'm the author of this tutorial: https://dapper-tutorial.net/query#example-query-multi-mapping-one-to-many

why they have you store the orders in the dictionary

A row is returned for every OrderDetail. So you want to make sure to add the OrderDetail to the existing Order and not create a new one for every OrderDetail. The dictionary is used for performance to check if the Order has been already created or not.

it would be cleaner to just return the ordersDictionary.Values

How will your query return dictionary values?

Of course, if you are in a method such as yours, you can do

var list = orderDictionary.Values;
return list;

But how to make this Connection.Query return dictionary values? An order is returned for every row/OrderDetail, so the order will be returned multiple times.

Outside the Query, your dictionary solution works great and is even a better solution for performance, but if you want to make your Query return the distinct list of orders without using Distinct or some similar method, it's impossible.

EDIT: Answer comment

my suggestion return orderDictionary.Values.ToList(); //change 3 return dictionaryValues

Thank you for your great feedback, it's always appreciated ;)

It would be weird in a tutorial to use what the query returns when there is no relationship but use the dictionary for one to many relationships

// no relationship
var orders = conn.Query<Order>("", ...).Distinct();

// one to many relationship
conn.Query<Order, OrderDetail>("", ...);
var orders = orderDictionary.Values.ToList();

Your solution is better for performance the way you use it, there is no doubt about this. But this is how people usually use the Query method:

var orders = conn.Query("", ...).Distinct();

var activeOrders = orders.Where(x => x.IsActive).ToList();
var inactiveOrders = orders.Where(x => !x.IsActive).ToList();

They use what the Query method returns.

But again, there is nothing wrong with the way you do it, this is even better if you can do it.

user_56191430
Related