asp.net c# Linq query to retrieve the sum of each value of a column

Question

I have the following array of TrackerReport Object:

public class TrackerReport : TrackerMilestone
{
    public long Views { get; set; }

    public override string ToString()
    {
        return "Id=" + MilestoneId + " | Name=" + Name + " | Views = " + Views;
}

I post also the parent class to better explain:

public class TrackerMilestone
{
    public int MilestoneId { get; set; }
    public int CampaignId { get; set; }       
    public string Name { get; set; }
    public int? SortIndex { get; set; }
    public string Url { get; set; }
    public bool IsGoal { get; set; }
    public bool IsPartialGoal { get; set; }
    public int? ParentMilestoneId { get; set; }
    public int? ViewPercent { get; set; }

    public override string ToString()
    {
        return "Id=" + MilestoneId + " | Name=" + Name + " | Url = " +  Url;
    }
}

So that it is displayed like this more or less:

ID    Name    Url    Counter
1      A     ab.com     5
2      N     ac.com     2

And I have a List of this object that I fill in this way:

var trackerReportList = new List<TrackerReport[]>();
foreach (var trackerItem in trackerChildren)
{
    //currentReport is the array of TrackerReport TrackerReport[] above mentioned
    var currentReport = GetReportForItem(GetFromDate(), GetToDate(), trackerItem.ID,
                                                 FindCampaignId(trackerItem));
    trackerReportList.Add(currentReport);    
}

All the entries have the same values, but the counter, so, for ex:
list1:

ID    Name    Url    Counter
1      A     ab.com     5
2      N     ac.com     2

list2:

ID    Name    Url    Counter
1      A     ab.com     17
2      N     ac.com     28

My goal is to generate a single TrackerReport[], with the sum of the counter values as counter.

TrackerReport[]:

ID    Name    Url    Counter
1      A     ab.com     22
2      N     ac.com     30

Is there any Linq query to do this? Or If you come up with a better solution, just post it.

Thanks in advance!

well, a tracker is a guy for sure :P

Attila
– Attila

2011-10-25 09:12:15 +00:00
Commented Oct 25, 2011 at 9:12 — Attila
– Attila, Commented Oct 25, 2011 at 9:12

Geoff Appleford · Accepted Answer · 2011-10-25 15:55:21Z

3

Here's how to do it using Linq.

Firstly, you want to get the data in a single list rather than a List of Arrays. We can do this with a SelectMany.

trackerReportList.SelectMany(c => c) flattens List<TrackerReport[]>() into IEnumerable<TrackerReport>

Then you group the objects by the relevant column/columns

group p by new {
            ID = p.MilestoneId, 
            Name = p.Name,
            Url = p.Url} into g

Finally, you project the grouping into the format we want. Counter is obtained by Adding the Views for the collection of objects in each grouping.
```
select new {
    ...
};
```

Putting it all together:

var report = from p in trackerReportList.SelectMany(c => c) 
             group p by new {
                ID = p.MilestoneId, 
                Name = p.Name,
                Url = p.Url} into g
             select new {
                ID = g.Key.ID,
                Name = g.Key.Name,
                Url = g.Key.Url,
                Counter = g.Sum(c => c.Views)
            };

edited Oct 25, 2011 at 15:55

answered Oct 25, 2011 at 9:53

Geoff Appleford

18.9k4 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Attila Over a year ago

Tnx a lot, i saw this solution after I already tried the other

Abdul Munim · Accepted Answer · 2011-10-25 09:33:17Z

This is the lambda expression for your query.

TrackerReport[] trackerInfoList = trackerReportList
    .SelectMany(s => s)
    .GroupBy(g => g.MilestoneId)
    .Select(s =>
                {
                    var trackerReportCloned = Clone<TrackerReport[]>(s.First());
                    trackerReportCloned.Views = s.Sum(su => su.Views);
                    return trackerReportCloned;
                })
    .ToArray();

If you have noted, I have used Clone<T>(T) method to deep cloning one object by the grouped value. I could have done by copying each property, but I found it the easiest.

This is the Clone() method:

public static T Clone<T>(T source)
{
    if (!typeof(T).IsSerializable)
    {
        throw new ArgumentException("The type must be serializable.", "source");
    }

    // Don't serialize a null object, simply return the default for that object
    if (Object.ReferenceEquals(source, null))
    {
        return default(T);
    }

    IFormatter formatter = new BinaryFormatter();
    Stream stream = new MemoryStream();
    using (stream)
    {
        formatter.Serialize(stream, source);
        stream.Seek(0, SeekOrigin.Begin);
        return (T)formatter.Deserialize(stream);
    }
}

Before you continue, you need to set [Serializable] attribute on your DTO objects.

[Serializable]
public class TrackerReport : TrackerMilestone
{
    .
    .
    .
}

[Serializable]
public class TrackerMilestone
{
    .
    .
    .
}

Hope, this is what you are looking for.

andrew · Accepted Answer · 2011-10-25 09:21:11Z

0

I'd do it in the following way: 1) Let the TrackerReports[] resultList is the array of deep copies of the trackerReportList[0] items 2) As I understand, the Views property corresponds to the Counter column. If so, then

foreach(report in resultList)
{
    report.Views = trackerReportList.Sum(reports => reports.Single(r => r.MilestoneId == report.MilestoneId).Views);
}

edited Oct 25, 2011 at 9:21

answered Oct 25, 2011 at 8:57

andrew

1,0302 gold badges10 silver badges21 bronze badges

Collectives™ on Stack Overflow

asp.net c# Linq query to retrieve the sum of each value of a column

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related