How to use SelectMany with LINQ JOINS

Introduction

SelectMany is Projects each element of a sequence to an IEnumerable and flattens the resulting sequences into one sequence.In this post I am going to show how you can use SelectMany Extension method to achieve the join between related tables easily without writing long queries.

To understand this consider example below

As shown in above image customer is having relation with the order table.

Scenario 1

Find out all user who is having orders.
To achieve the above requirement there is need to apply inner join between tables and Linq query to achieve this task is

var CustomerWithOrders = from c in Customers
  from o in c.Orders
    select new { 
           c.Name, 
           p.Description, 
           o.Price 
          }

Above query inner joins customer table with order table and returns those customer having orders.Sorter way to achieve inner join with the SelectMany is

Customers.SelectMany (
      c => c.Orders, 
      (c, o) => 
         new 
         {
            Name = c.Name, 
            Description = o.Description, 
            Price = o.Price
         }
   ) 

Scenario 2

Find out all user who is having orders and display N/A for the users having no orders.
To achieve above requirement there is need to apply Outer join between tales and Linq query is

var CustomerWithOrders = from c in Customers
 from o in c.Orders.DefaultIfEmpty()
 select new { 
         Name =c.Name,
         Description = ( o.Description ?? "N/A"), 
         Price = (((decimal?) o.Price) ?? 0)  
        } 

Above query outer joins customer table with order table and returns those customer having orders.
But with the SelectMany the query is

Customers.SelectMany (
      c => c.Orders.DefaultIfEmpty (), 
      (c, o) => 
         new 
         {
            Name = c.Name, 
            Description = (o.Description ?? "N/A"), 
            Price = ((Decimal?)(o.Price) ?? 0)
         }
   )

Summary

SelectMany gives fatter result. SelectMany use full when one is playing with the related tables and want to fetch data in fatterway.