在LINQ查询中计算天数总和

debugcn 发表于 Dev

Prashant Khadatkar

以下是我的数据表：

ResourceName    ContentName    ProjectName  PlanndStartDate PlannedEndDate   ActualStartDate    ActualEndDate   DesiredDate
ANIL            C-1479.doc     HP_WI_4141   2/24/2014       2/25/2014        2/24/2014          2/24/2014   2/23/2014
ANIL            C-1234.docx    HP_WI_3131   2/1/2014        2/12/2014        2/1/2014           2/13/2014   2/11/2014
CHETNA          C-1479.doc     HP_WI_4141   2/24/2014       2/25/2014        2/24/2014          2/24/2014   2/26/2014
CHETNA          C-1479.doc     HP_WI_4141   2/1/2014        2/12/2014        2/1/2014           2/13/2014   2/10/2014
CHETNA          C-14085.xlsx   HP_WI_5151   2/14/2014       2/28/2014        2/14/2014          2/26/2014   2/26/2014
GAURAV YADAV    C-1479.doc     HP_WI_4141   2/24/2014       2/25/2014        2/24/2014          2/24/2014   2/25/2014
GAURAV YADAV    C-1479.doc     HP_WI_4141   2/1/2014        2/12/2014        2/1/2014           2/13/2014   2/15/2014
GAURAV YADAV    C-14085.xlsx   HP_WI_5151   2/14/2014       2/28/2014        2/14/2014          2/26/2014   2/28/2014
NITIN           C-14077.pdf    HP_WI_2121   2/1/2014        2/12/2014        2/1/2014           2/13/2014   2/13/2014
SRINIVAS        C-14085.xlsx   HP_WI_5151   2/14/2014       2/28/2014        2/14/2014          2/26/2014   2/25/2014

现在，通过使用LINQ查询，我必须生成以下结果。

ResourceName    ContentName ProjectName PlanndStartDate PlannedEndDate  ActualStartDate ActualEndDate   DesiredDate TotalDays   GroupDays
ANIL            C-1479.doc  HP_WI_4141  2/24/2014       2/25/2014       2/24/2014        2/24/2014      2/23/2014        -1      -1
ANIL            C-1234.docx HP_WI_3131  2/1/2014        2/12/2014       2/1/2014         2/13/2014      2/11/2014        -2      -2
CHETNA          C-1479.doc  HP_WI_4141  2/24/2014       2/25/2014       2/24/2014        2/24/2014      2/26/2014         2      -1
CHETNA          C-1479.doc  HP_WI_4141  2/1/2014        2/12/2014       2/1/2014         2/13/2014      2/10/2014        -3      -1
CHETNA          C-14085.xlsxHP_WI_5151  2/14/2014       2/28/2014   2/14/2014                 2/26/2014      2/26/2014    0       0
GAURAV YADAV    C-1479.doc  HP_WI_4141  2/24/2014       2/25/2014       2/24/2014        2/24/2014      2/25/2014         1       3
GAURAV YADAV    C-1479.doc  HP_WI_4141  2/1/2014        2/12/2014       2/1/2014         2/13/2014      2/15/2014         2       3
GAURAV YADAV    C-14085.xlsxHP_WI_5151  2/14/2014       2/28/2014   2/14/2014       2/26/2014        2/28/2014            2       2
NITIN           C-14077.pdf HP_WI_2121  2/1/2014        2/12/2014       2/1/2014         2/13/2014      2/13/2014         0       0
SRINIVAS        C-14085.xlsxHP_WI_5151  2/14/2014       2/28/2014   2/14/2014            2/26/2014       2/25/2014       -1      -1

现在，结果应按“ ResouceName”，“ ContentName”和“ ProjectName”分组，并且“ TotalDays”列是“ DesireEndDate”和“ ActualEndDate”的差，而“ GroupDays”列是“ TodalDays”的总和。

请建议我该怎么做。谢谢。

蒂姆·施密特（Tim Schmelter）

GroupBy 和一个循环：

var groups = from row in table.AsEnumerable()
             let ResouceName = row.Field<string>("ResouceName")
             let ContentName  = row.Field<string>("ContentName") 
             let ProjectName  = row.Field<string>("ProjectName")
             group row by new{ ResouceName, ContentName, ProjectName } into Group
             select Group;

var tblResult = table.Clone();
tblResult.Columns.Add("TotalDays", typeof(int));
tblResult.Columns.Add("GroupDays", typeof(int);

foreach (var group in groups)
{ 
    int GroupDays = group.Sum(r => (r.Field<DateTime>("DesiredDate") - r.Field<DateTime>("ActualEndDate")).Days);
    foreach(DataRow row in group)
    {
        DateTime PlanndStartDate = row.Field<DateTime>("PlanndStartDate");
        DateTime PlannedEndDate = row.Field<DateTime>("PlannedEndDate");
        DateTime ActualStartDate = row.Field<DateTime>("ActualStartDate"); 
        DateTime ActualEndDate = row.Field<DateTime>("ActualEndDate");
        DateTime DesiredDate = row.Field<DateTime>("DesiredDate");
        TimeSpan Total = DesiredDate - ActualEndDate;
        tblResult.Rows.Add(group.Key.ResouceName, group.Key.ContentName, group.Key.ProjectName, PlanndStartDate, PlannedEndDate, ActualStartDate, ActualEndDate, DesiredDate, Total.Days, GroupDays);
    }
}

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。