Anand Anand - 16 days ago 6
C# Question

Group items and count

My table is like below

Main_ID | Child_ID | Activity |
1 |1 | Start
1 |2 | Stop
1 |3 | Stop
2 |1 | Start
2 |3 | Stop


What I'm looking for is like

Main_ID | Start | Stop
1 |1 |2
2 |1 |1


So, in first column I want to list all the Main_Ids and distinct Activities horizontally. After that I want to display the count of distinct Child_Ids under each activity for the Main_ID.

What I have tried is

var result = from q in db.Table
where q.Child_ID != null
group 1 by new { q.Main_ID,q.Child_ID, q.Activity } into g
select new MyList
{
Main_ID = g.Key.Main_ID,
Child_ID = g.Key.Child_ID,
Activity = g.Key.Activity,
Count = g.Count()
}
into p orderby p.Main_ID,p.Child_ID select p;


But with this I'm not getting what I have stated.

I have achieved for the Child_ID using following

var result2 = result.GroupBy(x => x.Child_ID)
.Select(grp => new MyList2
{
Child_ID = grp.Key,
StartCount = grp.Where(x => x.Activity == "Start")
.Select(x => x.Count).Cast<Int32>().FirstOrDefault(),
StopCount = grp.Where(x => x.Activity == "Stop")
.Select(x => x.Count).Cast<Int32>().FirstOrDefault()
});


Not sure how to display it against Main_ID

Answer

Considering this class:

public class Foo
{
    public int Main_ID {get;set;}
    public int Child_ID {get;set;}
    public string Activity {get;set;}
}

With this data:

var ls=new List<Foo>
    {
        new Foo{Main_ID=1,Child_ID=1,Activity="Start"},
        new Foo{Main_ID=1,Child_ID=2,Activity="Stop"},
        new Foo{Main_ID=1,Child_ID=3,Activity="Stop"},
        new Foo{Main_ID=2,Child_ID=1,Activity="Start"},
        new Foo{Main_ID=2,Child_ID=3,Activity="Stop"},
    };

You could do this:

var result = ls
    .GroupBy (g =>g.Main_ID)
    .Select (g =>new 
    {
       Main_ID = g.Key,
       Start = g.Where(s=>s.Actvity == "Start").Select(x => x.Child_ID).Distinct().Count(),
       Stop = g.Where(s=>s.Actvity == "Stop").Select(x => x.Child_ID).Distinct().Count(),
    } 
    ).ToList();

Result:

Main_ID Start Stop
1       1     2 
2       1     1