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

VistarTechnologiesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.