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"
}
]
}
]
}
]
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.