Aggregation using Group by in LINQ

[Pankaj Chamria]

Hi, I am trying to run a linq query on a collection and I want the result to contain all the columns in the collection. But those columns that are not unique across a single rows' aggregation should have the value as null. How can i do this? Is there any expression available using which I can return all such columns for a row (apart from the ones participating in aggregation) which are unique for that aggregated row. eg: 1) The Class:

public class Person
{
string Name
string Sex
int Age
}

2. The collection:

List<Person> personList

3. Sample data in collection:

Name Sex Age

Rob Male 25
Bob Male 25
Kim Female 22
Debbie Female 22

var result = from p in personList
group p by new {p.Sex} into g
select new {g.Key.Sex, ???????}

I want to be able to get the anonymous result as follows:

Sex Age Name

Male 25(As 25 is unique across Males) Null (As Name is not unique across Males)
Female 22(As 22 is unique across Females) Null

Does anyone have a way to be able to do this?

Pankaj Chamria, Software Programmer.

[Gideon Engelberth]

This may be difficult to add a lot of properties, but this should work for your specific example.

Dim groups = From g In (From p In personList _
Group p By p.City Into Group) _
Let SingleName As Boolean = (From r In g.Group Select r.Name Distinct).Count <= 1 _
Let SingleAge As Boolean = (From r In g.Group Select r.Age Distinct).Count <= 1 _
Select New Person() With {.Sex = g.Sex, _
.Name = If(SingleName, g.Group.First.Name, Nothing), _
.Age = If(SingleAge, g.Group.First.Age, Nothing)}
For Each g In groups
Console.WriteLine("{0}, {1}, {2}", g.Sex, g.Age, g.Name)
Next

[Gideon Engelberth]

Upon further consideration, I was able to extract the logic for checking properties for the same value within a group into an extension method. This will simplify the query a little bit.

Dim lcGroups = From g In (From p In personList _
Group By p.Sex Into Group) _
Select New Person() With {.Sex = g.Sex, _
.Name = g.Group.SameOrDefault(Function(p) p.Name), _
.Age = g.Group.SameOrDefault(Function(p) p.Age)}

<System.Runtime.CompilerServices.Extension()> _
Public Function SameOrDefault(Of TInput, TOutput)(ByVal items As IEnumerable(Of TInput), _
ByVal valueSelector As Func(Of TInput, TOutput)) As TOutput
Return SameOrDefault(items, valueSelector, EqualityComparer(Of TOutput).Default)
End Function

<System.Runtime.CompilerServices.Extension()> _
Public Function SameOrDefault(Of TInput, TOutput)(ByVal items As IEnumerable(Of TInput), _
ByVal valueSelector As Func(Of TInput, TOutput), _
ByVal comparer As IEqualityComparer(Of TOutput)) As TOutput
If items Is Nothing Then Return Nothing
If valueSelector Is Nothing Then
Throw New ArgumentNullException("valueSelector", "A value selector must be provided.")
End If
If comparer Is Nothing Then comparer = EqualityComparer(Of TOutput).Default

Dim result As TOutput = valueSelector(items.First())
For Each i In items
    If Not comparer.Equals(result, valueSelector(i)) Then Return Nothing
Next
Return result

End Function

The first overload will work for most cases, but if you wanted to compare complex types or use some non-default comparison, such as the magnitude of a vector, the second overload would come in handy.

[Pankaj Chamria]

Thanks a lot for taking time out to post a LINQ query for my requirement. I would try using this after converting it to C#:-)

Pankaj Chamria, Software Programmer.