CODEWITHSUNDEEP
c#linqdatatable
sandeep.mishra
sandeep.mishra

Reputation: 825

How to group a list and display a inner list in a datatable by linq

Hi I have a list like below

var data2 = dt.AsEnumerable().Select(x => new
            {
                SubTask = x.Field<string>("subtask"),
                Name = x.Field<string>("FirstName"),
                Date = x.Field<string>("Day") + ", " + x.Field<string>("Date"),
                Hour = System.Text.Encoding.UTF8.GetString(x.Field<System.Byte[]>("EmpHours"))
            });

My list is like below

Name   Date           Hour        Subtask
----    ------      -------      ----------
a      2018-11-01       1            task1
a      2018-11-01       2           task2
b      2018-11-01      'PTO'        'PTO'
c       2018-11-01      5            design
c       2018-11-01      3            coding

I want to group by date and name and display the result in a innerlist inside the main list.

I wanted to group by date and name and then sum the hour column for each name. The hour column is string type 'It may contain string 'PTO' or number like '2'.

I tried below query

var data2 = dt.AsEnumerable().Select(x => new
            {
                SubTask = x.Field<string>("subtask"),
                Name = x.Field<string>("FirstName"),
                Date = x.Field<string>("Day") + ", " + x.Field<string>("Date"),
                Hour = System.Text.Encoding.UTF8.GetString(x.Field<System.Byte[]>("EmpHours")),
                EmpList=  (from grp in dt.AsEnumerable()
                           .GroupBy(row => new {
                               Name = row.Field<string>("Name"), 
                               Date = row.Field<string>("Date") 
                           }).Select(g => new { Date = g.Key.Date, Name = g.Key.Name, Hour = g.Sum(i => i.Field<decimal>("Hour"));

            })

 I want output like below table 




  Name   Date           Hour        Subtask
    ----    ------      -------      ----------
    a      2018-11-01      3           task1.task2    
    b      2018-11-01      'PTO'       'PTO'
    c       2018-11-01      8          design,coding

can anyone help me on this?

Upvotes: 0

Views: 60

Answers (2)

Jamiec
Jamiec

Reputation: 136239

Basically what you're trying to do will require you to know if Hour is numeric and also group on that. The below would seem to do what you're after

 var result = data.GroupBy(x => new { 
                   x.Name,                                  // Group on Name
                   x.Date,                                  // And date
                   IsHourNumeric=x.Hour.All(Char.IsNumber)  // And whether Hour is numeric
             })
            .Select(g => new {
              Name = g.Key.Name,
              Date = g.Key.Date,
              Hour = g.Key.IsHourNumeric 
                       ? g.Select(x => int.Parse(x.Hour)).Sum().ToString() // If hour was numeric sum
                       : g.First().Hour, // Otherwise just take the first item ?!?
              SubTasks = g.Select(x => x.SubTask)
            });

A live example with your data is here: https://rextester.com/DPJZUH68065

Upvotes: 1

gkumar
gkumar

Reputation: 1

apply linq group by after your initial results (added .TOList())

var data2 = dt.AsEnumerable().Select(x => new
        {
            SubTask = x.Field<string>("subtask"),
            Name = x.Field<string>("FirstName"),
            Date = x.Field<string>("Day") + ", " + x.Field<string>("Date"),
            Hour = System.Text.Encoding.UTF8.GetString(x.Field<System.Byte[]>("EmpHours"))
        }).ToList();

Now apply group by to this result like below example:

var result = data2.GroupBy(n => new { n.Name, n.Date})
        .Select(cl => new Result
        {
            Name = cl.First().Name,
            Date = cl.First().Date,
            Hours = cl.Sum(c => c.Hour),
        }).ToList();

Upvotes: 0

Related Questions