CODEWITHSUNDEEP
sqllinqgroup-bysum
vitalsigns.sa
vitalsigns.sa

Reputation: 179

Group By Sum Linq to SQL in C#

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

Upvotes: 17

Views: 57834

Answers (1)

Amy B
Amy B

Reputation: 110071

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

Upvotes: 38

Related Questions