首页 文章

Linq由父属性分组的子对象属性的总和

提问于
浏览 次
-2

几个小时之后，我一直在吵架这几个小时，似乎无法让它变得正确 . 通过OleDB连接加载大型数据集，以便使用DataSet和DataTables

所以我有以下数据表

我想要做的是获取Job.PeriodRequired分组的JobEstimate.Quantity的总和，其中Job.JobType =“Q”，Job.JobGroupCode =“GOLD”和JobEstimate.ProductCode =“10000”

到目前为止我得到的最好的是下面的代码，但只给出了第一个估算线的数量

var periodQuantity = from je in ds.JobEstimate
            join j in ds.Job on je.JobCode equals j.JobCode
            where je.ProductCode == "10000" &&
                j.JobType == "Q" &&
                j.JobGroupCode == "GOLD"
            group je by j.PeriodRequired into g
            select new { PeriodId = g.Key, Quantity = g.Sum(e => e.Quantity) };
c# linq sum grouping

1 回答

  • 1

    由于问题中DataType缺乏明确性，我试图在这里使用类来模拟它 . 请更改代码以适合您的数据类型

    public class Job
{
public string JobCode{get;set;}
public string JobName{get;set;}
public string PeriodRequired{get;set;}
public string JobType{get;set;}
public string JobGroupCode{get;set;}
public DateTime DateRequired{get;set;}
}

public class JobEstimate
{
public string JobCode{get;set;}
public string ProductCode{get;set;}
public int Qty {get;set;}
}

    例如数据

    var jobList = new List<Job>(){
    new Job{JobCode="abc1",JobName="JobName1",PeriodRequired="1",JobType="Q",JobGroupCode="GOLD"},
    new Job{JobCode="abc2",JobName="JobName1",PeriodRequired="2",JobType="Q",JobGroupCode="GOLD"},
    new Job{JobCode="abc3",JobName="JobName1",PeriodRequired="3",JobType="R",JobGroupCode="GOLD"},
    new Job{JobCode="abc4",JobName="JobName1",PeriodRequired="1",JobType="Q",JobGroupCode="GOLD"}
    };
var jobEstimateList = new List<JobEstimate>(){
        new JobEstimate(){JobCode="abc1",ProductCode="10000",Qty=12},
        new JobEstimate(){JobCode="abc1",ProductCode="10000",Qty=11},
        new JobEstimate(){JobCode="abc2",ProductCode="10000",Qty=12},
        new JobEstimate(){JobCode="abc3",ProductCode="10000",Qty=14},
        };

    您可以获得所需的结果

    var result = jobList.Join(jobEstimateList,
                job=> job.JobCode, 
                je=>je.JobCode,(job,je)=>new {
                                        JobCode=job.JobCode,
                                        JobName = job.JobName,
                                        JobType = job.JobType,
                                        JobGroupCode = job.JobGroupCode,
                                        ProductCode = je.ProductCode,
                                        PeriodRequired = job.PeriodRequired,
                                        Qty = je.Qty})
            .Where(x=>x.JobType=="Q" && x.JobGroupCode=="GOLD" && x.ProductCode=="10000")
            .GroupBy(x=> x.PeriodRequired)
            .Select(x=> new {PeriodId = x.Key,Qty= x.Sum(c=>c.Qty)});

    结果

    PeriodId | Qty
  1      | 23 
  2      | 12
    回复于

相关问题