LINQ Inner Join with AND and OR condition

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