Using LINQ to create yearly summary series for use in charting

Question

I have a database procedure that based on what data is available, will give me a grouped yearly summary. With my test instance I'm running, currently my data matches something similar

--------------------------------------------
|Month  |Id  |BehaviorName  |Count         |
--------------------------------------------
|1      |NULL|              |0             |
--------------------------------------------
|2      |NULL|              |0             |
--------------------------------------------
|3      |NULL|              |0             |
--------------------------------------------
|4      |NULL|              |0             |
--------------------------------------------
|5      |NULL|              |0             |
--------------------------------------------
|6      |4   |Name1         |2             |
--------------------------------------------
|7      |4   |Name1         |5             |
--------------------------------------------
|7      |3   |NameElse      |7             |
--------------------------------------------
|8      |NULL|              |0             |
--------------------------------------------
|9      |NULL|              |0             |
--------------------------------------------
|10     |NULL|              |0             |
--------------------------------------------
|11     |NULL|              |0             |
--------------------------------------------
|12     |NULL|              |0             |
--------------------------------------------

This is a very simple result set but the data can be any more or less complicated. The data outputted here is going to be used in conjunction with the Telerik RadChart control for graphing purposes. Essentially what I need to do is take the result set above, and mirror it depending on how many instances of the specified ID occur so that it can be plotted as a separate series

So for instance BehaviorName titled Name1 would have it's own result set like

--------------------------------------------                
|Month  |Id  |BehaviorName  |Count         |  
--------------------------------------------  
|1      |NULL|              |0             |  
--------------------------------------------  
|2      |NULL|              |0             |  
--------------------------------------------  
|3      |NULL|              |0             |  
--------------------------------------------  
|4      |NULL|              |0             |  
--------------------------------------------  
|5      |NULL|              |0             |  
--------------------------------------------  
|6      |4   |Name1         |2             |  
--------------------------------------------  
|7      |4   |Name1         |5             |  
--------------------------------------------  
|8      |NULL|              |0             |  
--------------------------------------------  
|9      |NULL|              |0             |  
--------------------------------------------  
|10     |NULL|              |0             |  
--------------------------------------------  
|11     |NULL|              |0             |  
--------------------------------------------  
|12     |NULL|              |0             |  
--------------------------------------------

and BehaviorName titled NameElse would have it's own result set like

--------------------------------------------
|Month  |Id  |BehaviorName  |Count         |
--------------------------------------------
|1      |NULL|              |0             |
--------------------------------------------
|2      |NULL|              |0             |
--------------------------------------------
|3      |NULL|              |0             |
--------------------------------------------
|4      |NULL|              |0             |
--------------------------------------------
|5      |NULL|              |0             |
--------------------------------------------
|6      |NULL|              |0             |
--------------------------------------------
|7      |3   |NameElse      |7             |
--------------------------------------------
|8      |NULL|              |0             |
--------------------------------------------
|9      |NULL|              |0             |
--------------------------------------------
|10     |NULL|              |0             |
--------------------------------------------
|11     |NULL|              |0             |
--------------------------------------------
|12     |NULL|              |0             |
--------------------------------------------

I've decided in my application to take the single result set and try to split it into separate groupings via LINQ. My question is what would be the best approach for this?

var series = from occurence in fetched.YearlyTotals.Tables[0].AsEnumerable()
             group occurence by g.Field<int>("TargetedBehaviorId") into occurences
             select new
             {
                 Behavior = occurences.Key,
                 Occurences = from o in occurences
                              select new
                              {
                                 Month = o.Field<int>("Month"),
                                 TargetedBehaviorId = o.Field<int>("TargetedBehaviorId"),
                                 BehaviorName = o.Field<string>("BehaviorName"),
                                 Count = o.Field<int>("Count")
                              }
             };

Currently this will disregard any null fields, but I need to include every month within the series for each unique row that would match the result sets above. Currently this LINQ statement only gives me the 3 rows with data.

Also if there is an easier way that this can be accomplished on the SQL end, I wouldn't be opposed to working with that either.

Update:

I ended up using a combination of a few of the techniques mentioned in this question. I created a class specifically for creating my chart series items just for the sake of clarity

private class OccurrenceItem
{
    public int TargetedBehaviorId { get; set; }
    public int Month { get; set; }
    public int Count { get; set; }
    public string BehaviorName { get; set; }

    public OccurrenceItem()
    {

    }

}

//Gets a list of all the months that are returne from the query
//Always returns all 12 months
int[] months = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 };
var currentBehaviors = from behaviors in fetched.TargetedBehaviors
                       select behaviors;

