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