CODEWITHSUNDEEP
linq
whihathac
whihathac

Reputation: 1761

Aggregation in LINQ

I am new to LINQ. I am stuck with a very silly problem

Name    Subjects    Role
----    --------    --------

A       Math        Student
A       English     Student
B       Math        Student 
B       English     Student
C       Math        Student
C       Math        Admin

I need result as 

Name    Subjects        Role
----    --------        --------
A       Math, English   Student
B       Math, English   Student
C       Math            Student
C       Math            Admin

I am confused as to how to go about this problem. This is simple in SQL where I can do a groupby clause and get the comma seperated values via a function.

Can someone please help me out?

Edited: The three columns are from 3 different sources. I have updated the resultant table. Thanks for your help in advance!

Upvotes: 1

Views: 746

Answers (2)

Abdul Munim
Abdul Munim

Reputation: 19217

Try this:

var grouped = classes.GroupBy(g => new {Name = g.Name, Role = g.Role}).Select(
    s =>
    new
        {
            Name = s.Key,
            Subjects = s.Select(x => x.Subject).Aggregate("", (current, se) => current + (", " + se)),
            Role = s.Select(x => x.Role).First()
        });

var result = grouped.Select(s => new
                                     {
                                         s.Name, 
                                         Subjects = s.Subjects.Substring(2), 
                                         s.Role
                                     }).ToList();

This will put your subjects in a comma separated string.

Hope this helps.

Upvotes: 1

Lasse Espeholt
Lasse Espeholt

Reputation: 17782

I don't have your code but it should look like this:

var grouped = from element in yourList
              group element by element.Name into g
              select new
              {
                  Name = g.Key,
                  Subjects = g.Select(e => e.Subject),
                  // Assuming they are identical when they have the same name
                  Role = g.First().Role
              };

Upvotes: 2

Related Questions