Linq for sum of distinct addresses

Question

I have a DataTable of Products. Each product has a weight and a return address. The return address is comprised of 7 fields.

I need to cycle through the distinct addresses and sum up the total weight of product.

Example table would look like this...

Product weight  address1    address2    address3            city            state               postcode    country
A123    6       House       1st Street  some place          a city          a state             AB1 2CD     GB
A456    3       House       1st Street  some place          a city          a state             AB1 2CD     GB
A789    4       House       1st Street  some place          a city          a state             AB1 2CD     GB
A123    6       House2      2st Street  another place       another city    another state       EF2 3GH     GB
A456    3       House2      2st Street  another place       another city    another state       EF2 3GH     GB
A789    4       House2      2st Street  another place       another city    another state       EF2 3GH     GB

I would have 2 addresses returning a weight of 13.

I only need to group by the address fields (not product) and sum the weight by the address. I also need to return the country as well as the summed weight.

Is this possible using linq? Or would I be better using a SqlDataAdaptor on the DataTable? I know how I could do with with the SqlDataAdaptor but I don't know how to do with Linq and I'm guessing linq would be better for overhead?

It can be done using LINQ, but question is: What have you tried? — MarcinJuraszek, Mar 18 '13 at 10:28

Oliver · Answer 1 · 2013-03-18T10:46:29.153

The GroupBy() will group all of the products into sub-collections per distinct address. The Select() is then totalling up the weight of each sub-collection to provide the total weight.

var totals = products
        .GroupBy(p => new 
        { 
            address1 = p.Field<string>("address1"),
            address2 = p.Field<string>("address2"),
            address3 = p.Field<string>("address3"),
            city = p.Field<string>("city"),
            state = p.Field<string>("state"),
            postcode = p.Field<string>("postcode"),
            country = p.Field<string>("country")
        })
        .Select(g => new 
        {
             Total = g.Sum(p => p.Field<int>("weight"),
             Country = g.Key.country
        });

Example use:

foreach (var address in totals)
{
    Console.WriteLine(string.Format("Country: {0}, Weight: {1}", address.Country, address.Total));
}

products is a DataTable, so you should use Linq to DataTable here :) — Sergey Berezovskiy, Mar 18 '13 at 10:31

Sergey Berezovskiy · Accepted Answer · 2013-03-18T11:23:40.053

2

Group table rows by all address fields, and the calculate sum for each group:

var query = 
    from p in table.AsEnumerable()
    group p by new {
         Address1 = p.Field<string>("address1"),
         Address2 = p.Field<string>("address2"),
         Address3 = p.Field<string>("address3"),
         City = p.Field<string>("city"),
         State = p.Field<string>("state"),
         Postcode = p.Field<string>("postcode"),
         Country = p.Field<string>("country")
    } into g
    select new { 
        Address = g.Key, 
        TotalWeight = g.Sum(x => x.Field<int>("weight"))
    };

That will give you sequence of anonymous objects, which will have all address fields in Address property and sum of weights in TotalWeight property.

edited Mar 18 '13 at 11:23

answered Mar 18 '13 at 10:29

Sergey Berezovskiy

232,247
41
429
459

this is great, + 1 but will mark previous as answer. However, for anyone looking at this from a search, both of these are correct answers! – Stuart Mar 18 '13 at 10:55
@Stuart strange decision, but you are the OP :) – Sergey Berezovskiy Mar 18 '13 at 10:57
1

I've changed my mind. This is more correct as an answer with `AsEnumerable()` – Stuart Mar 18 '13 at 11:20
@Stuart you are the OP :) you can change your mind. But core difference here is query syntax (which looks more readable in this particular case, and usage of anonymous object, which holds all fields related to address) – Sergey Berezovskiy Mar 18 '13 at 11:23

Linq for sum of distinct addresses

2 Answers2

Linked