LINQ Group By And Sum

Problem: LINQ syntax for grouping a set of records together and performing a sum of some of the values.

Solution: This example shows a group clause that uses multiple columns:

var query = from order in db.Orders
group order by new { Customer = order.Customer,
SalesPerson = order.SalesPerson }
into grp
select new
{
Customer = grp.Key.Customer.Name,
SalesPerson = grp.Key.SalesPerson.Name,
Quantity = grp.Sum(o => o.Quantity),
Amount = grp.Sum(o => o.Amount)
};

A simpler version is found here: http://msdn.microsoft.com/en-us/vcsharp/aa336747#sumGrouped e.g.

var categories =
from p in products
group p by p.Category into g
select new { Category = g.Key, TotalUnitsInStock = g.Sum(p => p.UnitsInStock) };

Combining LINQ Query Results

Problems: I had 2 problems when trying to combine the results of 2 separate LINQ queries recently.

1. A compile error with the anonymous classes I created to enable the concat: "The type arguments for method 'System.Linq.Enumerable.Count (System.Collections.Generic.IEnumerable)' cannot be inferred from the usage. Try specifying the type arguments explicitly."

2. A runtime error when trying to concat the two lists even though they apparently have the same columns/properties: "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."

Some sample code before:

var combinedDetailsList = (from membershipSold in membershipsSold
select new
{
SalespersonID = membershipSold.MembershipSalespersonID,
SalespersonName = membershipSold.MembershipSalesperson,
MembershipGross = membershipSold.GrossValue,
UpgradeGross = 0.00
})
.Concat
(from upgradeSold in upgradesSold
select new
{
SalespersonID = upgradeSold.MembershipSalespersonID,
SalespersonName = upgradeSold.MembershipSalesperson,
MembershipGross = 0.00,
UpgradeGross = upgradeSold.GrossAdjustment
});


Solutions:

1. To deal with the incompatible type issue you simply cast the values in your anonymous class to a consistent type (easy).

2. The concat problem is described in the following link, with the most useful post being the last one: http://stackoverflow.com/questions/2021861/all-queries-combined-using-a-union-intersect-or-except-operator-must-have-an-equ
Essentially the solution is to either use "let" statements (which I went for) or to convert each to separate lists and then combine them. The use of "lets" is meant to be a bit more effecient (though I think makes it a bit harder to understand what is going on).

Final code which compiles and runs:

var combinedDetailsList = (from membershipSold in membershipsSold
let defaultUpgradeGross = (Decimal?)0.00
select new
{
SalespersonID = (int?)membershipSold.MembershipSalespersonID,
SalespersonName = membershipSold.MembershipSalesperson,
MembershipGross = (Decimal?)membershipSold.GrossValue,
UpgradeGross = defaultUpgradeGross
})
.Concat
(from upgradeSold in upgradesSold
let defaultMembershipGross = (Decimal?)0.00
select new
{
SalespersonID = (int?)upgradeSold.MembershipSalespersonID,
SalespersonName = upgradeSold.MembershipSalesperson,
MembershipGross = defaultMembershipGross,
UpgradeGross = (Decimal?)upgradeSold.GrossAdjustment
});

Introduction

This blog is just for random .net (mainly asp.net) C# code snippets (nuggets) which I've found useful at one time or another. Hopefully it will help a few others out!