Understanding Dapper splitOn

Question

I'm getting an unexpected result showing Products with an empty Items list following the expected result with the correct Items list. It's only happening for the Product that contains multiple items. Does anyone know how to code this for Dapper?

This is my test code:

    public IEnumerable<Customer> Test2()
    {
        var query = @"select cast(1 as int) CustomerId, 'name' CustomerName,cast(1 as int) CustomerId, cast(1 as int) ProductId, 'a' ProductName, 'x' AccountOpened, cast(1 as int) ProductId, cast(1 as int) ItemId, 'a' ItemName
UNION
select cast(1 as int) CustomerId, 'name' CustomerName,cast(1 as int) CustomerId, cast(1 as int) ProductId, 'a' ProductName, 'x' AccountOpened, cast(1 as int) ProductId, cast(2 as int) ItemId, 'b' ItemName
UNION
select cast(1 as int) CustomerId, 'name' CustomerName, cast(1 as int) CustomerId, cast(2 as int) ProductId, 'b' ProductName, 'x' AccountOpened, cast(2 as int) ProductId, cast(3 as int) ItemId, 'b' ItemName
UNION
select cast(2 as int) CustomerId, 'name1' CustomerName, cast(2 as int) CustomerId, cast(1 as int) ProductId, 'a' ProductName, 'x' AccountOpened, cast(1 as int) ProductId, cast(1 as int) ItemId, 'a' ItemName
UNION
select cast(2 as int) CustomerId, 'name1' CustomerName, cast(2 as int) CustomerId, cast(1 as int) ProductId, 'a' ProductName, 'x' AccountOpened, cast(1 as int) ProductId, cast(2 as int) ItemId, 'b' ItemName
UNION
select cast(2 as int) CustomerId, 'name1' CustomerName, cast(2 as int) CustomerId, cast(2 as int) ProductId, 'b' ProductName, 'x' AccountOpened, cast(2 as int) ProductId, cast(3 as int) ItemId, 'b' ItemName
UNION
select cast(2 as int) CustomerId, 'name1' CustomerName, cast(2 as int) CustomerId, cast(3 as int) ProductId, 'c' ProductName, 'x' AccountOpened, cast(3 as int) ProductId, cast(4 as int) ItemId, 'c' ItemName";
        var lookup = new Dictionary<int, Customer>();
        DbConnection.Query<Customer, Product, Item, Customer>(query, (c, p, i) =>
        {
            if (!lookup.TryGetValue(c.CustomerId, out var cust))
                lookup.Add(c.CustomerId, cust = c);
            if (cust.Products == null)
                cust.Products = new List<Product>();
            var product = p;
            if (cust.Products.Exists(prod => prod.ProductId == p.ProductId))
            {
                product = cust.Products.Find(prod => prod.ProductId == p.ProductId);
            }
            cust.Products.Add(p);
            if (product.Items == null)
                product.Items = new List<Item>();
            product.Items.Add(i);
            return cust;
        }, splitOn: "CustomerId,ProductId");
        return lookup.Values;
    }

    public class Customer
    {
        public int CustomerId { get; set; }
        public string CustomerName { get; set; }
        public List<Product> Products { get;set; }
    }
    public class Product
    {
        public int ProductId { get; set; }
        public string ProductName { get; set; }
        public string AccountOpened { get; set; }
        public List<Item> Items { get; set; }
        //public int CustomerId { get; set; }
    }
    public class Item
    {
        public int ItemId { get; set; }
        public string ItemName { get; set; }
        //public int ProductId { get; set; }
    }

This is the JSON-ized response I'm getting back:

[
  {
    "customerId": 1,
    "customerName": "name",
    "products": [
      {
        "productId": 1,
        "productName": "a",
        "accountOpened": "x",
        "items": [
          {
            "itemId": 1,
            "itemName": "a"
          },
          {
            "itemId": 2,
            "itemName": "b"
          }
        ]
      },
      {
        "productId": 1,
        "productName": "a",
        "accountOpened": "x",
        "items": null
      },
      {
        "productId": 2,
        "productName": "b",
        "accountOpened": "x",
        "items": [
          {
            "itemId": 3,
            "itemName": "b"
          }
        ]
      }
    ]
  },
  {
    "customerId": 2,
    "customerName": "name1",
    "products": [
      {
        "productId": 1,
        "productName": "a",
        "accountOpened": "x",
        "items": [
          {
            "itemId": 1,
            "itemName": "a"
          },
          {
            "itemId": 2,
            "itemName": "b"
          }
        ]
      },
      {
        "productId": 1,
        "productName": "a",
        "accountOpened": "x",
        "items": null
      },
      {
        "productId": 2,
        "productName": "b",
        "accountOpened": "x",
        "items": [
          {
            "itemId": 3,
            "itemName": "b"
          }
        ]
      },
      {
        "productId": 3,
        "productName": "c",
        "accountOpened": "x",
        "items": [
          {
            "itemId": 4,
            "itemName": "c"
          }
        ]
      }
    ]
  }
]

Answer 1

I see te problem is where you work with products in query.

This may help

var product = p;
if (cust.Products.Exists(prod => prod.ProductId == p.ProductId))
{
   product = cust.Products.Find(prod => prod.ProductId == p.ProductId);
}
else
{
   cust.Products.Add(p);
}

if (product.Items == null)
   product.Items = new List<Item>();
product.Items.Add(i);

Answer 2

This is what I changed to fix the code:

        var product = p;
        if (cust.Products.Exists(prod => prod.ProductId == p.ProductId))
        {
            product = cust.Products.Find(prod => prod.ProductId == p.ProductId);
        }
        cust.Products.Add(p);

To this:

        var product = cust.Products.FirstOrDefault(prod => prod.ProductId == p.ProductId);
        if (product == null)
            cust.Products.Add(product = p);

Answer 3

The Problem is your query, Every ProductId should have the same List of Items

Your Query which should be

SELECT * FROM Customer c 
INNER JOIN Product p ON c.CustomerId = p.CustomerId
INNER JOIN Item i ON i.ProductId = p.ProductId

Your Dapper query is like

var cust = DbConnection.Query<Customer, Product, Item, Customer>(query, (c, p, i) =>
    {
        c.Products.Add(p);
        p.Items.Add(i);

        return c;
    }, splitOn: "ProductId,ItemId");
return cust;

You may need to initialize your Lists if they are null.