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) };
Tuesday, May 24, 2011
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
});
1. A compile error with the anonymous classes I created to enable the concat: "The type arguments for method 'System.Linq.Enumerable.Count
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!
Subscribe to:
Posts (Atom)