Linq Joins with SelectMany

SelectMany 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 all of the users who have an order.

To achieve the above requirement, you need to apply an inner join between the tables. The Linq query to do this is:

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

The query above inner joins the customer table with the order table and returns those customer having orders.

The shorter way to achieve the above is to use SelectMany:

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

Scenario 2
Find all of the users who have an order and display N/A for the users who do not.

To achieve the above requirement, you need to apply an Outer join between the tables. The Linq query to do this is:

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

The query above outer joins the customer table with the order table and returns all of the customers with or without 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 is a way to flatten several results into a single sequence.
CodeProject