Add nested values from nested class lists in Linq

Question

I am sorry for the amount of code, but it's hard to really explain the structure of each class so I hope that it is ok that I included this much. So I am learning more complex queries using Linq in C#. Most of them involve querying numerous fields from numerous nested objects.

My issue is figuring out how to get the information I want. For instance the one I am stuck on now which has prompted me to make this is shown below. I am to query the Order Id, Customer First/Last Name, and the total for all orders with a placed status. There are numerous other generated objects but they don't have a placed status so I did not include them.

The current query shown below, returns Roger 4 times and Mary 5 times. Which is correct as it's coded due to their being 4 adds for Roger and 5 adds for Mary. However, I am getting no LastName and haven't the slightest clue as to how to sum all orders placed.

When trying to use any sort of .Sum or .Count feature it doesn't recognize it. I know I am likely not shaping the data correctly with my select new statements. I am looking for some insight as to how to minimize the amount of data being output as well as what direction to go in to accomplish adding up the totals.

 class Customer
        {
            public int CustomerID { get; set; }
            public String FirstName { get; set; }
            public String LastName { get; set; }
            public String Email { get; set; }

            public Customer(int id, String fName, String lName, String email)
            {
                CustomerID = id;
                FirstName = fName;
                LastName = lName;
                Email = email;
            }
        }
    }

        class Order
    {
        public int OrderID { get; set; }
        public DateTime OrderDate { get; set; }
        public String ShippingMethod { get; set; }
        public String OrderStatus { get; set; }
        public Customer OrderedBy { get; set; }
        public List<Product> OrderItems { get; set; }

        public Order(int ordId, DateTime ordDate, String shipMethod, String ordStatus, Customer cust)
        {
            OrderID = ordId;
            OrderDate = ordDate;
            ShippingMethod = shipMethod;
            OrderStatus = ordStatus;
            OrderedBy = cust;
            OrderItems = new List<Product>();
        }

        public void addProduct(Product prod)
        {
            OrderItems.Add(prod);
        }
        public double calcOrderTotal()
        {

            var itemVar = OrderItems.Sum(i => i.calcProductTotal());

            return itemVar;
        }
        public double calcShipping()
        {
            double total = 0;
            return total;
        }

    }
}

    class Product
    {
        public int ProdID { get; set; }
        public String Name { get; set; }
        public double Price { get; set; }
        public String Image { get; set; }
        public String Desc { get; set; }
        public int QtyOrdered { get; set; }
        public double Weight { get; set; }

        public Product(int id, string name, double price)
        {
            ProdID = id;
            Name = name;
            Price = price;
        }
        public Product(int id, string name, double price, string image, string desc)
            : this(id, name, price)
        {
            Image = image;
            Desc = desc;
        }
        public Product(int id, string name, double price, int qty, double weight) : this(id, name, price)
        {
            QtyOrdered = qty;
            Weight = weight;
        }
        public double calcProductTotal()
        {
            return Price * QtyOrdered;
        }
    }
}

   class Program
{
    static void Main(string[] args)
    {   //http://msdn.microsoft.com/en-us/vcsharp/aa336746
        List<Order> orders2 = setupData2();

private static List<Order> setupData2()
        {
    List<Order> orders = new List<Order>();
            Customer c1 = new Customer(14, "Mary", "Smith", "msmith@gmail.com");
            Customer c2 = new Customer(25, "Andy", "Johnson", "andy.johnson@gmail.com");
            Customer c3 = new Customer(42, "Tim", "Clark", "tc@tc.net");
            Customer c4 = new Customer(125, "Roger", "Wilson", "rogerw@zmail.com");

    Order ord4 = new Order(48, DateTime.Now.Subtract(new TimeSpan(4, 1, 1, 1, 1)), "Ground", "Placed", c4);
            ord4.addProduct(new Product(129, "Do It Yourself Tornado Kit", 225.50, 4, 85.5));
            ord4.addProduct(new Product(421, "Catcus Costume", 48.70, 2, 18.85));
            ord4.addProduct(new Product(400, "Anvil", 338.70, 1, 384.25));
            ord4.addProduct(new Product(455, "Jet Propelled Unicycle", 556.40, 4, 328.35));
            orders.Add(ord4);

            Order ord5 = new Order(55, DateTime.Now.Subtract(new TimeSpan(1, 1, 1, 1, 1)), "Ground", "Placed", c1);
            ord5.addProduct(new Product(124, "Earth Quake Pills", 145.50, 3, 2.25));
            ord5.addProduct(new Product(129, "Do It Yourself Tornado Kit", 225.50, 1, 85.5));
            ord5.addProduct(new Product(327, "Giant Mouse Trap", 88.70, 4, 26.50));
            ord5.addProduct(new Product(400, "Anvil", 338.70, 2, 384.25));
            ord5.addProduct(new Product(425, "Iron Bird Seed", 27.70, 1, 5.85));
            orders.Add(ord5); }

                                var orders = (from o in orders2
                      from p in o.OrderItems
                      where o.OrderStatus == "Placed"
                      orderby o.OrderDate
                      let total = p.Price * p.QtyOrdered
                      select new { o.OrderID, o.OrderedBy.FirstName, o.OrderedBy.LastName, Total = total });
        double totalOrders = 0;
        foreach (var x in orders)
        {            
            Console.WriteLine("Order Id: " + x.OrderID + " Ordered by: " + x.FirstName + " " + x.LastName);
            totalOrders+= x.Total;
        }
        Console.WriteLine("Price for all Orders: " + totalOrders);
}

Possible groupby solution, but can't pull in the names now.

