LINQ to model grouping

Question

I am trying to group multiple items according to several columns from my database view. I have combed through countless posts covering this topic and have had no luck in solving my issue.

Here is an example view definition:

|CustomerID|OrderID|Product|

Here is some sample data:

CustomerID|OrderID|Product
    1     |  123  | Apple
    1     |  123  | Pear
    1     |  124  | Apple

Here is what I want to achieve in my json:

CustomerID: 1
OrderIDs: [
  {OrderID: 123,
  Product: [
    Apple,
    Pear
  ]},
  {OrderID: 124,
  Product: [
    Apple
  ]}
]

These are my classes

class CustomerOrders
{
    public int CustomerID { get; set; }
    public IEnumerable<OrderIDs> OrderID;
}

class OrderIDs
{
    public int OrderID { get; set; }
    public int Product { get; set; }
}

And my Linq :

.Select(co => new CustomerOrders
{
 CustomerID = co.CustomerID
 OrderIDs = co.Select(o => new OrderID
 {
   OrderID = o.OrderID
   Product = o.Product
 }
}

But this returns the following json:

CustomerID: 1
OrderIDs: [
  {
  OrderID: 123,
  Product: [
    Apple,
    Pear
  ]},
  OrderID: 123,
  Product: [
    Apple,
    Pear
  ]},
  {OrderID: 124,
  Product: [
    Apple
  ]}
]

Answer 1

There are already some problems on your classes, for example Product is set to int whereas it should be an IEnumerable . And the problem is that you did not use GroupBy in your Linq.

Here is my result :

static void Main(string[] args)
{
    var data = new[]
    {
        new DbRow { CustomerId = 1, OrderId = 123, Product = "Apple" },
        new DbRow { CustomerId = 1, OrderId = 123, Product = "Pear" },
        new DbRow { CustomerId = 1, OrderId = 124, Product = "Apple" }
    };

    var resultFullLinq = data.GroupBy(row => row.CustomerId)
        .Select(groupCustomer => new CustomerOrder
        {
            Id = groupCustomer.Key,
            Orders = groupCustomer.GroupBy(row => row.OrderId)
                .Select(groupOrder => new Order
                {
                    Id = groupOrder.Key,
                    Products = groupOrder.Select(row => row.Product).ToList()
                }).OrderBy(order => order.Id).ToList()
        }).ToList();

    // OR use methods

    var resultMethods = data.GroupBy(row => row.CustomerId).Select(groupCustomer => CreateCustomerOrder(groupCustomer)).ToList();
}

private static CustomerOrder CreateCustomerOrder(IGrouping<int, DbRow> groupCustomer)
{
    return new CustomerOrder
    {
        Id = groupCustomer.Key,
        Orders = GetOrderIds(groupCustomer).OrderBy(order => order.Id).ToList()
    };
}

private static IEnumerable<Order> GetOrderIds(IGrouping<int, DbRow> groupCustomer)
{
    return groupCustomer.GroupBy(row => row.OrderId).Select(groupOrder => GetOrderId(groupOrder));
}

private static Order GetOrderId(IGrouping<int, DbRow> groupOrder)
{
    return new Order
    {
        Id = groupOrder.Key,
        Products = groupOrder.Select(p => p.Product)
    };
}

// Class that represent a row of the database
public class DbRow
{
    public int CustomerId { get; set; }
    public int OrderId { get; set; }
    public string Product { get; set; }
}

class CustomerOrder
{
    public int Id { get; set; }
    public IEnumerable<Order> Orders;
}

class Order
{
    public int Id { get; set; }
    public IEnumerable<string> Products { get; set; }
}