Linq Join on Mutiple columns using Anonymous type

No.of Views2595
Bookmarked0 times
Downloads 
Votes0
By  pranay rana   On  02 Oct 2011 06:10:41
Tag : LINQ , Miscellaneous
I was working on the project using LINQ. I got the requirement to join the two entity on multiple column.
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

Always we face new problems in our development because that is nature of the software development.Last week, I was working on the project using LINQ. I got the requirement to join the two entity on multiple column.

For example consider the following image. There are two entity Distributor and Customer related to each other.Now I want to find out all customer who lives in same city where the distributor living. 

Image Loading

So to find out that I have to make join between Customer and Distributor. And to achieve this I need to join by using multiple columns City,State,Country,ID. (Note: I am using id in join because later on I want to get which distributor near to customer).

Now with the LINQ you can join two entity on multiple columns by creating one anonymous type.

EmployeeDataContext edb= new EmployeeDataContext();
var cust = from c in edb.Customers
           join d in edb.Distributors on
             new { CityID = c.CityId, StateID = c.StateId, CountryID = c.CountryId,
                   Id = c.DistributorId }   
           equals
             new { CityID = d.CityId, StateID = d.StateId, CountryID = d.CountryId,
                   Id = d.DistributorId }   
           select c;

Note : As anonymous types used to join entity on multiple column, so for that make sure that both are equal and they must have the same properties in same order. Otherwise it don't get complied and you get error.

Once you are done run the code and you see the following query in your sql profiler or you can also use the visual studio feature to get the query.

SELECT [t0].[Id], [t0].[Name], [t0].[EmailId], [t0].[CityId], [t0].[StateId],
[t0].[CountryId], [t0].[PinCode], [t0].[DistributorId]
FROM [dbo].[Customer] AS [t0]
INNER JOIN
[dbo].[Distributor] AS [t1] ON
([t0].[CityId] = [t1].[CityId])
    AND ([t0].[StateId] = [t1].[StateId])
    AND  ([t0].[CountryId] = [t1].[CountryId])
    AND ([t0].[DistributorId] =[t1].[DistributorId])

That's all very easy and easy when you work in LINQ.

 
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
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