Dapper - triple join with two list

Question

I checked previous threads, could not find anything..

The scenario is, 3 classes :

public class Customer
{
    public long Cust_id { get; set; }
    public string Cust_name { get; set; }
    public List<Order> Orders { get; set; }
}

public class Order
{
    public long Order_id { get; set; }
    public long Cust_id { get; set; }
    public string Order_no { get; set; }
    public List<OrderDetail> OrderDetails { get; set; }
}

public class OrderDetail
{
    public long Odetail_id { get; set; }
    public long Order_id { get; set; }
    public decimal Final_price { get; set; }
}

and running this :

    string sqlQuery = @"select  customers.* , orders.*, order_detail.* from customers
                inner join orders on orders.cust_id = customers.cust_id
                inner join order_detail on order_detail.order_id = orders.order_id"; 

    //here we getting raw the data *1* multiple cust_id, *2* multiple order_id, *3* multiple order_detail.order_id
    var t = General.db.GetConnection().Query<Customer, Order, OrderDetail, Customer>(sqlQuery, (customer, order, orderdetail) =>
    {
        //1st rel
        customer.Orders = new List<Order>();
        customer.Orders.Add(order);

        //2nd rel
        customer.Orders[0].OrderDetails = new List<OrderDetail>();
        customer.Orders[0].OrderDetails.Add(orderdetail);

        return customer;
    }, splitOn: "order_id, odetail_id");

which return exactly record count like this (sample query on database) :

using the

    var result = t.GroupBy(p => p.Cust_id).Select(g =>
    {
        var groupedCustomer = g.First();
        groupedCustomer.Orders = g.Select(p => p.Orders.Single()).ToList();
        return groupedCustomer;
    });

found here, is able to group the ORDERS by CUSTOMERS - OK... But we need also the to group the ORDERDETAILS by ORDER... Anyone knows something or alternative way than LINQ ?

When I use Dapper, I create POCO classes that match the query, I don't try to get Dapper to figure out how to fill per-table classes. So, my query would be `cnxn.Query(queryString)`. Now I have a List that looks like my SQL results etc and I can go to town — Flydog57, Jul 07 '23 at 22:17
i think you will come to the same problem in the end... because you have to group by 3 entities.. — Zakari, Jul 07 '23 at 22:52
update : hmm, looks is doable according to https://stackoverflow.com/a/56826135 — Zakari, Jul 08 '23 at 00:18

score 0 · Answer 1 · answered Jul 10 '23 at 09:19

Your results come as a two-dimensional array, so data is repeated line-by-line. Hence you cannot create a new order list for each row, you need to do it when the customer changes. The two approaches are to either put data in a dynamic and sort it afterwards, or to write your own mapping. I will show you the latter. First you need to define two dictionaries to pick up your customers and orders. I'm assuming the order id's are unique, otherwise you will have to make a composite dictionary key.

var customerDictionary = new Dictionary<long, Customer>();
var orderDictionary = new Dictionary<long, Order>();

When you encounter a new Customer or Order you need to put them in the relevant dictionary, otherwise you find the value and add to that.

var t = General.db.GetConnection().Query<Customer, Order, OrderDetail, Customer>(sqlQuery, (customer, order, orderdetail) =>
    {
        Customer? customerResult;
        if (!customerDictionary.TryGetValue(customer.Cust_id, out customerResult))
        {
            // We haven't see this customer before so create it.
            customerResult = customer;
            customerResult.Orders = new List<Order>();
            customerDictionary.Add(customer.Cust_id, customerResult);
        }

        Order? orderResult;
        if (!orderDictionary.TryGetValue(order.Order_id, out orderResult))
        {
            // We haven't seen this order before, so create it
            orderResult = order;
            orderResult.OrderDetails = new List<OrderDetail>();
            orderDictionary.Add(order.Order_id, orderResult);
            // And add it to the current customer
            customerResult.Orders.Add(order);
        }

        orderResult.OrderDetails.Add(orderDetail);
        return null; // Doesn't matter what we return. The result is in customerDictionary
    }, splitOn: "order_id, odetail_id");

After this you can go ahead and use the data in customerDictionary, orderDictionary can be discarded.

Hi Palle Due, this is like you doing a /for/ loop.. When you are not using LINQ this is the solution. — Zakari, Jul 11 '23 at 10:12

Dapper - triple join with two list

1 Answers1