Dapper nested objects

Question

I am following this already existing question to make it work for a similar case in my scenario but there is something missing and I hope you can help me.

My class structure:

class Order
{
    public int Order_Id { get; set; }
    public Customer Customer { get; set; }
    public List<Product> Products { get; set; }

    public static string query = @"SELECT ORD.* , CUST.*, PROD.* FROM [Order] AS ORD
                                    JOIN dbo.Customer AS CUST ON ORD.Customer_Id = CUST.Customer_Id
                                    JOIN dbo.Product AS PROD ON PROD.Product_Id = ORD.Product_Id";
}

class Customer
{
    public int Customer_Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

class Product
{
    public int Product_Id { get; set; }
    public string Product_Name { get; set; }
}

My dapper call:

var result = connection.Query<Order, Customer, Product, Order>(Order.query,
(order, customer, products) => {
    order.Customer = customer;
    order.Products = new List<Product>();
    order.Products.Add(products);
    return order;
}, splitOn: "Customer_Id, Product_Id").AsQueryable();

The customer gets filled correctly but not the list of products. This is what is in the DB:

What I would expect is a list of 2 orders, where each one contains 2 products

Answer 1

You have multiple columns with the same name. How will Dapper know which one to split on? It is just going to pick the first

In any case, SELECT * is generally bad, as you may be pulling far more info than you need.

What you need to do is rewrite your query to have distinct column names, and only ask for info that you actually need in your objects.

---

You also need guaranteed ordering here, so that child items are grouped together. Something like:
ORDER BY Order_id, Customer_Id ``