C# Datatable How to Group Like Rows

Question

I am still relatively new to C# and I was wondering if there is a built-in function to do the following:

-I am loading rows from a database into a DataGridView via a DataTable

-I have many rows with the same value in one column but a differing value in antoher column, EG:

NAME, PRICE
chair, 7.00
chair, 6.00
chair, 8.00
table, 15.00
table, 17.00
plate, 4.00
plate, 4.50

Does anyone have any knowledge on how to loop through each row, and take the cheapest price for each product and send it to another List/Object?

I have only started using DataTables and DataGridView elements today so I am a little uncertain the best way to proceed. Basically I want an end list containing one of each product with the cheapest price found next to it. EG

NAME, PRICE
chair, 6.00
table, 15.00
plate, 4.00

I am not sure if using an Object, List or Dictionary would be the best in this situation and was wondering if someone can shed some more light please. :)

Thanks for any help

score 4 · Accepted Answer · edited May 23 '17 at 12:09

4

Considering that you datatable looks like this:

var dt=new DataTable();
dt.Columns.Add("NAME",typeof(string));
dt.Columns.Add("PRICE",typeof(float));

dt.Rows.Add("chair", 7.00);
dt.Rows.Add("chair", 6.00);
dt.Rows.Add("chair", 8.00);
dt.Rows.Add("table", 15.00);
dt.Rows.Add("table", 17.00);
dt.Rows.Add("plate", 4.00);
dt.Rows.Add("plate", 4.50);

And you have an object that you want to present the information in like this:

public class Foo
{
    public string Name {get;set;}
    public float Price {get;set;}
}

The you can do a linq query like this to get the min price and the name:

var result= (
    from row in dt.AsEnumerable()
    group row by row.Field<string>("NAME") into g
    select new Foo
    {
        Name = g.Key,
        Price=g.Min (x =>x.Field<float>("PRICE"))
    }
).ToList();

This will get the following result:

chair 6 
table 15 
plate 4

Reference:

edited May 23 '17 at 12:09

Community

1
1

answered Oct 21 '14 at 06:25

Arion

31,011
10
70
88

That worked perfectly thank you! I need to investigate LINQ more as this is a far more efficient and simple solution compared to what I was intending to do. If I wanted to add more properties to the Foo Class and save them via the LINQ query can you advise how I would do that? – Aaron McKelvie Oct 21 '14 at 06:45
@AaronMcKelvie : No problem. I updated the answer with some resources for you to read up on – Arion Oct 21 '14 at 06:50
Thanks again Arion, You've been very helpful I'll check out those links! I was wondering if you had any advice on the above comment edit I made? – Aaron McKelvie Oct 21 '14 at 07:03

C# Datatable How to Group Like Rows

1 Answers1