• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 898
  • Last Modified:

C# Linq query grouping by multiple columns in DataTable

I have created the below DataTable in C#:

DataTable myTable = new DataTable();
myTable.Columns.Add("ID1", typeof(int));
myTable.Columns.Add("ID2", typeof(int));
myTable.Columns.Add("Value1", typeof(string));
myTable.Columns.Add("Value2", typeof(string));
myTable.Columns.Add("Date1", typeof(DateTime));

Open in new window


I am trying to get distinct values of ID1, ID2, and Value1, and the max values of Value2 and Date1.  I would use this query in SQL:

SELECT ID1,
       ID2,
       Value1,
       MAX(Value2),
       MAX(Date1)
FROM MyTable i
GROUP BY ID1, ID2, Value1

Open in new window


Having problems getting it done in Linq... all the examples I can find only group by a single column.  Trying to expand the grouping based on what I've found so far has gotten me here, and I'm getting the feeling I'm going down the wrong path with it:

var myLinqQuery = myTable.AsEnumerable()
                         .GroupBy(r1 => r1.Field<int>("ID1"), r2 => r2.Field<int>("ID2"))
                         .Select(g =>
                         {
                             var row = myTable.NewRow();

                             row["ID1"] = g.Select(r1 => r1.Field<int>("ID1"));
                             row["ID2"] = g.Select(r => r.Field<int>("ID2"));
                             row["Value1"] = g.Select(r => r.Field<string>("Value1"));
                             row["Value2"] = g.Max<string>(r => r.Field<string>("Value2"));

                             return row;
                         }).CopyToDataTable();

Open in new window

0
VistarTechnologies
Asked:
VistarTechnologies
1 Solution
 
Fernando SotoRetiredCommented:
Hi VistarTechnologies;

I have modified your code to work as your SQL. Please note that within the Linq query and more specifically within the Select clause you can create a collection of the same object that is being queried or a new class object or an Anonymous type but you can not ask it to execute a method such as myTable.NewRow() and fill it. Therefore I added new code to do the CopyToDataTable.
// Modify your query to act like the SQL you posted
var myLinqQuery = myTable.AsEnumerable()
                         .GroupBy(r1 => new { ID1 = r1.Field<int>("ID1"), ID2 = r1.Field<int>("ID2"), Value1 = r1.Field<string>("Value1") })
                         .Select(g => new
                         {
                             ID1 = g.Key.ID1,
                             ID2 = g.Key.ID2,
                             Value1 = g.Key.Value1,
                             Value2 = g.OrderBy(r => r.Field<string>("Value2")).Select(r => r.Field<string>("Value2") ).Last(),
                             Date1 = g.Select(r => r.Field<DateTime>("Date1")).Max()
                         }).ToList();

// Holds the new rows
DataTable newTable = new DataTable();
// Holds the DataRows created below
List<DataRow> newRows = new List<DataRow>();
if (myLinqQuery != null)
{
    foreach (var element in myLinqQuery)
    {
        var row = myTable.NewRow();
        row["ID1"] = element.ID1;
        row["ID2"] = element.ID2;
        row["Value1"] = element.Value1;
        row["Value2"] = element.Value2;
        row["Date1"] = element.Date1;
        newRows.Add(row);
    }
    newTable = newRows.CopyToDataTable();
}

Open in new window

Here is some info on Data Transformations with LINQ.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now