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.no Name wages
1 Tom $200
1 Tom $300
1 Tom $400
2 Rob $500
2 Rob $600


The required output is in following way:

Name OriginalWage DuplicateWage
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.

Leave a Reply

Your email address will not be published. Required fields are marked *