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!
