Aggregate Functions in Linq Group Statements

I was refactoring some of my linq queries in my budget app today and was having some difficulty with some of the grouping statements especially when I wanted to sum up the values in a decimal field.

Without showing you the code I'm refactoring (that would be way too embarrassing), here's a particular query that I was working with.

from mc in MthCategories
from s in (from s in Spendings where mc.MthCategoryId == s.MthCategoryId select s).DefaultIfEmpty()
where mc.Mth.Month == 3 && mc.Mth.Year == 2010
group s by mc.MthCategoryId into g
select new {
    MthCategoryId = g.Key,
    AmountSpent = (from s1 in g select s1.Amount).Sum()
}

This looks fine, however, when executed you will get the following InvalidOperationException

"The null value cannot be assigned to a member with type System.Decimal which is a non-nullable value type."

What gives!

Now I was initially thinking that this had something to do with the inferred type that was being applied to "AmountSpent", so I had a look to see what g was.

I really wasn't thinking clearly and all sorts of things were going through my head. I tried checking if the FirstOrDefault() value was null and then doing the Sum, but that didn't work. A little while later it occurred to me that "g" was actually comprised of a key and a collection and regardless of how many items were in the collection it was still there and that my initial thinking was screwed up. The problem had nothing to do with the inferred type but was caused by the fact that my sub query (from s1 in g select s1.Amount).Sum() was selecting a null value and it was the act of trying to apply Sum to a null object that was causing the problem.

The resulting query was:

from mc in MthCategories
from s in (from s in Spendings where mc.MthCategoryId == s.MthCategoryId select s).DefaultIfEmpty()
where mc.Mth.Month == 3 && mc.Mth.Year == 2010
group s by mc.MthCategoryId into g
select new {
    MthCategoryId = g.Key,
    AmountSpent = (from s1 in g select s1 == null ? 0 : s1.Amount).Sum(), 
    Spendings = g
}

Of course all of this could have been avoided had I clicked the Activate autocompletion link in LINQPad.

I hope this has helped.

Enjoy!

fsBudget - A Small Budget Application Built with jQuery, Ext-Js, C# and WCF

Finished! My first OS project.

Built using C#, jQuery and Ext-Js, it utilises WCF to consume the business layer.

Feel free to have a look.

Enjoy!

Time Well Spent

Well this morning provided some time to find out a bit more about a guy by the name of the Joseph Albahari.

Firstly, listed to Talking Shop Down Under where Richard chatted with Joe regarding a range of stuff primarily on C# 4.0 and LINQPad.

Secondly, being prompted by the podcast I went and had a look at Joe's site and found reference to a webcast that he'd done for LIDNUG. This was absolutely awesome! If you've got approx. 1.5 hours free definitely worth a watch as Joe takes you through some of the new features of C# 4.0 and explains some of the pros and cons and offers some explanations as to the why's.

Enjoy!