.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 Projection

For the first example we will perform a very simple one-to-many query of the employees' skills. This query returns the complete list of skills with no filtering. As you can see in the sample code below, the employees collection is queried using the SelectMany method. The method is provided with a delegate in the form of a lambda expression. This lambda expression is executed for each employee and is used to return the Skills collections. The four collections are then combined into a single set of strings.

var skills = employees.SelectMany(e => e.Skills);

/* RESULTS

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

*/

Accessing Parent Object Data

In some cases you will wish to project the results of a SelectMany query and include data from both the parent and child objects. If the child collection contains objects that include a reference to their parent then this may be used. If there is no link to the parent from the children, you can use an overloaded version of the SelectMany method. This version adds a second parameter, called the result selector, that also accepts a delegate. In this case, the delegate will be called with two parameters. The first parameter represents the parent object and the second provides the child. The delegate can use these arguments to create the object to be included in the results. Therefore, to project the results of the query to an anonymous type that contains the parent and child objects, you could use the lambda expression "(p, c) => new { p, c }".

In the following example, the results include the name from the employee object and the entire Skill string.

var skills = employees.SelectMany(e => e.Skills, (e, s) => new { e.Name, s });

/* RESULTS

{ Name = Bob, s = ASP.NET }
{ Name = Bob, s = C# }
{ Name = Bob, s = JavaScript }
{ Name = Bob, s = SQL }
{ Name = Bob, s = XML }
{ Name = Sam, s = ASP.NET }
{ Name = Sam, s = C# }
{ Name = Sam, s = Oracle }
{ Name = Sam, s = XML }
{ Name = Mel, s = C# }
{ Name = Mel, s = C++ }
{ Name = Mel, s = SQL }
{ Name = Jim, s = HTML }
{ Name = Jim, s = Visual Basic }

*/>

Filtering Data

For the final example we will add some filtering to the query. Filtering can be applied to both the parent and the child objects. For the parent object, use the Where method to apply filtering against the parent collection. Add the SelectMany after this Where call. To apply filtering to the child objects, add a Where operator after the SelectMany.

The next example filters the employee list so that only developers are returned. This uses the lambda expression, e => e.Title == "Developer". The SelectMany operator then retrieves all of the skills for the filtered developers, returning each as an anonymous type containing the employee's name and the skill. Finally, these employee and skill combinations are filtered to include only skills that start with the letter C, using the lambda, empAndSkill => empAndSkill.s.StartsWith("C").

var skills = employees
    .Where(e => e.Title == "Developer")
    .SelectMany(e => e.Skills, (e, s) => new { e.Name, s })
    .Where(empAndSkill => empAndSkill.s.StartsWith("C"));

/* RESULTS

{ Name = Sam, s = C# }
{ Name = Mel, s = C# }
{ Name = Mel, s = C++ }

*/>

One-to-Many Projection with Query Expression Syntax

In the final sections of this article we will recreate the previous example queries using query expression syntax. This syntax does not include a new clause for SelectMany. Instead, you can add an extra from clause, which obtains data from the results of the first from clause. When parsing such a query, the first from clause translates to a Select standard query operator. All other from clauses become SelectMany operations.

The following code recreates the initial example, which created a combined list of skills from all of the employees. Note that the second from clause is used to obtain the skills from the Skills property of the results of the first from clause.

var skills =
    from e in employees
    from s in e.Skills
    select s;

In the second example we combined the employees' names and skills in an anonymous type. With query expression syntax this becomes a simple projection that uses values from the two range variables.

var skills =
    from e in employees
    from s in e.Skills
    select new { e.Name, s };

The third example performed filtering of both the parent and child collections using two Where operators. The query syntax variation of this is very similar. Two where clauses are used, each using the range variable for the collection being filtered.

var skills =
    from e in employees
    where e.Title == "Developer"
    from s in e.Skills
    where s.StartsWith("C")
    select new { e.Name, s };