Please enable Javascript to correctly display the contents on Dot Net Tricks!

LINQ Inner Join with AND and OR condition

  Author : Shailendra Chauhan
Posted On : 16 Jul 2014
Total Views : 81,869   
Updated On : 16 Jul 2014
 

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 exists in both the tables. The simple inner join example is given below:

DataContext context = new DataContext();
var q = (from pd in context.Products
 join od in context.Orders on pd.ProductID equals od.ProductID
 orderby od.OrderID
 select new
 {
 od.OrderID,
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 od.Quantity,
 od.Price,
 }).ToList();

Inner Join with AND condition

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

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 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 other is defined NOT NULL then we need to do typecasting of 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 query for inner join with or condition you need to use || operator in where condition as shown below:

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])
What do you think?

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



Free Interview Books
 
COMMENTS (0)
2 JUN
Hadoop Development cum Administration (Online)

7:00 AM-9:00AM IST

Know More
28 MAY
ASP.NET Core with Angular4 Development (Classroom)

10:30 AM-11:30 AM Weekend

Know More
27 MAY
ASP.NET MVC with Angular4 Development (Classroom)

4:00 PM-5:30 PM Weekend

Know More
25 MAY
ASP.NET MVC with Angular4 Development (Online)

09:00 PM-11:00 PM IST(+5.30 GMT)

Know More
25 MAY
ASP.NET MVC with Angular4 Development (Online)

09:00 PM-11:00 PM IST(+5.30 GMT)

20 MAY
Angular2 and Angular4 Development (Online)

08:00 AM-10:00 AM IST(+5.30 GMT) Weekend

20 MAY
NodeJS with Angular4 Development (Classroom)

5:00 PM-6:30 PM Weekend

8 MAY
ASP.NET MVC with Angular4 Development (Online)

07:00 AM-09:00 AM IST(+5:30 GMT) M/W/F

2 MAY
ASP.NET Core Development (Online)

07:00 AM - 09:00 AM IST( TUS, THR)

29 APR
ASP.NET MVC with Angular2 Development (Classroom)

9:30 AM-11:00 AM

22 APR
MEAN Stack 2 Development (Classroom)

11:00 AM-12:30 PM Weekend

10 APR
Master Class ASP.NET MVC 5 with Angular2 Plus Angular4 Development (Online)

09:00 PM - 11:00 PM IST (M/W/F)

25 MAR
Xamarin Forms : Build Cross-platform Apps (Classroom)

8:00 AM-9:30AM Weekend

BROWSE BY CATEGORY
 
 
LIKE US ON FACEBOOK
 
+