Tuesday, November 1, 2011

LINQ union with group by

In my recent development activity with LINQ query, I need to get the some of user points from tow different tables where user's points are based on some activities. There are many users who are into one table but not in another table and some are in both table.


For example tables having data like this


Table 1
UserID  Points
1           10
2           20



Table 2
UserID  Points
2           20
3           30



Result should be
UserID  Points
1           10
2           40
3           30


I try to get this result with LINQ query using joins and group by samples from the web.
I had googling to get something useful but dont get any success.


So I try with new logic and here it is, I think it will help full to other also.

Example Item class to use


public class Item
{
  public int ID { get; set; }
  public int Total { get; set; }

  public Item()
  {
  }

  public Item(int ID, int Total)
  {
    this.ID = ID; this.Total = Total;
  }
}


Code to get result


class Program
{
  static void Main(string[] args)
  {

    List<Item> i1 = new List<Item>();
    i1.Add(new Item(1, 10));
    i1.Add(new Item(2, 20));

    List<Item> i2 = new List<Item>();
    i2.Add(new Item(2, 20));
    i2.Add(new Item(3, 30));

    var result = (from c in i1.Union(i2)
                  group c by c.ID
                  into g
                  select new
                  {
                    ID = g.Key,
                    Total = g.Sum(c=>c.Total)
                  }).ToList();

    foreach (var r in result)
    {
      Console.WriteLine("ID {0} total {1}", r.ID, r.Total);
    }
    Console.ReadLine();
   }
}


It is simple console code in c# to demonstrate the logic.