Holi Sale. Get upto 40% OFF on Job-oriented Training! Offer Ending in
D
H
M
S
Get Now
LINQ Inner Join with AND and OR condition

LINQ Inner Join with AND and OR condition

01 Jan 2024
Intermediate
47.6K Views
5 min read
Learn via Video Course & by Doing Hands-on Labs

ASP.NET MVC with WebAPI Course

In this LINQ Tutorial, you will learn the LINQ Inner Join with AND and OR conditions with some actual Programming Examples.LINQ has a JOIN query operator that provides SQL JOIN-like behavior and syntax. As you know, Inner join returns only those records or rows that match or are present in both tables.

There are different types of join in LINQ. Such as,Inner Join,Cross Join,Left outer join, andGroup join. Here we are going to focusing on Inner Join with AND condition and OR condition. First, let's see the simple LINQ inner join.

What is LINQ inner join?

The inner join is used to display a result that contains only those elements from the first data source that appear only one time in the second data source. In short, If an element of the first data source does not have matching elements, then it will not show in the result data set. Join and Inner Join are the same.

Example of LINQ inner join:

using System; 
using System.Linq; 
using System.Collections.Generic; 


public class Product1 { 

	public int pro_id 
	{ 
		get; 
		set; 
	} 

	public string pro_name 
	{ 
		get; 
		set; 
	} 
	public string pro_section 
	{ 
		get; 
		set; 
	} 
} 

public class Product2 { 

	public int pro_id	{ 
		get; 
		set; 
	} 

	public string pro_section 
	{ 
		get; 
		set; 
	} 
	public int pro_price 
	{ 
		get; 
		set; 
	} 
} 

class Mall { 

	static public void Main() 
	{ 
		List pro1 = new List() { 

			new Product1() {pro_id = 11, pro_name = "Toy", pro_section = "Kid's section"}, 
                           new Product1() {pro_id = 12, pro_name = "Kurti", pro_section = "Women's section"}, 
                           new Product1() {pro_id = 13, pro_name = "Shorts", pro_section = "Boy's section"}, 	
			

		}; 

		List pro2 = new List() { 

			new Product2() {pro_id = 11, pro_section = "Kid's section", pro_price = 500}, 

			new Product2() {pro_id = 12, pro_section = "Women's section", pro_price = 1100}, 
											
			new Product2() {pro_id = 13, pro_section = "Boy's section", pro_price = 700}, 								

		}; 

		var res = from e1 in pro1 
					join e2 in pro2 
						on e1.pro_id equals e2.pro_id 
							select new
							{ 
								Product_Name = e1.pro_name, 
								Product_Price = e2.pro_price 
							}; 

		Console.WriteLine("Product and their Prices: "); 
		foreach(var val in res) 
		{ 
			Console.WriteLine("Product Name: {0}, Price: {1}", 
								val.Product_Name, val.Product_Price); 
		} 
	} 
} 

Output


Product and their Prices: 
Product Name: Toy, Price: 500
Product Name: Kurti, Price: 1100
Product Name: Shorts, Price: 700

Inner Join with AND condition

Sometimes, you need to apply inner join with and condition. To write the query for inner join with and condition you need to make two anonymous types (one for the left table and one for the right table) by using the new keyword and compare both the anonymous types as shown below:

Inner join with AND Condition Example:


DataContext context = new DataContext();
var q=from cust in context.tblCustomer
 join ord in context.tblOrder
// Both anonymous types should have exact same number of properties having same name and datatype
 on new {a=(int?)cust.CustID, cust.ContactNo} equals new {a=ord.CustomerID, ord.ContactNo}
 select new 
 {
 cust.Name,
 cust.Address,
 ord.OrderID,
 ord.Quantity
 };
// Generated SQL
SELECT [t0].[Name], [t0].[Address], [t1].[OrderID], [t1].[Quantity]
FROM [tblCustomer] AS [t0]
INNER JOIN [tblOrder] AS [t1] ON (([t0].[CustID]) = [t1].[CustomerID]) AND ([t0].[ContactNo] = [t1].[ContactNo])

Note

  1. Always remember, both the anonymous types should have exact same number of properties with the same name and Datatype otherwise you will get the compile time error "Type inference failed in the call to Join".

  2. Both the comparing fields should define either NULL or NOT NULL values.

  3. If one of them is defined NULL and the other is defined NOT NULL then we need to do typecasting of a NOT NULL field to NULL data type like as above

Inner Join with OR condition

Sometimes, you need to apply inner join with or condition. To write a query for inner join with or condition you to need to use || operator in where condition as shown below:

Inner Join with OR condition Example

DataContext context = new DataContext();
var q=from cust in context.tblCustomer
 from ord in context.tblOrder
 where (cust.CustID==ord.CustomerID || cust.ContactNo==ord.ContactNo)
 select new 
 {
 cust.Name,
 cust.Address,
 ord.OrderID,
 ord.Quantity
 };
// Generated SQL
SELECT [t0].[Name], [t0].[Address], [t1].[OrderID], [t1].[Quantity]
FROM [tblCustomer] AS [t0], [tblOrder] AS [t1]
WHERE (([t0].[CustID]) = [t1].[CustomerID]) OR ([t0].[ContactNo] = [t1].[ContactNo])

Summary:

I hope you will enjoy the LINQ query with AND and OR conditions while programming with LINQ. I would like to have feedback from my blog readers. Your valuable feedback, questions, or comments about this article are always welcome.Enjoy coding

Unlock The next level of LINQ:

FAQs

Q1. What kind of join is LINQ?

A LINQ JOIN is used to combine rows from two or more tables, based on a common field between them.

Q2. What is inner join syntax?

 SELECT columns_from_both_tables FROM table1 INNER JOIN table2 ON table1.column1 = table2.column2

Q3. Can we use join in LINQ?

In LINQ, the JOIN clause is very useful when merging more than two table or object data into a single unit
Share Article
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this