.NET 3.5+

LINQ One-to-Many Projection

by Richard Carr, published at http://www.blackwasp.co.uk/LinqOneToManyProjection.aspx

The fourth part of the LINQ to Objects tutorial continues the investigation of the projection operators and clauses. This article describes the use of the SelectMany operator, which allows one-to-many object hierarchies to be flattened and queried.

One-to-Many Relationships

One-to-many relationships are commonplace in object-oriented programming. They usually take the form of a parent-child relationship. For example, an Employee class may include a property that holds a collection of skills. In this case the parent object is the employee and the skills are the children.

Parent-child relationships are difficult to query with LINQ's Select standard query operator. Using this operator you could easily query the skills of a single employee but when you have a collection of employees, each with a set of skills, querying all employees' skills is more complex. However, you can easily perform such operations using the SelectMany standard query operator.

SelectMany Operator

The SelectMany standard query operator is an extension method that allows any collection that implements the IEnumerable<T> interface to be queried. The method's parameter is used to extract a child collection from each parent item. Finally, all of the child collections are combined, or flattened, into a single IEnumerable. Further operations may then be performed upon the new set of data.

This can initially sound like a complex operation. It can be easier to understand when given an example. Let's consider a system that includes a collection of employees, each with a set of skills. The employees and their skills are listed below:

• Bob (Senior Developer)
  • ASP.NET
  • C#
  • JavaScript
  • SQL
  • XML
• Sam (Developer)
  • ASP.NET
  • C#
  • Oracle
  • XML
• Mel (Developer)
  • C#
  • C++
  • SQL
• Jim (Developer)
  • HTML
  • Visual Basic

The employees are represented using Employee objects that each include a collection of strings holding the skills. When the SelectMany operator is used to read the Skills collections, the four sets of skills are extracted and then combined into a single IEnumerable. The final result, once the collection is read, is the following list. This assumes that no further projection or filtering is applied:

• ASP.NET
• C#
• JavaScript
• SQL
• XML
• ASP.NET
• C#
• Oracle
• XML
• C#
• C++
• SQL
• HTML
• Visual Basic

You can see that all of the skills are now in a single set, which may be queried further as desired. To demonstrate the use of the SelectMany method, we will recreate the example data above using C# code and perform some queries. To begin, we need to create the Employee class. This is shown below:

public class Employee
{
    public string Name { get; set; }
    public string Title { get; set; }
    public Collection<string> Skills { get; set; }
}

We can now create the collection of employees and skills as per the example data:

var bob = new Employee
{
    Name = "Bob",
    Title = "Senior Developer",
    Skills = new Collection<string> { "ASP.NET", "C#", "JavaScript", "SQL", "XML" }
};
var sam = new Employee
{
    Name = "Sam",
    Title = "Developer",
    Skills = new Collection<string> { "ASP.NET", "C#", "Oracle", "XML" }
};
var mel = new Employee
{
    Name = "Mel",
    Title = "Developer",
    Skills = new Collection<string> { "C#", "C++", "SQL", }
};
var jim = new Employee
{
    Name = "Jim",
    Title = "Junior Programmer",
    Skills = new Collection<string> { "HTML", "Visual Basic" }
};

var employees = new List<Employee> { bob, sam, mel, jim };