Aggregation using Group by in LINQ

Question

5.00/5 (1 vote)

See more:

.NET3.0

.NET3.5

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?

Posted 17-Nov-08 2:16am

Pankaj Chamria

Updated 3-Jan-12 3:04am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gideon Engelberth · Answer 1 · 2008-11-18T05:40:00

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

Gideon Engelberth · Answer 2 · 2008-11-18T07:31:00

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 _<br />                          Group By p.Sex Into Group) _<br />               Select New Person() With {.Sex = g.Sex, _<br />                                         .Name = g.Group.SameOrDefault(Function(p) p.Name), _<br />                                         .Age = g.Group.SameOrDefault(Function(p) p.Age)}<br /><br /><System.Runtime.CompilerServices.Extension()> _<br />Public Function SameOrDefault(Of TInput, TOutput)(ByVal items As IEnumerable(Of TInput), _<br />                                                  ByVal valueSelector As Func(Of TInput, TOutput)) As TOutput<br />    Return SameOrDefault(items, valueSelector, EqualityComparer(Of TOutput).Default)<br />End Function<br /><br /><System.Runtime.CompilerServices.Extension()> _<br />Public Function SameOrDefault(Of TInput, TOutput)(ByVal items As IEnumerable(Of TInput), _<br />                                                  ByVal valueSelector As Func(Of TInput, TOutput), _<br />                                                  ByVal comparer As IEqualityComparer(Of TOutput)) As TOutput<br />    If items Is Nothing Then Return Nothing<br />    If valueSelector Is Nothing Then <br />        Throw New ArgumentNullException("valueSelector", "A value selector must be provided.")<br />    End If<br />    If comparer Is Nothing Then comparer = EqualityComparer(Of TOutput).Default<br /><br />    Dim result As TOutput = valueSelector(items.First())<br />    For Each i In items<br />        If Not comparer.Equals(result, valueSelector(i)) Then Return Nothing<br />    Next<br />    Return result<br />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.

Aggregation using Group by in LINQ

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0