            var ordersTotal = from o in orders2
                          from p in o.OrderItems
                          where o.OrderStatus == "Placed"
                          orderby o.OrderDate
                          group p by o.OrderID into g
                          select new { OrderId = g.Key, Price = g.Sum(p => p.Price * p.QtyOrdered) };

FIrst you have two objects named orders (bad coding), second you are not getting LastName because your anonymous type has `o.OrderedBy.LastName` when you place items into this type it is already ordered by what ever commands came before `orderby o.OrderDate` — Edward, Mar 15 '17 at 02:59
@nick-s Apologies, it is my initialization of the collections of orders. The code should be updated correctly now. It's just creating the List with all the Orders in it, which is the SetupData2 method, and I am calling that List orders2. — The_fatness, Mar 15 '17 at 02:59
@Edward that is not allowed. Says that 'Order' does not contain a definition for LastName. — The_fatness, Mar 15 '17 at 03:19
@Edward Actually no, that is not required. The property names in anonymous types are derived from the `OrderedBy` property. — h-rai, Mar 15 '17 at 03:27
To get your counts (indexing) your solution can be found here [index](http://stackoverflow.com/questions/269058/how-do-you-add-an-index-field-to-linq-results) it shows how to do it with regular linq and with lambda expression — Edward, Mar 15 '17 at 03:30
It's not quite clear WHAT kind of sum you really want. It can be the total number of orders or total number of products ordered in all the orders collectively or it could be the sum of the value of all the products in all the orders. — h-rai, Mar 15 '17 at 03:33

h-rai · Accepted Answer · 2017-03-15T03:53:05.027

1

Try this code.

            var orders = (from o in orders2
                          where o.OrderStatus == "Placed"
                          orderby o.OrderDate
                          select new { o.OrderedBy.FirstName, o.OrderedBy.LastName }).Distinct();

            foreach (var x in orders)
            {
                Console.WriteLine(x.FirstName + " " + x.LastName);
            }

            Console.WriteLine(orders2.Sum(order => order.OrderItems.Select(item =>(double)(item.QtyOrdered * item.Price)).Sum()));

edited Mar 15 '17 at 03:53

answered Mar 15 '17 at 03:22

h-rai

3,636
6
52
76

A better way for this in c# is `Console.WriteLine("{0} {1}", x.FirstName, x.LastName);` – Edward Mar 15 '17 at 03:25
Ok, I did that and it works. It's a little ugly but with yours and Edwards comments, I've narrowed down the issue to it printing each .addProduct. Which I imagine is a part of the foreach loop. What I need to do now is decipher how to make it print each line once and then have the total at the bottom. I will update the code with what I have now. – The_fatness Mar 15 '17 at 03:26
You mean single line for each name? – h-rai Mar 15 '17 at 03:28
@nick-s I have tried implementing the Distinct() but it is still printing it out multiple times. In reference to your other comment, the updated code now correctly adds the totals, although it is likely a poor way to do it. – The_fatness Mar 15 '17 at 03:36
That's because your `select new` code has changed to include `Total` which is going to be distinct for each of the row even if it has same Customer. – h-rai Mar 15 '17 at 03:42
@The_fatness correct a proper way would be a groupby then sum, also groupby would reduce the result set to each person like you want. – Edward Mar 15 '17 at 03:46
@nick-s Your updated code prints each value line by line, doing the calculations I am not sure where those values are coming from. The price * qty doesn't match on any line. Edward Trying to use a groupby now. – The_fatness Mar 15 '17 at 03:55
@Edward I added a groupby with sum to the bottom of the OP. Can't figure out how to include the names now. – The_fatness Mar 15 '17 at 04:21
@nick-s Thank you very much – The_fatness Mar 15 '17 at 16:40

Daveo · Answer 2 · 2017-03-15T22:38:01.347

0

Take a look at this .NET fiddle of your code

https://dotnetfiddle.net/91yYMF

It shows how you can do a Sum. Is there anything else you need to do?

edited Mar 15 '17 at 22:38

answered Mar 15 '17 at 04:08

Daveo

19,018
10
48
71

not sure what you're saying, that code is what I posted. Nothing changes, and it still doesn't output the correct information. – The_fatness Mar 15 '17 at 05:10
@The_fatness I was simply showing an alternate way to do it - see the section `Another way to do it` I was asking for clarification of what the **correct information is?** As I am not sure what you are trying to do – Daveo Mar 15 '17 at 22:37

Add nested values from nested class lists in Linq

2 Answers2