Jun 27, 2013

An Interesting LINQ Exercise

This post is in reply of this thread of the official ASP.NET forum.

The input data is in following format:

S.noNamewages
1Tom$200
1Tom$300
1Tom$400
2Rob$500
2Rob$600

The required output is in following way:

NameOriginalWageDuplicateWage
Tom$200$300
Tom$200$400
Rob$500$600

It looks interesting to combine each person first record to all other records using LINQ. So I decided to write this post.

For sample input data in c#:


 var ret =  new[]{            
                                new { SNO=1,Name="Tom",Wages=200 },
                                new { SNO=1,Name="Tom",Wages=300 },
                                new { SNO=1,Name="Tom",Wages=400 },
                                new { SNO=2,Name="Rob",Wages=500 },
                                new { SNO=2,Name="Rob",Wages=600 },
                                new { SNO=3,Name="John",Wages=700 },
                                new { SNO=3,Name="John",Wages=800 },
                                new { SNO=3,Name="John",Wages=500 },
                                new { SNO=3,Name="John",Wages=600 } 
                            };

To get the desired result:


 var result = (from item in ret
                      group item by item.SNO into grp
                      let org = grp.First()
                      let dup = grp.Skip(1).Select(x => new { Name = org.Name, Org = org.Wages, Dup =x.Wages}) 
                      select dup).SelectMany(x=>x).ToList();

it will generate the required output.

What will happen if there is only one row of the person? In this case, the row will be skipped. Consider following input:


var ret =  new[]{            
                                new { SNO=1,Name="Tom",Wages=200 },
                                new { SNO=1,Name="Tom",Wages=300 },
                                new { SNO=1,Name="Tom",Wages=400 },
                                new { SNO=2,Name="Rob",Wages=500 },
                                new { SNO=2,Name="Rob",Wages=600 },
                                new { SNO=3,Name="John",Wages=700 },
                                new { SNO=3,Name="John",Wages=800 },
                                new { SNO=3,Name="John",Wages=500 },
                                new { SNO=3,Name="John",Wages=600 },
                                new { SNO=4,Name="KP",Wages=600 } 
                            };

The last person record will be skipped.

To get records of all persons (set default duplicate wage = 0 in case of single record):


 var result2 = (from item in ret
                       group item by item.SNO into grp
                       let org = grp.First()
                       let dup = grp.Count() >1 
                                 ? grp.Skip(1).Select(x => new { Name = org.Name, Org = org.Wages, Dup = x.Wages })
                                 : grp.Select(x => new { Name = org.Name, Org = org.Wages, Dup = 0 })
                       select dup).SelectMany(x => x).ToList();
linq c# group by

Hope, It helps.