Tuesday, January 18, 2011

Linq Joins with SelectMany

Shout it kick it on DotNetKicks.com
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.

2 comments:

  1. Wow this post is so wrong. To anyone who reaches this page in the future, please ignore everything you read.

    ReplyDelete
  2. The only difference in your examples is that one uses Extension methods, and one does not. They both use SelectMany. Your first example is not doing any inner joins at all.

    This is how you do an inner join:

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

    ReplyDelete