C# -Datewise total and GrandTotal using LINQ

Question

From the Data,I have to find datewise total and GrandTotal the appropriate row should be filled with (***) when not applicable).

OrderID     OrderAmt   OrderDate                                              
----------- ---------- --------------------
1           10.50      2003-10-11 08:00:00
2           11.50      2003-10-11 10:00:00
3           1.25       2003-10-11 12:00:00
4           100.57     2003-10-12 09:00:00
5           19.99      2003-10-12 11:00:00
6           47.14      2003-10-13 10:00:00
7           10.08      2003-10-13 12:00:00
8           7.50       2003-10-13 19:00:00
9           9.50       2003-10-13 21:00:00

Output

OrderID     Order Date OrderAmt   Sub Total  Grand Total 
----------- ---------- ---------- ---------- ----------- 
1           10/11/2003 10.50       ***         ***            
2           10/11/2003 11.50       ***         ***           
3           10/11/2003 1.25       23.25        ***        
4           10/12/2003 100.57      ***         ***            
5           10/12/2003 19.99      120.56       ***        
6           10/13/2003 47.14      ***          ***           
7           10/13/2003 10.08      ***          ***           
8           10/13/2003 7.50       ***          ***           
9           10/13/2003 9.50       74.22      218.03

What are the changes needed in the following query ?

var qry =
                (from o in ord

                 select new
                 {

                     OrdNumber = o.OrderNumber,
                     OrdDate = o.OrderDate,
                     Amount = o.OrderAmount,
                     GrandTotal = ord.Sum(p => p.OrderAmount)
                 }
                  ).ToList();

Answer 1

This will group by data and also show the grand total over the list

public class MyClass
{
    public int OrderID;
    public decimal OrderAmount;
    public DateTime OrderDate;
}

List<MyClass> list = new List<MyClass>();
list.Add(new MyClass { OrderID = 1, OrderAmount = 10.50m, OrderDate = new DateTime(2009, 10, 11, 8, 0, 0) });
list.Add(new MyClass { OrderID = 1, OrderAmount = 11.50m, OrderDate = new DateTime(2009, 10, 11, 10, 0, 0) });
list.Add(new MyClass { OrderID = 1, OrderAmount = 1.25m, OrderDate = new DateTime(2009, 10, 11, 12, 0, 0) });

list.Add(new MyClass { OrderID = 1, OrderAmount = 100.57m, OrderDate = new DateTime(2009, 10, 12, 09, 0, 0) });
list.Add(new MyClass { OrderID = 1, OrderAmount = 19.99m, OrderDate = new DateTime(2009, 10, 12, 11, 0, 0) });

var t = from l in list
    group l by l.OrderDate.Date into g
    let sum = g.Sum(x => x.OrderAmount)
    select new { g.Key, sum , GrandTotal = list.Sum(x => x.OrderAmount) };

Not sure how you want to get it into the format you require above?

Answer 2

Apart from being unreadable, and wildly inefficient, this should do the trick:

var SubTotals = from o in ord
                group o by o.OrderDate.Date into g
                    select new
                               {
                                   Id = g.Max(x => x.OrderNumber),
                                   Amount = g.Sum(x => x.OrderAmount)
                               };
var qry = from o in ord
          orderby o.OrderDate
          let subTotals = SubTotals.Where(x => x.Id == o.OrderNumber)
          let grandTotal = ord.Sum(x => x.OrderAmount)
          let lastId = ord.OrderBy(x => x.OrderNumber).Last().OrderNumber
          select
              new
                  {
                      OrderNumber = o.OrderNumber,
                      OrderDate = o.OrderDate.Date,
                      Amount = o.OrderAmount,
                      SubTotal = (subTotals.Any()
                                    ? subTotals.First().Amount.ToString()
                                    : "***"),
                      GrandTotal = (o.OrderNumber == lastId
                                    ? grandTotal.ToString()
                                    : "***")
                  };

As a general disclaimer I would urge you to reconsider using linq for formatting data. Hacking up this kind of code is fine if you are tied to using only SQL, but given the chance, there is no shame in just looping over a dataset :-)