Thursday, September 23, 2010

Aggregate string with Linq

Linq gives you the standard aggregate function like sum, average, max, etc. But if you want (for some reason) want to concatenate some string-values in a group by it is not possible with a build-in operator.

The following code shows an example of how to group a list of values and aggregate a list of strings to a comma separated line with unique values.



var orderList = new List();
var c = new Customer { Name = "Frank" };
orderList.Add(new Order {Customer = c, Amount = 2, Price = 1.99 } );
orderList.Add(new Order {Customer = c, Amount = 4, Price = 1.59, Note = "Discrete" } );
orderList.Add(new Order {Customer = c, Amount = 1, Price = 21.00 } );
orderList.Add(new Order {Customer = c, Amount = 80, Price = 0.99, Note = "Before Xmas!!" } );

c = new Customer { Name = "John" };
orderList.Add(new Order {Customer = c, Amount = 9, Price = 1.99 } );
orderList.Add(new Order {Customer = c, Amount = 14, Price = 2.59, Note = "Call when send" } );
orderList.Add(new Order {Customer = c, Amount = 7, Price = 26.95 } );
orderList.Add(new Order {Customer = c, Amount = 90, Price = 0.99 } );

var q = orderList
.GroupBy(order => new { order.Customer.Name },
(customer, orders) => new {
customer.Name,
Totalprice = orders.Sum(o => o.Price),
Notes = string.Join(", ", orders
.Where(o => string.IsNullOrEmpty(o.Note) == false)
.Select(o => o.Note).Distinct().ToArray())
});

q.Dump();

Linqpad will give this output:



No comments: