Group By multiple Columns in DataTable using LINQ in C# and VB.Net

Last Reply 4 months ago By SUJAYS

Posted 4 months ago

I am not asking like that . This is my datatable 

DataTable dt = GetDataTableFromExcel();
List<DataTable> dts = dt.AsEnumerable()
                .GroupBy(row => row.Field<string>("OUTLET NAME "))
                .Select(g => g.CopyToDataTable()).ToList();

As it is selecting based on Outlet name, here i want to select based another column too using C# with Linq query. I tried like this but not working.

DataTable dt = GetDataTableFromExcel();
List<DataTable> dts = dt.AsEnumerable()
                .GroupBy(row => row.Field<string>("OUTLET NAME ","Occasion"))
                .Select(g => g.CopyToDataTable()).ToList();

 

Posted 4 months ago Modified on 4 months ago

Check the below example.

C#

DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                    new DataColumn("Name", typeof(string)),
                    new DataColumn("Country",typeof(string)) });
dt.Rows.Add(1, "John Hammond", "United States");
dt.Rows.Add(2, "Mudassar Khan", "India");
dt.Rows.Add(3, "Suzanne Mathews", "France");
dt.Rows.Add(4, "Robert Schidner", "Russia");

var result = from rows in dt.AsEnumerable()
                group rows by new { Name = rows["Name"], Country = rows["Country"] } into grp
                select grp;

List<DataTable> dts = new List<DataTable>();
foreach (var item in result)
{
    dts.Add(item.CopyToDataTable());
}

VB.Net

Dim dt As DataTable = New DataTable()
dt.Columns.AddRange(New DataColumn(2) {
                    New DataColumn("Id", GetType(Integer)),
                    New DataColumn("Name", GetType(String)),
                    New DataColumn("Country", GetType(String))})
dt.Rows.Add(1, "John Hammond", "United States")
dt.Rows.Add(2, "Mudassar Khan", "India")
dt.Rows.Add(3, "Suzanne Mathews", "France")
dt.Rows.Add(4, "Robert Schidner", "Russia")
Dim result = From rows In dt.AsEnumerable()
        Group rows By Key = New With {.Name = rows("Name"), .Country = rows("Country")} Into Group
        Select Group
Dim dts As List(Of DataTable) = New List(Of DataTable)()
For Each item In result
    dts.Add(item.CopyToDataTable())
Next

 


Posted 4 months ago

I got the ouput from this.

var result = from rows in dt.AsEnumerable()
             group rows by new { OUTLETNAME = rows["OUTLET NAME "], Occasion = rows["Occasion"] } into grp
             select grp;