SelectMany in Entity Framework

SelectMany in Entity Framework is an operator that flattens out the collection of collections into one single collection of objects. It is a very useful operator with many use cases.

Example Query

Consider the following query. The query returns list of Products under the ProductModel.

Select a.name as ProductModel, b.productID, b.Name from 
Production.ProductModel  a
inner join [Production].[Product] b
on ( a.ProductModelID=b.ProductModelID)
where a.Name like 'C%'

//Returns
ProductModel    productID  Name
Cycling Cap       712      AWC Logo Cap
Cable Lock        843      Cable Lock
Classic Vest	  864	   Classic Vest, S
Classic Vest	  865	   Classic Vest, M
Classic Vest	  866	   Classic Vest, L
Chain	          952      Chain

We can write the above query in Entity Framework as follows. The query returns the collection of ProductModel. Under each ProductModel we have collection of Products.

using (AdventureWorks db = new AdventureWorks())
{
    db.Database.Log = Console.Write;

    var model = db.ProductModels
                    .Where(m => m.Name == "Classic Vest")
                    .Select(m =>
                            new
                            {
                                m.Name,
                                m.Products,
                            }
                        ).ToList();


    foreach (var p in model)
    {
        Console.WriteLine("{0}", p.Name);

        foreach (var prd in p.Products)
        {
            Console.WriteLine("\t\t{0}", prd.Name);
        }
    }
}            

SelectMany

The selectMany helps flattens out the collection of collections into one single collection of objects. Now let use re write the above query using the selectMany

SelectMany flattens two collections. In the example below ProductModels is the first or outer collection. We need to choose the second or inner collection as the first argument to the SelectMany, which is Products (SelectMany(p => p.Products)

Next, we need to choose the shape of our output using a projection query. The lambda expression gets two arguments. The first argument is a reference to the outer collection (ProductModels). The second is a reference to the inner collection (Products).

The query returns a single collection, which is similar to what you get when you execute the SQL

using (AdventureWorks db = new AdventureWorks())
{
    db.Database.Log = Console.Write;

    var model = db.ProductModels
                    .Where(m=> m.Name.StartsWith("C"))
                    .SelectMany(
                        p => p.Products, (m, p) => new
                        {
                            ModelName= m.Name,
                            ProductID = p.ProductID,
                            ProductName=p.Name
                        }).ToList();

    foreach (var p in model)
    {
        Console.WriteLine("{0} {1} {2}", p.ModelName, p.ProductID, p.ProductName);
    }
}

//QUERY
SELECT
    [Extent1].[ProductModelID] AS [ProductModelID],
    [Extent1].[Name] AS [Name],
    [Extent2].[ProductID] AS [ProductID],
    [Extent2].[Name] AS [Name1]
    FROM  [Production].[ProductModel] AS [Extent1]
    INNER JOIN [Production].[Product] AS [Extent2] ON [Extent1].[ProductModelID] = [Extent2].[ProductModelID]
    WHERE [Extent1].[Name] LIKE N'C%'

SelectMany Multiple collections

You can chain of selectMany methods as shown in below. In our first selectMany projection, we have included ProductInventories collection. Now in the second selectMany we can flatten it again into a single collection.

using (AdventureWorks db = new AdventureWorks())
{
    db.Database.Log = Console.Write;


    var model = db.ProductModels
                    .Where(m => m.Name.StartsWith("C"))
                    .SelectMany(
                        p => p.Products, (m, p) => new
                        {
                            ModelName = m.Name,
                            ProductID = p.ProductID,
                            ProductName = p.Name,
                            p.ProductInventories         //ProductInventories         
                        })
                        .SelectMany(p => p.ProductInventories, (m, p) => new
                        {
                            m.ModelName,
                            m.ProductID,
                            m.ProductName,
                            p.LocationID,
                            p.Quantity
                        }).ToList();

    foreach (var p in model)
    {
        Console.WriteLine("{0} {1} {2} {3} {4}", p.ModelName, p.ProductID, p.ProductName,p.LocationID, p.Quantity);
    }
}

//SQL
SELECT
    [Extent1].[ProductModelID] AS [ProductModelID],
    [Extent1].[Name] AS [Name],
    [Extent2].[ProductID] AS [ProductID],
    [Extent2].[Name] AS [Name1],
    [Extent3].[LocationID] AS [LocationID],
    [Extent3].[Quantity] AS [Quantity]
    FROM   [Production].[ProductModel] AS [Extent1]
    INNER JOIN [Production].[Product] AS [Extent2] ON [Extent1].[ProductModelID] = [Extent2].[ProductModelID]
    INNER JOIN [Production].[ProductInventory] AS [Extent3] ON [Extent2].[ProductID] = [Extent3].[ProductID]
    WHERE [Extent1].[Name] LIKE N'C%'

References

SelectMany API

Summary

SelectMany converts collection of collections into one single collection of objects. You can also use it flatten the multiple hierarchical collections into a single collection.