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();
Hope, It helps.