< Browse > Home / Programming / Blog article: LINQ To SQL Join On Multiple Conditions

| RSS

LINQ To SQL Join On Multiple Conditions

September 16th, 2008 | 6 Comments | Posted in Programming

LINQ To SQL or just plain LINQ allows us to do a join on multiple conditions using an elegant technique. In this post I will show you how to perform a Join on multiple conditions. As an example I will take two tables called House and ShoppingMall.

image

Both these tables have PostCode and CouncilCode as common fields. Lets say that we want to retrieve all records from ShoppingMall where both PostCode and CouncilCode on House match. This requires us to do a join using two columns. In LINQ such a join can be done using anonymous types. Here is an example.

var query = from s in context.ShoppingMalls
            join h in context.Houses
            on
            new { s.CouncilCode, s.PostCode }
            equals
             new { h.CouncilCode, h.PostCode }
            select s;






The code above gets translated into this SQL query.






SELECT [t0].[ShoppingMallId], [t0].[Address],


[t0].[PostCode], [t0].[CouncilCode]


FROM [dbo].[ShoppingMall] AS [t0]


INNER JOIN [dbo].[House] AS [t1]


ON ([t0].[CouncilCode] = [t1].[CouncilCode])


AND ([t0].[PostCode] = [t1].[PostCode])








And when the above query is executed it produces the results we want.










 





	
	
	
	
	Leave a Reply
	
	 11991 views, 16 so far 
	today | 	

	
	

	Tags: 	

	
	






	










	Follow Discussion
	
6 Responses to “LINQ To SQL Join On Multiple Conditions”


	
    

					
		
  1.   

		
		
		loopGhost Says:
    
				    
		
		October 
		3rd, 2008 at 8:55 am 
		
		
    Awesome… bump, so hopefully this will get a little google traction.
    

    
		
		
    2. 

		
						
		
  2.   

		
		
		jwright Says:
    
				    
		
		December 
		30th, 2008 at 4:13 am 
		
		
    something to keep in mind.
    
if your joining two tables where the linking columns have different names, the anonymous types’ properties must match.
    
i.e.,
    

    var query = from s in context.ShoppingMalls
    
            join h in context.Houses
    
            on
    
            new {CouncilCode=s.CouncilCode, PostCode=s.PostCode }
    
            equals
    
             new {CouncilCode=h.District, PostCode=h.ZipCode }
    
            select s;
    

    
		
		
    3. 

		
						
		
  3.   

		
		
		Deepak Says:
    
				    
		
		December 
		30th, 2008 at 8:27 am 
		
		
    Thanks for the tip jwright.
    

    
		
		
    4. 

		
						
		
  4.   

		
		
		mp09ind Says:
    
				    
		
		February 
		11th, 2009 at 10:37 pm 
		
		
    What is context here?
    

    
		
		
    5. 

		
						
		
  5.   

		
		
		Deepak Says:
    
				    
		
		February 
		12th, 2009 at 8:29 am 
		
		
    mp09ind,
    

    context is my DataContext here which I generated using Visual Studio designer. You can find more details on how to do that here.
    

    http://www.onedotnetway.com/linq-to-sql-tutorial/
    

    
		
		
    6. 

		
						
		
  6.   

		
		
		mike313t Says:
    
				    
		
		June 
		25th, 2009 at 4:04 am 
		
		
    What if when joining  one condition is an “equals”
    
and another condition is “not equals”?
    

    
		
		
    7. 

		
		
	


	
 



Leave a Reply