8

I have the following Linq to group a list by year, then by month.

var changesPerYearAndMonth = list
              .GroupBy(revision => new { revision.LocalTimeStamp.Year, revision.LocalTimeStamp.Month })
              .Select(group => new { GroupCriteria = group.Key, Count = group.Count() })
              .OrderBy(x => x.GroupCriteria.Year)
              .ThenBy(x => x.GroupCriteria.Month);

My current output is the following:

Year 2005, month 1, count 469
Year 2005, month 5, count 487
Year 2005, month 9, count 452
Year 2006, month 1, count 412
Year 2006, month 5, count 470
...

As you can see, months without value, are not included in the query. I would like to include them, having the following output:

Year 2005, month 1,  count 469
Year 2005, month 2,  count 0
Year 2005, month 3,  count 0
...
Year 2005, month 12, count 0
Year 2006, month 1,  count 412
Year 2006, month 2,  count 0
...
Year 2006, month 12, count 0

In other words, I need to get also empty months.

Could I implement this with a Linq query? Thanks in advance

Daniel Peñalba
  • 30,507
  • 32
  • 137
  • 219
  • 2
    Why do we spend so much time coming up with a difficult, non-readable and inefficient Linq when we can do it simpler and more-readable in a loop? – Aliostad Nov 22 '10 at 13:35
  • 1
    @Aliostad: So far, Daniel's Linq solution is quite straight-forward and readable. He can always fall back to using a loop if the answer to this question turns out to be too complicated using Linq. – Heinzi Nov 22 '10 at 13:37
  • Well, I just think we are in a Linq-mad world which is not good. You loose all that debugging power. The point is, similar to XSLT, if it gets too complex it is a house of cards. – Aliostad Nov 22 '10 at 13:40
  • 2
    Using `OrderBy` twice like this is a bad idea. You should use `OrderBy(x => x.GroupCriteria.Year).ThenBy(x => x.GroupCriteria.Month)`. That's a side issue (which is why I've not posted it as an answer) but it's still worth knowing about. – Jon Skeet Nov 22 '10 at 13:40
  • @Aliostad: I don't use linq because is a good exercise... This is the reason, tested in our labs. Four million objects: Get revision count by user using LINQ: 0 ms. Get revision count by user iterative approach: 780ms – Daniel Peñalba Nov 22 '10 at 13:43
  • @Daniel: Well LINQ is having to iterate too, don't forget... there's nothing that LINQ to Objects is doing which can't be done imperatively. Of course, if it's *really* using LINQ to SQL, that's a different matter. – Jon Skeet Nov 22 '10 at 13:44
  • @Daniel @ Jon EXACTLY!! That is my point... it has just made it easier for us all to forget it **LOOPS**. – Aliostad Nov 22 '10 at 13:46
  • I'm new in linq. I have used always iterative approachs. Linq performs good and requies less LOC to do some things. I'm doing some statistic graphs with lots of queries. I think that is a good technology that it's there, and you can use, remember, if you want. – Daniel Peñalba Nov 22 '10 at 13:54

1 Answers1

18

I think you want something like this:

var changesPerYearAndMonth =
    from year in Enumerable.Range(2005, 6)
    from month in Enumerable.Range(1, 12)
    let key = new { Year = year, Month = month }
    join revision in list on key
              equals new { revision.LocalTimeStamp.Year, 
                           revision.LocalTimeStamp.Month } into g
    select new { GroupCriteria = key, Count = g.Count() };

Note that:

  • You need to know what years you're trying to come up with data for. You can fetch this from another query, of course, to find the minimum and maximum years involved - or maybe find the minimum and assume there will be nothing in the future (I don't know if this a valid assumption or not though)
  • This will automatically be ordered correctly
Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • Jon Skeen 1 : Me 0 . But I dont like "You need to know what years" part. You need to make one more query to know what years are you working with. – Euphoric Nov 22 '10 at 13:48
  • Still failing ... there is an error near ") into g" (remove the parenthesis) and g has no .Key property – Daniel Peñalba Nov 22 '10 at 15:02
  • @Daniel: Okay, try now. Had forgotten the details of GroupJoin :) – Jon Skeet Nov 22 '10 at 15:18
  • @John, only a correction on line 5, it would be "join revision in list on key". Thanks a lot! – Daniel Peñalba Nov 22 '10 at 15:19
  • @JonSkeet How do I trim the result? For example if the first 3 months of the first year have Count = 0 and/or the last month of the last year has Count = 0, I might not want to know about those wants because they are "out of the sequence" most likely. – JoanComasFdz Apr 11 '14 at 12:31
  • @JoanComasFdz: It's not clear what you mean by "out of sequence" here, but you could use `SkipWhile(g => g.Count() == 0).TakeWhile(g => g.Count() != 0)` – Jon Skeet Apr 11 '14 at 12:45
  • @JonSkeet What I mean is: Given [0, 0, 2, 0, 2, 0, 0], Trim it so the result is: [2, 0, 2]. That's my solution: list.SkipWhile(c => c.Count == 0).Reverse().SkipWhile(c => c.Count == 0).Reverse(); – JoanComasFdz Apr 11 '14 at 12:57
  • @JoanComasFdz: Right, yes, that will do it. May not be *hugely* efficient, but it doesn't matter much with this little data. – Jon Skeet Apr 11 '14 at 13:02
  • @JonSkeet thanks! Another question: How can I add some data from the revision to the final anonymous type? So the last line will be something like: select new { GroupCriteria = key, Count = g.Count(), Name = revision.Name }; – JoanComasFdz Apr 11 '14 at 13:30
  • How would you pass in the range to get the past year from `DateTime.Now`? – Heinrich Nov 09 '14 at 18:51
  • @Heinrich: Do you mean just to get the range of months? You'd only need one `Enumerable.Range` (because there'd only be one year involved) and you'd call `DateTime.Today.Month` to work out how many months to get... – Jon Skeet Nov 09 '14 at 20:39
  • I try to modified this to `Group By` week but still doesn't manage to make it works. Is there any way I can integrate with method to determine ISO week? – Martin Valentino Sep 05 '15 at 06:22
  • @MartinSiagian: I suggest you ask a new question, showing what you've tried. Note that .NET doesn't make getting the ISO week terribly easy - you mnay want to consider using my Noda Time project instead. (But that depends on the context.) – Jon Skeet Sep 05 '15 at 08:29