Playing with Linq grouping: GroupByMany ?

Geek Lectures - Things geeks should know about » Blog Archive » Playing with Linq grouping: GroupByMany ?

Sat, 22 Dec 2007 04:17:56 GMT

PingBack from http://geeklectures.info/2007/12/21/playing-with-linq-grouping-groupbymany/

re: Playing with Linq grouping: GroupByMany ?

Frans Bouma — Mon, 07 Jan 2008 17:21:49 GMT

I ran the linq query posted above using RTM linq to sql, but it results in a LOT of queries. This is logical: the main GROUP BY query ran on the DB can't contain 'City' in the projection list unless it's in the group by clause as well. So this isn't fetched. The nested group by requires 'City' so this is fetched PER CUSTOMER from the db.

Instead, you should group the customers on Country AND City using an anonymous type. Though the nested group by then becomes more difficult at first glance.

Linq to Sql can't merge parent/child resultsets inside a projection at runtime, so you won't get this efficiently using Linq to Sql in its current state.

re: Playing with Linq grouping: GroupByMany ?

Mitsu — Wed, 09 Jan 2008 15:08:18 GMT

I do not understand.

The whole grouping stuff is not running against Linq to Sql but Linq to object. This sample is only working in memory but I am using a Linq to Sql query to populate my collection.

I have just checked again but only one sql query is executed.

After 'q.ToList()', all the grouping stuff is not running Linq to Sql anymore.

Regarding your comment, you are right, the group can not contain city but I am not working on the group ! I am using the group to populate the children.

It's a Linq option that allows you to easilly retrieve the items of the group.

g.Key contains the result of one item of the 'group by' (not containing city or any other field anymore).

But g is a new IEnumerable<Customer> and g.ToList() gives you back all the customers of the group (you must consider it as a new Linq query).

Mitsu

re: Playing with Linq grouping: GroupByMany ?

Michael L. Harges — Thu, 10 Jan 2008 03:21:38 GMT

Wow, this is a very timely and informative post. I work on a product that was built pre-Linq that has a GetTree method that builds a hierarchy of TreeGroup objects that look very similar to your GroupResult class. I was wondering this past weekend how I could accomplish the same thing in Linq and your post showed me exactly what I need to do. Thanks!

re: Playing with Linq grouping: GroupByMany ?

Tue, 15 Jan 2008 17:55:29 GMT

Can anyone convert this to VB? I'm having trouble converting the lambda function correctly (I know VB uses the Function keyword, but I'm not sure how to use it in the context of this function. Any ideas?

re: Playing with Linq grouping: GroupByMany ?

Jeremy Holt — Fri, 25 Jan 2008 21:12:50 GMT

How would you write this extremely simple SQL as LINQ?

Surely I don't have to write inner loops etc.

SELECT Commodity, Grade, SUM(Quantity) Quantity

FROM Contacts

GROUP BY Commodity, Grade

RESULTS:

Commodity Grade Quantity

Arg 2002 40/50 -352.20

Arg 2002 40/80 -3.00

Arg 2002 50/80 100.00

Arg 2004 38/50 -100.00

Arg 2004 60/70 0.00

Many thanks

Jeremy

re: Playing with Linq grouping: GroupByMany ?

mitsu — Sat, 26 Jan 2008 20:34:46 GMT

Hi Jeremy,

Try something like:

var q =

from c in Commodity

group c by new {c.Commodity, c.Grade } into g

select new {

g.Key.Commodity,

g.Key.Grade,

Quantity = g.Sum(c => c.Quantity) }

re: Playing with Linq grouping: GroupByMany ?

Jeremy Holt — Sun, 27 Jan 2008 15:31:50 GMT

Hi Mitsu,

That was great - many thanks

Jeremy

re: Playing with Linq grouping: GroupByMany ?

Jeremy Holt — Sun, 27 Jan 2008 16:06:32 GMT

Mitsu,

I just changed it into VB and have come up with an odd problem:

Dim db As New AtrbeDataContext

Dim funcQuantity = Function(x As ContractDetail) If(x.Contract.SellerID = 653, -x.Quantity, x.Quantity)

Dim qry = From c In db.ContractDetails _

Where (c.Contract.CommodityGroupID = 12 And c.ShippedStatus = 0 And c.Contract.Status = 0 And c.Contract.SellerID = 653) Or _

(c.Contract.CommodityGroupID = 12 And c.ShippedStatus = 0 And c.Contract.Status = 0 And c.Contract.BuyerID = 653) _

Group c By Commodity = c.Contract.Commodity, Grade = c.Grade Into g = Group _

