How to Update previous row column based on the current row column data using LinQ

Question

var customer= from cust in customerData
              select new Customer
               {
                 CustomerID = cust["id"],
                 Name = cust["Name"],
                 LastVisit = cust["visit"],
                 PurchashedAmount = cust["amount"],
                 Tagged = cust["tagged"]
                 Code = cust["code"]
               }

The rows looks like this

Name    LastVisit   PurchasedAmount    Tagged   Code       CustomerID
------  ---------    --------------     ------   -----       -----
Joshua  07-Jan-09                      Yes      chiJan01    A001
Joshua                10000       

The 2nd row belongs to first row just that the other columns are empty.How can i merge the PurchasedAmount into the first row using LinQ?

Answer 1

This is probably a more general solution than you need - it will work even if the other values are scattered across rows. The main condition is that the Name column should identify rows that belong together.

customer = from c in customer
           group c by c.Name
           into g
           select new Customer
           {
               Name = g.Key,
               LastVisit = g.Select(te => te.LastVisit).
                             Where(lv => lv.HasValue).FirstOrDefault(),
               PurchaseAmount = g.Select(te => te.PurchaseAmount).
                                  Where(pa => pa.HasValue).FirstOrDefault(),
               Tagged = g.Select(te => te.Tagged).
                          Where(ta => ta.HasValue).FirstOrDefault(),
               Code = g.Select(te => te.Code).
                        Where(co => !string.IsNullOrEmpty(co)).FirstOrDefault(),
               CustomerID = g.Select(te => te.CustomerID).
                        Where(cid => !string.IsNullOrEmpty(cid)).FirstOrDefault()

           };

This will return a new IEnumerable with the items grouped by Name and the non-null values selected (same effect as moving PurchasedAmount to the first row and deleting the second in your case).

Note that the code is based on the assumption that LastVisit, PurchaseAmount and Tagged are nullable types (DateTime?, int? and bool?). Thus the usage of HasValue. If, however, they are strings in your case, you have to use !string.IsNullOrEmpty() instead (as for Code and CustomerID).