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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | 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
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.