Select Commodity, _

Grade, _

Quantity = g.Sum(Function(x As ContractDetail) If(x.Contract.SellerID = 653, -x.Quantity, x.Quantity)) _

Order By Commodity

This works fine and gives the expected results. However, if I replace the g.Sum(....) with funcQuantity (which of course is identical) the query fails with a NotSupportedException - "Unsupported overload used for query operator 'Sum'.

Any idea why this would be?

Jeremy

re: Playing with Linq grouping: GroupByMany ?

mitsu — Sun, 27 Jan 2008 20:28:56 GMT

Ok,

You must understand that Linq to Sql is analyzing the code (an expression tree) to translate it to a sql query. So Linq to Sql is only recognizing what Linq defines (actually, Linq to sql recognizes a subset of Linq methods). Your query fails here because funcQuantity can not be translated into slq by Linq to Sql.

re: Playing with Linq grouping: GroupByMany ?

Ryan Versaw — Mon, 28 Jan 2008 19:08:30 GMT

Depending on what you're using this for, it can be quite useful to strongly type your results. Keep in mind that this only works for a single Key type (just as the solution above does). It only takes a few small changes to do so: (hopefully this is displayed well...)

public class GroupResult<TKey, TItem>

{

public TKey Key { get; set; }

public int Count { get; set; }

public IEnumerable<TItem> Items { get; set; }

public IEnumerable<GroupResult<TKey, TItem>> SubGroups { get; set; }

public override string ToString()

{

return string.Format("{0} ({1})", Key, Count);

}

public static class MyEnumerableExtensions

{

public static IEnumerable<GroupResult<TKey, TElement>> GroupByMany<TKey, TElement>(

this IEnumerable<TElement> elements, params Func<TElement, TKey>[] groupSelectors)

{

if (groupSelectors.Length > 0)

{

var selector = groupSelectors.First();

//reduce the list recursively until zero

var nextSelectors = groupSelectors.Skip(1).ToArray();

return

elements.GroupBy(selector).Select(

g => new GroupResult<TKey, TElement>

{

Key = g.Key,

Count = g.Count(),

Items = g,

SubGroups = g.GroupByMany(nextSelectors)

});

}

else

return null;

}

re: Playing with Linq grouping: GroupByMany ?

Ryan Versaw — Mon, 28 Jan 2008 20:40:27 GMT

After re-reading a little of your post, I see you mentioned that xaml does not support generic types. I suppose my previous code probably won't work for anyone using this for WPF, but for others it might be useful.

re: Playing with Linq grouping: GroupByMany ?

mitsu — Tue, 29 Jan 2008 12:43:10 GMT

Hi Ryan,

Yes, xaml does not support generic types. Of course WPF does !! What you just cannot do is using the type name inside the xaml syntax.

Regarding your interesting proposition, it seems that there is a problem. In your solution TKey is inferred from 'params Func<TElement, TKey>[] groupSelectors'. This means that all the group definitions are returning the same type. So you could write:

q.GroupByMany(c => c.Country, c => c.City) because Country and City are both strings but you could not write

q.GroupByMany(c => c.Country, c => c.DateProperty)

re: Playing with Linq grouping: GroupByMany ?

Shawn — Wed, 06 Feb 2008 19:48:47 GMT

I've been impressed by this solution but I am trying to understand how it could be made to allow a user to select one of the sorting fields using something like a combobox. Any ideas?

var result = customers.GroupByMany(c => c.GetProperty(ComboBox1.SelectedValue), c => "City");

Linq GroupByMany dynamically

Mitsu's blog — Thu, 07 Feb 2008 02:04:51 GMT

Shawn asked me in my last post about GroupByMany how to use it dynamically. The answer is not easy. So

re: Playing with Linq grouping: GroupByMany ?

mitsu — Thu, 07 Feb 2008 02:12:27 GMT

Hi Shawn,

Here is a solution: http://blogs.msdn.com/mitsu/archive/2008/02/07/linq-groupbymany-dynamically.aspx

Linq GroupByMany dynamically

Noticias externas — Thu, 07 Feb 2008 02:14:50 GMT

Shawn asked me in my last post about GroupByMany how to use it dynamically. The answer is not easy. So

re: Playing with Linq grouping: GroupByMany ?

Shawn — Thu, 07 Feb 2008 18:21:54 GMT

Wow Mitsu, Thank you so much for your post. I've found both to be very enlightening and informative. As I learn more about LINQ and Lambda expressions, articles like these really showcase the power behind these new features and what is now possible. Thanks again.

Shawn

re: Playing with Linq grouping: GroupByMany ?

Dharmender — Fri, 08 Feb 2008 12:40:08 GMT

Can u opls let me know the nested grouping of multiple level with this no tree view involved instead an expeneder showing the groupin n subgrouping

LINQ的分组聚合技术

墙头草 — Wed, 02 Apr 2008 13:22:07 GMT

很大程度上来讲，LINQ的工作方式很像SQL。查询源定义、join、select和where子句都是相当标准的，而Group/By/Into子句

则正是我们这里要仔细说明的。SQL总是返回矩阵式数据...

re: Playing with Linq grouping: GroupByMany ?

Soyka — Thu, 07 Aug 2008 13:52:50 GMT

Thanks for the article! It helped me resolve a problem I was stuck with for a whole day! Keep up the good work..

re: Playing with Linq grouping: GroupByMany ?

mitsu — Thu, 07 Aug 2008 16:32:36 GMT

You're welcome. I am happy to see people using it.

re: Playing with Linq grouping: GroupByMany ?

Leonardo — Sat, 20 Dec 2008 00:29:48 GMT

I am trying to do dynamic subgroups on a xceed query with linq.

All is working fine,my only problem is like to find a way to dynamic to build the linq query.

Code:

private void UserControl_DataContextChanged(object sender, DependencyPropertyChangedEventArgs e)

{

if (e.NewValue is COP.GUI.Model.VMFund)

{

var activeFund = e.NewValue as COP.GUI.Model.VMFund;

//Populate the FundDetails collection with all the funds from activeFund.

var NewData = activeFund.FundDetails.ToList();

//GroupByMany allows you to define a list of subgroups in a single call.

//Dynamic solution

string dealer = "DealerName";

string portfolio = "Portfolio";

string ticker = "Ticker";

var resultDealerGroup = NewData.GroupByMany(dealer, portfolio, ticker);

var resultDealer = resultDealerGroup.Select(t =>

new

{

DealerName = t.Key.ToString(),

IM = (from d in t.Items as IEnumerable<COP.GUI.MT.Proxy.FundDealer.FundDealerDetailRow>

select d.IM).Sum(),

VM = (from d in t.Items as IEnumerable<COP.GUI.MT.Proxy.FundDealer.FundDealerDetailRow>

select d.VM).Sum(),

Notional = (from d in t.Items as IEnumerable<COP.GUI.MT.Proxy.FundDealer.FundDealerDetailRow>

select d.Notional).Sum(),

Trades = t.Count,

Portfolio = from d in t.SubGroups as IEnumerable<GroupResult>

select new

{

Portfolio = d.Key.ToString(),

IM = (d.Items as

IEnumerable<COP.GUI.MT.Proxy.FundDealer.FundDealerDetailRow>).Select(g => g.IM).Sum(),

VM = (d.Items as

IEnumerable<COP.GUI.MT.Proxy.FundDealer.FundDealerDetailRow>).Select(g => g.VM).Sum(),

Notional = (d.Items as

IEnumerable<COP.GUI.MT.Proxy.FundDealer.FundDealerDetailRow>).Select(g => g.VM).Sum(),

Trades = d.Count,

Ticker = from s in d.SubGroups as IEnumerable<GroupResult>

select new

{

Ticker = d.Key.ToString(),

IM = (s.Items as

IEnumerable<COP.GUI.MT.Proxy.FundDealer.FundDealerDetailRow>).Select(g => g.IM).Sum(),

VM = (s.Items as

IEnumerable<COP.GUI.MT.Proxy.FundDealer.FundDealerDetailRow>).Select(g => g.VM).Sum(),

Notional = (s.Items as

IEnumerable<COP.GUI.MT.Proxy.FundDealer.FundDealerDetailRow>).Select(g => g.VM).Sum(),

Trades = s.Count

}

});

var dgCollView = this.FindResource("dgViewSource") as DataGridCollectionViewSource;

dgCollView.Source = resultDealer;

tradesGrid.ItemsSource = dgCollView.View;

}

Is working correctly but the user must to select from a menu with checkboxes the subgroups like dealer and portfolio and Ticker

An iodea about like do do dynamic te code of the linq query depending of user chooise.???!!!

Thanks in advance

Leonardo

re: Playing with Linq grouping: GroupByMany ?

Steve — Thu, 02 Apr 2009 22:43:29 GMT

Thanks for the post!

Very informative

re: Playing with Linq grouping: GroupByMany ?

jhigaki — Fri, 15 May 2009 18:13:23 GMT

Hey mitsu and all the other folks... a question regarding this expression

/****************************************/

var q =

from c in Commodity

group c by new {c.Commodity, c.Grade } into g

select new {

g.Key.Commodity,

g.Key.Grade,

Quantity = g.Sum(c => c.Quantity) }

/****************************************/

Here the grouping criteria is an anonymous type {c.Commodity, c.Grade }

I need it to be a DEFINED TYPE as

public class GroupingCriteria

{

public string commodity;

public string grade;

}

I've tried so far coding the Equals and CompareTo from implementing

IEquatable<GroupingCriteria> and IComparable<GroupingCriteria> but it doesn't seem to work.

I need this because Dotfuscator CE won't work with anonymous types expressions.

Any ideas?

re: Playing with Linq grouping: GroupByMany ?

Mitsu — Sat, 16 May 2009 22:03:32 GMT

Hi,

Did you try using properties instead of just fields ?

public class GroupingCriteria

{

public string commodity {get; set;};

public string grade {get; set;};

}

re: Playing with Linq grouping: GroupByMany ?

Polo H — Tue, 30 Jun 2009 21:59:38 GMT

Can you help me understand or point me in the right direction to solve my issue? I’m trying to subgroup a Datatable(all I am given) using LINQ. I am trying to go several layers deep(5).

I have tried several things but everything falls apart after a few levels. I’m sure I just don’t understand it that well yet. I have tried:

var query = from nRow in newDT2.AsEnumerable()

group nRow by nRow.Field(”agency_group”) into agency_group

select new

{

ag = agency_group.AsEnumerable(),

agK = agency_group.Key,

agency_names =

from agency_names in agency_group

group agency_names by agency_names.Field(”agency_name”) into agency_names_group

select new

{

an = agency_names_group.AsEnumerable(),

anK = agency_names_group.Key,

div_depts =

from div_depts in agency_names_group

group div_depts by div_depts.Field(”div_dept”) into div_depts_group

select new

{

dd = div_depts_group.AsEnumerable(),

ddK = div_depts_group.Key,

unit_sections =

from unit_section in div_depts_group

group unit_section by unit_section.Field(”unit_section”) into unit_section_groups

select new

{

us = unit_section_groups.AsEnumerable(),

usK = unit_section_groups.Key,

group_unit_req =

from gur in unit_section_groups

group gur by gur.Field(”group_unit_requirement”)

}

};

and

var query = from nRow in newDT.AsEnumerable()

group nRow by nRow.Field(”agency_group”) into agency_group

from agency_name in

(from nRow in agency_group

group nRow by nRow.Field(”agency_name”) into agency_name_group

from division_name in

(from nRow in agency_name_group

group nRow by nRow.Field(”div_dept”))

group division_name by agency_name_group)

group agency_name by agency_group;

But something just isn’t working for me. I’ve looked all over and can’t find anything that goes that deep. Any help you can provide is greatly appreciate.

Thanks for your time.

re: Playing with Linq grouping: GroupByMany ?

Mitsu — Tue, 30 Jun 2009 23:37:21 GMT

Hi Polo,

Hard to say, could you send me your sample at mitsufu@microsoft.com ?

Excellent

Steven White — Mon, 17 Aug 2009 09:57:12 GMT

The code worked perfectly and is very useful. Thanks for publishing it. :-)

re: Playing with Linq grouping: GroupByMany ?

nmducit — Thu, 17 Sep 2009 04:24:59 GMT

I want use Group by Linq in Mvc.

re: Playing with Linq grouping: GroupByMany ?

Alex — Wed, 23 Sep 2009 16:07:41 GMT

Here is the VB.NET translation for whoever is wondering:

Public Class GroupResult

Public Key As Object

Public Count As Integer

Public Items As IEnumerable

Public SubGroups As IEnumerable(Of GroupResult)

Public Overrides Function ToString() As String

Return String.Format("{0} ({1})", Key, Count)

End Function

End Class

Module Extensions

<Extension()> _

Public Function GroupByMany(Of TElement)(ByRef elements As IEnumerable(Of TElement), ByVal ParamArray groupSelectors As Func(Of TElement, Object)()) As IEnumerable(Of GroupResult)

If (groupSelectors.Length > 0) Then

Dim selector = groupSelectors.First()

Dim nextSelectors = groupSelectors.Skip(1).ToArray()

Return elements.GroupBy(selector).Select(Function(g) New GroupResult With {.Key = g.Key, .Count = g.Count(), .Items = g, .SubGroups = g.GroupByMany(nextSelectors)})

Else

Return Nothing

End If

End Function

End Module