LINQ用于不同地址的总和[英] Linq for sum of distinct addresses

本文是小编为大家收集整理的关于LINQ用于不同地址的总和的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有A DataTable产品.每种产品都有一个重量和返回地址. 返回地址由7个字段组成.

我需要循环浏览不同的地址并总结产品的总重量.

示例表看起来像这样...

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

我将有2个地址返回重量为13.

我只需要按地址字段(而非产品)进行分组,然后按地址总和.我还需要返回该国以及总的体重.

使用linq可以吗?还是在DataTable上使用SqlDataAdaptor我会更好? 我知道如何处理SqlDataAdaptor,但我不知道该如何处理Linq,我猜Linq会更好吗?

推荐答案

由所有地址字段组排行,每个组的计算总和:

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"))
    };

将为您提供匿名对象的顺序,该对象将在地址属性中具有所有地址字段和总重量属性中的权重总和.

其他推荐答案

GroupBy()将将所有产品分为每个不同地址的子收集. 然后,Select()总计每个子收集的重量以提供总重量.

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
        });

示例使用:

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

本文地址:https://www.itbaoku.cn/post/1557164.html

问题描述

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?

推荐答案

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.

其他推荐答案

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));
}