CODEWITHSUNDEEP
c#linq
ar.gorgin
ar.gorgin

Reputation: 5002

How to select multiple columns from datatable in linq group by?

I have a DataTable, and i want to grouped with a column .

I use this code for it :

DataTable  dtList = list1.Items.GetDataTable();
         var grouped = from row in dtList.AsEnumerable()
                      group row by row["T"] into valueGroup
                      select new { Value = valueGroup.Key, ID = valueGroup.Max(id => id["ID"]) };

My problem is : it is select two column(id,value). I want select multiply column.

How to select other column of dtlist ?

Upvotes: 3

Views: 28338

Answers (2)

Lukasz M
Lukasz M

Reputation: 5723

If you want to select multiple columns to be groupped by, try something like this:

DataTable  dtList = list1.Items.GetDataTable();
var grouped = from row in dtList.AsEnumerable()
                      group row by new { T = row["T"], U = row["U"] } into valueGroup
                      select new { Value = valueGroup.Key, ID = valueGroup.Max(id => id["ID"]) };

This will give you multiple columns returned as an anonymous type objects with T and U properties accessible in the Value field of the return set.

I've tested this and it seems to work on a sample data set.

It's based on a solution described here: http://social.msdn.microsoft.com/Forums/en-US/linqprojectgeneral/thread/54f6ac70-e74c-4d9d-a9ee-6373714c0755.

Edit

When using group by clause all items are divided into groups and for each group only one item is returned, so in order to refer to a column, you have to either put it in the group by clause or use an aggregation method on it (like you did with ID field and Max() method).

However, if you want to make the groups, get the maximum ID for each group and then retrieve other columns for those items (with maximum ID value in each group) you can use a join with the same table. This code shows how to do it in your case for sample T and U columns. It seems to work correctly on a test data set:

var dtListEnumerable = dtList.AsEnumerable();
//group items and get max ID for each group
var groupedTemp = from row in dtListEnumerable
          group row by row["T"] into valueGroup
          select new { Value = valueGroup.Key, ID = valueGroup.Max(id => id["ID"]) };

//join to get the rest of columns for each item with max ID returned for each group
var grouped = from groupedItem in groupedTemp
          join row in dtListEnumerable on groupedItem.ID equals row["ID"]
              into tablesJoined
          from row in tablesJoined.DefaultIfEmpty()
          select new
          {
              ID = groupedItem.ID,
              T = row["T"], 
              U = row["U"]
          };

If you need any help with this, let me know. If it does not return data you want to retrieve, please give some more details about it.

Upvotes: 2

Hassan Boutougha
Hassan Boutougha

Reputation: 3919

to select multiple column (column1, column2, colummn3) that are in your dtList:

DataTable  dtList = list1.Items.GetDataTable();
var grouped = from row in dtList.AsEnumerable()
                  group row by row["T"] into valueGroup
                  select new {
                         Value = valueGroup.Key,
                         ID = valueGroup.Max(id => id["ID"]),
                         col1 = row["column1"].ToString(),
                         col2 = row["column2"].ToString(),
                         col3 = row["column3"].ToString()
                          };

Upvotes: 1

Related Questions