locked
GroupBy on Mutiple Columns of an AsEnumerable DataTable RRS feed

  • Question

  • Is it possible to have a Linq GroupBy that specifies multiple columns in an AsEnumerable DataTable.  I have successfully grouped based on a single column using s.Field<string>(colName) as the GroupBy parameter, but can't find a way to specify multiple columns.   See the desired code below.  Thanks.

    string col1 = "col name 1";
    string col2 = "col Name 2";
    
    var myDataTableAE = myDataTable.AsEnumerble();
    var group = myDataTableAE..GroupBy(s => new { s.Field<string>(col1), s.Field<string>(col2) })
    .Where(g => g.Count() > 1)
    .ToList();

    Thursday, March 7, 2019 7:49 PM

Answers

  • See if the following helps

    var objectTable = new DataTable();
    
    objectTable.Columns.Add("resource_name", typeof(string));
    objectTable.Columns.Add("day_date", typeof(DateTime));
    objectTable.Columns.Add("actual_hrs", typeof(decimal));
    
    objectTable.Rows.Add(1, DateTime.Today, 1);
    objectTable.Rows.Add(1, DateTime.Today, 1);
    objectTable.Rows.Add(1, DateTime.Today, 1);
    objectTable.Rows.Add(2, DateTime.Today, 0);
    
    var results = objectTable
        .AsEnumerable()
        .GroupBy(row => new
        {
            ID = row.Field<string>("resource_name"),
            time1 = row.Field<DateTime>("day_date")
        })
        .Select(grp => new
        {
            resource_name1 = grp.Key.ID,
            day_date1 = grp.Key.time1,
            Sum = grp.Sum(r => r.Field<decimal>("actual_hrs"))
        });
    
    
    foreach (var item in results)
    {
        Console.WriteLine($"{item.resource_name1,5} {item.day_date1.ToShortDateString(),14} {item.Sum,5}");
    
    }
    
    Console.WriteLine();
    
    var resultsList = results.Where(data => data.Sum > 1).ToList();
    foreach (var item in resultsList)
    {
        Console.WriteLine($"{item.resource_name1,5} {item.day_date1.ToShortDateString(),14} {item.Sum,5}");
    }

        1       3/7/2019     3
        2       3/7/2019     0
    
        1       3/7/2019     3


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, March 7, 2019 11:37 PM

All replies

  • See if the following helps

    var objectTable = new DataTable();
    
    objectTable.Columns.Add("resource_name", typeof(string));
    objectTable.Columns.Add("day_date", typeof(DateTime));
    objectTable.Columns.Add("actual_hrs", typeof(decimal));
    
    objectTable.Rows.Add(1, DateTime.Today, 1);
    objectTable.Rows.Add(1, DateTime.Today, 1);
    objectTable.Rows.Add(1, DateTime.Today, 1);
    objectTable.Rows.Add(2, DateTime.Today, 0);
    
    var results = objectTable
        .AsEnumerable()
        .GroupBy(row => new
        {
            ID = row.Field<string>("resource_name"),
            time1 = row.Field<DateTime>("day_date")
        })
        .Select(grp => new
        {
            resource_name1 = grp.Key.ID,
            day_date1 = grp.Key.time1,
            Sum = grp.Sum(r => r.Field<decimal>("actual_hrs"))
        });
    
    
    foreach (var item in results)
    {
        Console.WriteLine($"{item.resource_name1,5} {item.day_date1.ToShortDateString(),14} {item.Sum,5}");
    
    }
    
    Console.WriteLine();
    
    var resultsList = results.Where(data => data.Sum > 1).ToList();
    foreach (var item in resultsList)
    {
        Console.WriteLine($"{item.resource_name1,5} {item.day_date1.ToShortDateString(),14} {item.Sum,5}");
    }

        1       3/7/2019     3
        2       3/7/2019     0
    
        1       3/7/2019     3


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, March 7, 2019 11:37 PM
  • Karen - Thanks loads.  You clearly answered my question and more.  Of real value to me.  Steve
    Friday, March 8, 2019 7:10 PM