How to use SelectMany with LINQ JOINS

 

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

img01

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)
         }
   )
Pranay Rana

Pranay is MVB on DZone and working as a Senior Software engineer. Doing Web development using Asp.Net ,C#, MS sql server, JQuery/Javascript that he had experience of 4.3 years now. For himdef. of programming is : Programming is something that you do once and that get used by multiple for many years You can visit him on his blog - http://pranayamr.blogspot.com/ Follow on twitter : @pranayamr

One Comment to “How to use SelectMany with LINQ JOINS”

Comments are closed.