Jacob and I have been struggling with some “nasty” LINQ today where we need to get the aggregated value of a Quantity column from a transaction table, however a little “twist” was that we need to sum all the positive values into one group and all the negative values into another, and in the result set end up with only ONE row containing a PositiveQuantitySum and a NegativeQuantitySum … at first it sound pretty easy, but turned out not to be the case.
After a few attempts we ended up with this LINQ which actually gives the correct result, but we have 2 rows (one “positive”, one “negative”) – here is the LINQ:
from test in
(from t in AxInventTransactions
where t.Quantity >= 0 &&
t.DateFinancial >= DateTime.Parse("01-04-2010") &&
t.DateFinancial <= DateTime.Parse("31-08-2010") &&
t.Customer.StatisticsGroupName.Contains("S")
orderby t.ItemNumber, t.CustomerAccountNumber
group t by new { t.ItemNumber, t.CustomerAccountNumber, t.VendorAccountNumber, t.Customer.StatisticsGroupName, CustomerName = t.Customer.Name, ProductName = t.Product.Name }
into myGroup select new { myGroup.Key.ItemNumber,
myGroup.Key.CustomerAccountNumber,
myGroup.Key.VendorAccountNumber,
myGroup.Key.StatisticsGroupName,
myGroup.Key.ProductName,
myGroup.Key.CustomerName,
Quantity = myGroup.Sum(i => i.Quantity)
})
.Union(from t in AxInventTransactions
where t.Quantity < 0 &&
t.DateFinancial >= DateTime.Parse("01-04-2010") &&
t.DateFinancial <= DateTime.Parse("31-08-2010") &&
t.Customer.StatisticsGroupName.Contains("S")
orderby t.ItemNumber, t.CustomerAccountNumber
group t by new { t.ItemNumber,
t.CustomerAccountNumber,
t.VendorAccountNumber,
t.Customer.StatisticsGroupName,
CustomerName = t.Customer.Name,
ProductName = t.Product.Name}
into myGroup select new { myGroup.Key.ItemNumber,
myGroup.Key.CustomerAccountNumber,
myGroup.Key.VendorAccountNumber,
myGroup.Key.StatisticsGroupName,
myGroup.Key.ProductName,
myGroup.Key.CustomerName,
Quantity = myGroup.Sum(i => i.Quantity) } ).OrderBy(x => x.ItemNumber).ThenBy(x => x.CustomerAccountNumber)
orderby test.ItemNumber, test.CustomerAccountNumber
select new
{
ItemNumber = test.ItemNumber,
CustomerAccountNumber = test.CustomerAccountNumber,
VendorAccountNumber = test.VendorAccountNumber,
QuantityPlus = test.Quantity >= 0 ? test.Quantity : 0,
QuantityNeg = test.Quantity < 0 ? test.Quantity : 0,
StatisticsGroupName = test.StatisticsGroupName,
ProductName = test.ProductName,
CustomerName = test.CustomerName,
}
The above LINQ which uses UNION to merge the two result set (positive / negative)produces a result set something like this:
We would like to have this result as ONE row, containing a column for the positive sum (6) and a column for the negative sum (-3).
While eating dinner tonight (I now, geeky!), I realized that we had to skip UNION and do it all “in one go”. The problem however is that (as you probably already know): Anonymous types can't be null
… and grouping all the positive and negative values of Quantity “in one go” would result in a NULL value for each “alternate” value (or whatever we should call it), causing an InvalidOperationException in this case: “The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type.”
So .. we need to make sure that IF the value returned by Sum() is actually NULL (which does not play together with System.Int32 :-) we instead return 0 (as it’s harmless to either add/subtract zero, this will of course not affect the result).
I ended up with this LINQ:
from t in AxInventTransactions
where t.DateFinancial >= DateTime.Parse("01-04-2010") &&
t.DateFinancial <= DateTime.Parse("31-08-2010") &&
t.Customer.StatisticsGroupName.Contains("S")
orderby t.ItemNumber, t.CustomerAccountNumber
group t by new { t.ItemNumber,
t.CustomerAccountNumber,
t.VendorAccountNumber,
t.Customer.StatisticsGroupName,
CustomerName = t.Customer.Name,
ProductName = t.Product.Name }
into myGroup select new { myGroup.Key.ItemNumber,
myGroup.Key.CustomerAccountNumber,
myGroup.Key.VendorAccountNumber,
myGroup.Key.StatisticsGroupName,
myGroup.Key.ProductName,
myGroup.Key.CustomerName,
QuantityPlus = myGroup.Where(x => x.Quantity >= 0).Sum(i => i.Quantity) != null ? myGroup.Where(x => x.Quantity >= 0).Sum(i => i.Quantity) : 0,
QuantityNeg = myGroup.Where(x => x.Quantity < 0).Sum(i => i.Quantity) != null ? myGroup.Where(x => x.Quantity < 0).Sum(i => i.Quantity) : 0 }
Which produces a result set like this:
As you can see we now have ONE row with a column for “all positive values aggregated” and column for “all negative values aggregated”, which is exactly what I want.
The ‘trick’ is to make sure you don’t get any NULL values into the quotation, which is done using the ternary operator (very useful in LINQ also!), like so:
QuantityPlus = myGroup.Where(x => x.Quantity >= 0).Sum(i => i.Quantity) != null ? myGroup.Where(x => x.Quantity >= 0).Sum(i => i.Quantity) : 0
Which “translates” to something like this:
If Quantity is equal or larger than 0, add the value of Quantity to the aggregated “QuantityPlus” (System.Int32), however if the Quantity value does NOT meet the criteria (aka is a negative value) then NULL would be added (which of course will cause an exception!), SO INSTEAD we need to add the value 0 to the aggregated “QuantityPlus” (using the ternary operator takes care of this).
Hope the above makes sense and helps anyone which is facing similar problem finding the solution a bit easier :-) .. if anyone knows an alternate way of solving this issue, please feel free to write me an email.