C# Linq Group by on Multiple Columns

C# Linq Group By on multiple columns

var consolidatedChildren =
    from c in children
    group c by new
    {
        c.School,
        c.Friend,
        c.FavoriteColor,
    } into gcs
    select new ConsolidatedChild()
    {
        School = gcs.Key.School,
        Friend = gcs.Key.Friend,
        FavoriteColor = gcs.Key.FavoriteColor,
        Children = gcs.ToList(),
    };

---

var consolidatedChildren =
    children
        .GroupBy(c => new
        {
            c.School,
            c.Friend,
            c.FavoriteColor,
        })
        .Select(gcs => new ConsolidatedChild()
        {
            School = gcs.Key.School,
            Friend = gcs.Key.Friend,
            FavoriteColor = gcs.Key.FavoriteColor,
            Children = gcs.ToList(),
        });

Group By Multiple Columns

Use an anonymous type.

Eg

group x by new { x.Column1, x.Column2 }

LINQ - GroupBy multiple columns and merge the result

First try to get the columns you need projecting the result in an anonymous type:

var query=  from r in output
            let columns= r.Split(';')
            select new { c1 =columns[0], c2 =columns[1], c3 = columns[2] ,c5=columns[4]};

And then create the groups but now using the anonymous object you define in the previous query:

var result= query.GroupBy(e=>new {e.c1, e.c2, e.c3})
                 .Select(g=> new {SurName=g.Key.c1,
                                  Name=g.Key.c2,
                                  Address=g.Key.c3,
                                  Groups=String.Join(",",g.Select(e=>e.c4)});

I know I'm missing some columns but I think you can get the idea.

PS: The fact I have separated the logic in two queries is just for readability propose, you can compose both queries in one but that is not going to change the performance because LINQ use deferred evaluation.

Linq group by with multiple columns

It seems like you need grouping on Country name as it is repeating in the data but with different id (which is strange) in that case you need to group and select the first one :

var result1 = myRepoOutput.GroupBy(x => x.Country)
                          .Select(b => b.First());

Linq GroupBy multiple columns with Projection to new object

The First() could not be translated by Linq to SQL. You need to use FirstOrDefault().

...
PracticeArea = am.FirstOrDefault().PracticeArea,
Location = am.FirstOrDefault().Location,
Publication = am.FirstOrDefault().Publication,
...

Edit Proposed solution to avoid errors:

Required access to: context.PracticeAreas, context.Locations and context.Publications

IQueryable<RelatedOrganisationMentions> relatedOrganisationMentions = collection
    .GroupBy(
        m => new { m.LocationId, m.PublicationId, m.PracticeAreaId },
        (k, g) => new{ k.LocationId, k.PublicationId, k.PracticeAreaId, Count = g.Sum(x => x.Count) }
    )
    .Select(am => new RelatedOrganisationMentions
    {
        PracticeArea = context.PracticeAreas.FirstOrDefault(x => x.Id == am.PracticeAreaId),
        Location = context.Locations.FirstOrDefault(x => x.Id == am.LocationId),
        Publication = context.Publications.FirstOrDefault(x => x.Id == am.PublicationId),
        PracticeAreaId = am.PracticeAreaId,
        PublicationId = am.PublicationId,
        LocationId = am.LocationId,
        Count = x.Count
    })
    .OrderBy(r => r.Publication.Description)
    .ThenBy(r => r.PracticeArea.Description);

Group By Multiple Column In LINQ in C#

Books = g.ToList() //Actually this line of is not working

Probably because Books property is type of List<string>, not List<ActualClass>.

Can you please try this query, I added b.Select(bn => bn.BookName).ToList() to extract only names of books:

var books = new List<ActualClass>
{
   new ActualClass { BookName = "A", DateOfIssue = new DateTime(2015, 10, 10, 10, 10, 0), IssuerName = "1" },
   new ActualClass { BookName = "B", DateOfIssue = new DateTime(2015, 10, 10, 10, 10, 0), IssuerName = "1" },
   new ActualClass { BookName = "C", DateOfIssue = new DateTime(2015, 10, 10, 10, 10, 0), IssuerName = "1" },
   new ActualClass { BookName = "D", DateOfIssue = new DateTime(2015, 10, 10, 10, 10, 0), IssuerName = "2" },
   new ActualClass { BookName = "E", DateOfIssue = new DateTime(2015, 10, 10, 12, 10, 0), IssuerName = "2" },
   new ActualClass { BookName = "F", DateOfIssue = new DateTime(2015, 10, 10, 12, 10, 0), IssuerName = "2" }
};

var result = books.GroupBy(x => new { x.IssuerName, x.DateOfIssue })
                .Select(b => new ViewModel
                {
                    Books = b.Select(bn => bn.BookName).ToList(),
                    // Accessing to DateOfIssue and IssuerName from Key.
                    DateOfIssue = b.Key.DateOfIssue,
                    IssuerName = b.Key.IssuerName
                });

I grouped by: x.IssuerName, x.DateOfIssue. I did that by passing anonymous type in GroupBy() with following manner: x => new { x.IssuerName, x.DateOfIssue }.

Now they are in key and you can access to IssuerName and DateOfIssue from KEY in SELECT statement like in following: b.Key.IssuerName and b.Key.DateOfIssue.

LINQ Group By multiple columns and also count

Use g.Count() instead g.ToList().Count().

var journeyEvents = from ad in AccelerometerData
                    join ae in AccelerometerEvents on ad.AccelerometerDataId equals ae
                        .AccelerometerData.AccelerometerDataId
                    where ad.Device.DeviceId == journey.Device.DeviceId && ae.TimeStamp >= journey.StartDateTime &&
                          ae.TimeStamp <= journey.EndDateTime
                    group ae by new
                    {
                        ae.EventType,
                        ae.Level
                    } into g
                    select new
                    {
                        EventType = new JourneyEventType { JourneyEventTypeId = g.Key.EventType },
                        Level = g.Key.Level,
                        Count = g.Count()
                    };

journey.JourneyEvents = journeyEvents.ToList();

---

Related Topics