var emptySet = (from month in months
                from behavior in currentBehaviors
                select new OccurrenceItem
                {
                    Month = month,
                    TargetedBehaviorId = behavior.AssignedBehaviorId,
                    BehaviorName = behavior.Behavior,
                    Count = 0
                }).ToList();

var occurences = (from o in fetched.YearlyTotals.Tables[0].AsEnumerable()
                  select new OccurrenceItem {
                      Month = o.Field<int>("Month"),
                      TargetedBehaviorId = o.Field<int>("TargetedBehaviorId"),
                      BehaviorName = o.Field<string>("BehaviorName"),
                      Count = o.Field<int>("Count")
                  }).ToList();


var merged = occurences.Union(emptySet)
                .GroupBy(x => new {
                    x.Month,
                    x.TargetedBehaviorId,
                    x.BehaviorName,
                }).Select(x => new OccurrenceItem {
                    Month = x.Key.Month,
                    TargetedBehaviorId = x.Key.TargetedBehaviorId,
                    BehaviorName = x.Key.BehaviorName,
                    Count = (from y in x
                            select y.Count).Sum()
                }).OrderBy(x => x.Month);

All of these statements combined get me a List<OccurenceItem> which I can then separate out into separate series based on the TargetedBehaviorId and add as my chart series

You can create the collection of empty entries and merge it with your `series` collection. Look here was [similar question](http://stackoverflow.com/questions/17854050/best-way-to-use-linq-to-fill-in-gaps-in-my-data/17854794#17854794). — YD1m, Jul 29 '13 at 19:12

juhan_h · Answer 1 · 2013-07-30T07:34:56.330

To get the null values you should do a left join using IEnumberable.DefaultIfEmpty()

/* omitted */
group occurence by g.Field<int>("TargetedBehaviorId") into occurences
from o in occurrences.DefaultIfEmpty()
select new
/* omitted */

Update after sleeping

I noodled aroud with it a little, but I did not find an elegant way to accomplish this.

If you group by TargetBehaviour then you will only get the months where something has happened and DefaultIfEmpty actually will not have an effect. If you group by Month you will get all the months under which are empty ocurrences except when there is a behavior.

I would probalby go with the first solution (and thus render my answer: use DefaultIfEmpty completely off the mark) and use Union to add the empty months. Dependent whether you are using Entity Framework or Linq-to-Sql the union could provide troublesome (in EF) and would require to be executed in memory.

Hence, sorry for the inconviniece, you should probably look into jocull's answer.

I feel like this is an elegant way to do what I'm thinking, but merging a collection with an empty series like stated above is more what I'm looking for. Just tried your code here and couldn't get the result. — dgarbacz, Jul 29 '13 at 19:26
thanks for the update! It's an interesting problem and I'm trying to combine all of what's here in order to reach a solution. — dgarbacz, Jul 31 '13 at 13:30

score 0 · Answer 2 · answered Jul 29 '13 at 20:54

It may be worth it to you do break up the steps with IEnumerables or IQueryables for better comprehension. You could try something like this (untested, not sure on syntax) to have a clearly defined list of months to make sure you get them all even if they are not set. With there's no data you can return a custom form of default - whatever you want it to be.

int[] months = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 };
var series = months.SelectMany(m => {
    var query = from occurence in fetched.YearlyTotals.Tables[0].AsEnumerable()
                where occurence.Field<int>("Month") == m
                select occurence;

    var grouped = query.GroupBy(x => x.Field<int>("TargetedBehaviorId"));

    if(grouped.Any())
    {
        return   from g in grouped
                 select new
                 {
                     Month = m,
                     Behavior = occurences.Key,
                     Occurences = from o in occurences
                                  select new
                                  {
                                     Month = o.Field<int>("Month"),
                                     TargetedBehaviorId = o.Field<int>("TargetedBehaviorId"),
                                     BehaviorName = o.Field<string>("BehaviorName"),
                                     Count = o.Field<int>("Count")
                                  }
                 };
    }

    //Default for the month
    return new {
        Month = m,
        Behavior = int.MinValue,
        Occurences = null
    };
});

score -1 · Answer 3 · answered Jul 29 '13 at 19:15

-1

If you want to work on SQL end, you could do with SQL procedure, everything has its advantages and disadvantages, what you're going to use is up to you.

answered Jul 29 '13 at 19:15

Ivan Ičin

9,672
5
36
57

I was saying if there was a way to shape the result set on SQL Server to match something similar, I wouldn't be opposed to using that solution. LINQ is a bit more familiar to me. – dgarbacz Jul 29 '13 at 19:27

Using LINQ to create yearly summary series for use in charting

3 Answers3