How to turn dapper result into a dictionary using result mapping

Question

I want to use the splitOn feature denoted here: https://dapper-tutorial.net/result-multi-mapping

to group every Order of the results to a integer property "EmployeeId". I Followed the advice from How to map to a Dictionary object from database results using Dapper Dot Net?

but I am getting a An item with the same key has already been added. so how can I group my orders by EmployeeId?

I cannot modify the Order class and I prefer using a dictionary over creating a class that wraps Order. However, if there is no other way I am open to the idea of wrapping Order

https://dotnetfiddle.net/hn6Sjf

public class Program
{
    public class Order
    {
        public int OrderID { get; set; }
        public int CustomerID { get; set; }
        public DateTime OrderDate  { get; set; }
        public int ShipperID  { get; set; }
    }

    public static void Main()
    {
        string sql = @"
            SELECT TOP 10
                EmployeeID,
                OrderID,
                CustomerID,
                OrderDate,
                ShipperID
            FROM Orders 
            ORDER BY OrderID;
        ";

        using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
        {           
            var rawList = connection.Query<Order>(sql);
            FiddleHelper.WriteTable(rawList);   


                var dict = connection.Query<int, List<Order>, KeyValuePair<int, List<Order>>>(sql,
                    (s, i) => new KeyValuePair<int, List<Order>>(s, i), null, null, true, "OrderID")
                    .ToDictionary(kv => kv.Key, kv => kv.Value);

            FiddleHelper.WriteTable(dict);              
        }
    }
}

Alternatively, have you considered, or are you able to consider (given any constraints of your application), using `for json` in the SQL query, so that you return a JSON string which can be easily deserialized into your data structure? It's definitely less code, and may be just as performant. Let me know and I can give some additional pointers if necessary. Thanks. — square_particle, May 04 '20 at 17:50

jira · Accepted Answer · 2020-05-09T09:04:24.157

1

Would this meet your needs?

var dict = connection.Query<int, Order, ValueTuple<int, Order>>(sql,
        (s, i) => ValueTuple.Create(s, i), null, null, true, "OrderID")
        .GroupBy(t => t.Item1, t => t.Item2, (k, v) => new {Key = k, List = v})
        .ToDictionary(kv => kv.Key, kv => kv.List);

Fiddle

edited May 09 '20 at 09:04

answered May 08 '20 at 21:03

jira

3,890
3
22
32

Alex · Answer 2 · 2020-05-11T17:18:07.437

You could create an envelope class (Or use dynamic if you prefer that):

public class OrderEntity
{
  public int EmployeeID {get;set;}
  public Order Order {get;set;}
}

And then the mapping from the resultset into a dictionary grouped by employee id is straight forward:

var dict = new Dictionary<int,List<Order>>();
var r = connection.Query<OrderEntity, Order, OrderEntity>(sql,(orderEntity, order) => 
    {
        // You can skip that line if you want, the orderEntity is (probably) never used.
        orderEntity.Order = order;

        if(dict.ContainsKey(orderEntity.EmployeeID))
        {
            dict[orderEntity.EmployeeID].Add(orderEntity.Order);
        }
        else
        {
            dict.Add(orderEntity.EmployeeID, new List<Order> {orderEntity.Order});
        }

        return orderEntity;
    }, splitOn: "OrderID");

This method does it all in 1 iteration over the result set and only requires a O(1) key lookup into the dictionary.

How to turn dapper result into a dictionary using result mapping

2 Answers2