LINQ Joins with SelectMany

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

To understand this, consider the example below:

As shown in the above image, customer has a 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
  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 customers 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 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
 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 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.