How to use SelectMany with LINQ JOINS

No.of Views2904
Bookmarked0 times
Downloads 
Votes0
By  pranay rana   On  18 Jan 2011 08:01:32
Tag : LINQ , T-SQL
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.
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

 

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

Image Loading

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.

 
Sign Up to vote for this article
 
About Author
 
pranay rana
Occupation-CEO
Company-GMind Solusion
Member Type-Expert
Location-India
Joined date-08 Jan 2011
Home Page-http://pranayamr.blogspot.com
Blog Page-http://pranayamr.blogspot.com
Hey, I am Pranay Rana, working as a Senior Software engineer in mid-size company located in ahmedabad. Web development in Asp.Net with C# and MS sql server are the experience tools that I have had for the past 4.3 years now. For me def. of programming is : Programming is something that you do once and that get used by multiple for many years You can visit me on my blog - http://pranayamr.blogspot.com/ StackOverFlow - http://stackoverflow.com/users/314488/pranay My CV :- http://careers.stackoverflow.com/pranayamr
 
 
Other popularSectionarticles
    In this article, I am going to show how you can filter your data by RowNumbers that you assigned to your records.
    Published Date : 03/Feb/2011
    Now a days most of the developers are moving towards new LINQ to SQL they find difficult to write down SQL query in C# to query data using LINQ. LINQ is a query language which get integrated in C# to query data form ObjectCollects, SQL, XML etc.
    Published Date : 08/Jan/2011
    I have already written several article about Linq its a great ORM that we can use in various way. The purpose of this post to demonstrate How we can bind custom entity to stored procedure result with use of Linq-To-SQL.
    Published Date : 05/Aug/2010
    Today one of my friend asked me about simple insert,update and delete example with LINQ-To-SQL but at that time i was not having any simple example which will show the power of LINQ-To-SQL Example
    Published Date : 17/May/2010
    After two decades, the industry has reached a stable point in the evolution of object-oriented (OO) programming technologies
    Published Date : 12/May/2010
Comments
There is no comments for this articles.
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top
</