How to write a LINQ query that inverts the grouping of a hierarchical data source?

Question

How to write a LINQ query that takes hierarchical source data and converts it so that the grouping is inverted?

Say I have a list of Topic objects, each of which contains a collection of tags that represent metadata tags for this topic. I need to write a LINQ query to basically flip the hierarchy from the inside out, so that I have a list of tags, each of which has a collection of topics tagged with this particular tag.

Topic { Title = "Political Debate #1", Posted = 01/02/2008 } Tag { Name = "Contraversial", Color = "Red" } Tag { Name = "Politics", Color = "LightBlue" } Topic { Title = "iPhone to support SiliverLight!", Posted = 02/23/2009 } Tag { Name = "BleedingEdge", Color = "LightBlue" } Tag { Name = "Contraversial", Color = "Red" } Tag { Name = ".NET", Color = "LightGreen" } Topic { Title = "Fed Chairman admits guilt for causing second Great Depression", Posted = 06/15/2010 } Tag { Name = "Politics", Color = "LightBlue" } Tag { Name = "Contraversial", Color = "Red" } 

I want the above data to look like the results below.

 Tag { Name = "Contraversial", Color = "Red" } Topic { Title = "Political Debate #1", Posted = 01/02/2008 } Topic { Title = "iPhone to support SiliverLight!", Posted = 23/02/2009 } Topic { Title = "Fed Chairman admits guilt for causing second Great Depression", Posted = 06/15/2010 } Tag { Name = "Politics", Color = "LightBlue" } Topic { Title = "Political Debate #1", Posted = 01/02/2008 } Topic { Title = "Fed Chairman admits guilt for causing second Great Depression", Posted = 06/15/2010 } Tag { Name = ".NET", Color = "LightGreen" } Topic { Title = "iPhone to support SiliverLight!", Posted = 23/02/2009 } 

You can assume that any repeating piece of data is truly unique in that it is the only instance in memory, and these are just a few references to the same object. It is also prudent to respond to using anonymous classes to create a projection, as I understand that the form of classes may be slightly different after the inversion.

UPDATE: I added the code below that sets up the sample data. I play with answers and some of my ideas in LinqPad.

 var tags = new[] { new { Name = "Contraversial", Color = "Red" }, new { Name = "Politics", Color = "LightBlue" }, new { Name = ".NET", Color = "LightGreen" }, new { Name = "BleedingEdge", Color = "LightBlue" } }; var topics = new[] { new { Title = "Political Debate #1", Posted = DateTime.Parse("01/02/2008"), Tags = (from t in tags where new []{"Contraversial", "Politics"}.Contains(t.Name) select t), }, new { Title = "iPhone to support SiliverLight!", Posted = DateTime.Parse("02/23/2009"), Tags = (from t in tags where new []{"BleedingEdge", "Contraversial", ".NET", }.Contains(t.Name) select t), }, new { Title = "Fed Chairman admits guilt for causing second Great Depression", Posted = DateTime.Parse("06/15/2010"), Tags = (from t in tags where new []{"Contraversial", "Politics"}.Contains(t.Name) select t), }, }; 

Answer 1

After playing a bit of LinqPad, I think I found a suitable solution.

Here is a simple example.

 var topicsByTags = from topic in topics from tag in topic.Tags group topic by tag; 

And in order to get rid of the redundant collection of tags in each topic, we can do the following.

 var topicsByTags = from topic in topics from tag in topic.Tags group new { Title = topic.Title, Color = topic.Posted, } by tag into g select new { g.Key.Name, g.Key.Color, Topics = g, }; 

UPDATE: The following is another alternative that takes advantage of the grouping itself in the projection. The surface is a slightly cleaner query, the disadvantage is that the Key group adheres to the group, even if it will not be used.

 var topicsByTags = from topic in topics from tag in topic.Tags group new { Title = topic.Title, Color = topic.Posted, } by tag into g select new { g.Key.Name, g.Key.Color, Topics = g, }; 

I will leave my own answer to resolve some disputes about which solution solves the problem that I posed the best.

Answer 2

What you are looking for is Pivot.

Is it possible to collapse data using LINQ?

This source contains C # code for the Linq Pivot extension method:

 public static class LinqExtensions { public static Dictionary<TFirstKey, Dictionary<TSecondKey, TValue>> Pivot<TSource, TFirstKey, TSecondKey, TValue>(this IEnumerable<TSource> source, Func<TSource, TFirstKey> firstKeySelector, Func<TSource, TSecondKey> secondKeySelector, Func<IEnumerable<TSource>, TValue> aggregate) { var retVal = new Dictionary<TFirstKey, Dictionary<TSecondKey, TValue>>(); var l = source.ToLookup(firstKeySelector); foreach (var item in l) { var dict = new Dictionary<TSecondKey, TValue>(); retVal.Add(item.Key, dict); var subdict = item.ToLookup(secondKeySelector); foreach (var subitem in subdict) { dict.Add(subitem.Key, aggregate(subitem)); } } return retVal; } } 

Answer 3

 IDictionary<Topic, IList<Tag>> data; var n = data.SelectMany(x => x.Value.Select(y => new { Topic = x.Key, Tag = y })) .GroupBy(x => x.Tag, x => x.Topic);