将C＃中的Linq加入SQL [英] Group By Sum Linq to SQL in C#

问题描述

我有一个视图在我的名为view_ProjectTimeSummary的数据库中，它包含以下字段：

pre $ string $ user
string_ProjectDescription
datetime_Date
double_Hours

我有一个方法可以接受to和from date参数，并首先创建这个List<> ：

 列表< view_UserTimeSummary> view_UserTimeSummaryToReturn = 
（来自linqtable_UserTimeSummaryView 
 in datacontext_UserTimeSummary.GetTable< view_UserTimeSummary>（）
 where linqtable_UserTimeSummaryView.datetime_Week< = datetime_To 
&&& linqtable_UserTimeSummaryView.datetime_Week> = datetime_From 
选择linqtable_UserTimeSummaryView）.ToList< view_UserTimeSummary>（）; 
  

在返回List（用作datagridview的数据源）之前，我使用过滤string_UserDescription字段一个同名的参数：

pre $ code> if（string_UserDescription！=）
{
view_UserTimeSummaryToReturn =
（from c in view_UserTimeSummaryToReturn
where c.string_UserDescription == string_UserDescription
select c）.ToList< view_UserTimeSummary>（）;
}

返回view_UserTimeSummaryToReturn;

如何操作生成的List<>以显示 sum

例如，该用户和项目之间的double_Hours字段在to和from日期参数之间带有以下字段的Lis​​t<>

  string_UserDescription 
 string_ProjectDescription 
 double_SumOfHoursBetweenToAndFromDate 
 
 code> 

我是对的，这意味着我必须返回不同类型的List<>（因为它的字段少于view_UserTimeSummary）？

我已经读过，得到的总和就像'group / by / into b'，但不明白这个语法是如何工作的解决方案...有人可以帮我吗？

感谢
史蒂夫

解决方案<

  public class GroupedRow 
 {
 public string UserDescription {get; set;} 
 public string ProjectDescription {get; set;} 
 public double SumOfHoursBetweenToAndFromDate {get; set;} 
} 
  

由于您已经应用了过滤功能， g剩下要做的是group。

  List< GroupedRow>结果= 
（
源于源
中的行）将新的{row.UserDescription，row.ProjectDescription}划分为g 
选择新的GroupedRow（）
 {
 UserDescription = g.Key.UserDescription，
 ProjectDescription = g.Key.ProjectDescription，
 SumOfHoursBetweenToAndFromDate = g.Sum（x => x.Hours）
} 
） .ToList（）; 
  

（或其他语法）

 列表与LT; GroupedRow> result = source 
 .GroupBy（row => new {row.UserDescription，row.ProjectDescription}）
 .Select（g => new GroupedRow（）
 {
 UserDescription = g.Key.UserDescription，
 ProjectDescription = g.Key.ProjectDescription，
 SumOfHoursBetweenToAndFromDate = g.Sum（x => x.Hours）
}）
 .ToList ）; 
  

Really stuck with Linq to SQL grouping and summing, have searched everywhere but I don't understand enough to apply other solutions to my own.

I have a view in my database called view_ProjectTimeSummary, this has the following fields:

string_UserDescription
string_ProjectDescription
datetime_Date
double_Hours

I have a method which accepts a to and from date parameter and first creates this List<>:

List<view_UserTimeSummary> view_UserTimeSummaryToReturn = 
             (from linqtable_UserTimeSummaryView
              in datacontext_UserTimeSummary.GetTable<view_UserTimeSummary>()
              where linqtable_UserTimeSummaryView.datetime_Week <= datetime_To
              && linqtable_UserTimeSummaryView.datetime_Week >= datetime_From
              select linqtable_UserTimeSummaryView).ToList<view_UserTimeSummary>();

Before returning the List (to be used as a datasource for a datagridview) I filter the string_UserDescription field using a parameter of the same name:

if (string_UserDescription != "")
        {
            view_UserTimeSummaryToReturn = 
                        (from c in view_UserTimeSummaryToReturn
                         where c.string_UserDescription == string_UserDescription
                         select c).ToList<view_UserTimeSummary>();
        }

return view_UserTimeSummaryToReturn;

How do I manipulate the resulting List<> to show the sum of the field double_Hours for that user and project between the to and from date parameters (and not separate entries for each date)?

e.g. a List<> with the following fields:

string_UserDescription
string_ProjectDescription
double_SumOfHoursBetweenToAndFromDate

Am I right that this would mean I would have to return a different type of List<> (since it has less fields than the view_UserTimeSummary)?

I have read that to get the sum it's something like 'group / by / into b' but don't understand how this syntax works from looking at other solutions... Can someone please help me?

Thanks Steve

解决方案

Start out by defining a class to hold the result:

public class GroupedRow
{
  public string UserDescription {get;set;}
  public string ProjectDescription {get;set;}
  public double SumOfHoursBetweenToAndFromDate {get;set;}
}

Since you've already applied filtering, the only thing left to do is group.

List<GroupedRow> result =
(
  from row in source
  group row by new { row.UserDescription, row.ProjectDescription } into g
  select new GroupedRow()
  {
    UserDescription = g.Key.UserDescription,
    ProjectDescription = g.Key.ProjectDescription,
    SumOfHoursBetweenToAndFromDate = g.Sum(x => x.Hours)
  }
).ToList();

(or the other syntax)

List<GroupedRow> result = source
  .GroupBy(row => new {row.UserDescription, row.ProjectDescription })
  .Select(g => new GroupedRow()
  {
    UserDescription = g.Key.UserDescription,
    ProjectDescription = g.Key.ProjectDescription,
    SumOfHoursBetweenToAndFromDate = g.Sum(x => x.Hours)
  })
  .ToList();

这篇关于将C＃中的Linq加入SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！