CODEWITHSUNDEEP
c#linqgroup-by
Steven Sann
Steven Sann

Reputation: 578

linq group by two columns and get only rows with same group by values

I want to retrieve data by group two columns ( Parent_Id and Name ) using LINQ and get the result only the rows with the same group by values.

Child
---------
Id        Parent_Id      Name
1         1              c1
2         1              c2
3         2              c1 <-----
4         2              c1 <-----
5         3              c2
6         3              c3
7         4              c4 <-----

As you can see, for Parent_Id 1 and 2, Name are different. So, I don't what those rows.
The result I want is like

Parent_Id   Name
2           c1 
4           c4

What I have tried is

from c in Child
group c by new
    {
        c.Parent_Id,
        c.Name
    } into gcs
    select new Child_Model()
    {
        Parent_Id = gcs.Key.Parent_Id,
        Name= gcs.Key.Name
    };

But it return all rows.

Upvotes: 0

Views: 1394

Answers (4)

Cetin Basoz
Cetin Basoz

Reputation: 23807

Reduced to final edit as per Gert Arnold's request:

var result = from r in (from c in children
             where !children.Any(cc => cc.Id != c.Id &&
                cc.Parent_Id == c.Parent_Id &&
                cc.Name != c.Name)
             select new  {
                Parent_Id = c.Parent_Id,
                Name = c.Name
             }).Distinct().ToList()
             select new Child_Model
             {
                Parent_Id = r.Parent_Id,
                Name = r.Name
             };

Upvotes: 1

Microbubu Groner
Microbubu Groner

Reputation: 3

You can add a condition to filter result (groupName.Count() > 1):

from c in childs 
group c by new { c.Parent_Id, c.Name } into gcs 
where gcs.Count() > 1 
select new { gcs.Key.Parent_Id, gcs.Key.Name }

Upvotes: 0

Gert Arnold
Gert Arnold

Reputation: 109185

As you describe it you should group by Parent_id only and get the groups that have distinct Names:

var result = children
    .GroupBy(c => c.Parent_Id)
    .Where(g => g.Select(t => t.Name).Distinct().Count() == 1)
    .Select(g => new
    {
        Parent_Id = g.Key,
        Name = g.Select(c => c.Name).First()
    });

Upvotes: 2

lidqy
lidqy

Reputation: 2463

var myModel = Child.GroupBy( c => $"{c.Parent_Id}|{c.Name}",
          (k, list) => new Child_Model{
              Parent_Id = list.First().Parent_Id,
              Name = list.First().Parent_Id,
              Count = list.Count()})
          .Max (cm => cm.Count);

Upvotes: 0

Related Questions