SQL Join Query With LINQ

As we know the JOIN clause is very useful when merging more than two table or object data into a single unit. It combines different source elements into one and also creates the relationship between them. Using the join, you can grab the data based on your conditions. So, today I am going to show you how to form a query in SQL as well as LINQ for fetching the data.

Earlier we are using the JOIN in SQL to fetch the data from different tables from the database. SQL stands for Structured Query Language. This is query-based language to work on a relational-based database. In SQL, we manage the relationship between tables using the foreign key and primary key.

There are different types of join in SQL and these are Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, and Cross Join.

But after introducing Linq with C# 3.0, there were huge changes in the programming world. Now, most of the developers use Linq for getting the data from the object. Linq stands for Language Integrated Query. It provides the facilities to access from in memory objects, database, Xml, and any other data source.

In this article, I am going to show the Join on Inventory database between customer and order table. Customer and Order tables are engaged to use CustomerId as primary key and foreign key. You can use the following scripts to generate the Inventory database.

Customer Table

Order Table

Product Table

Use the following scripts to create database and corresponding tables,

USE [Inventory]
GO
/****** Object: Table [dbo].[Customer] Script Date: 1/4/2sql-join-query-with-linq16 11:2sql-join-query-with-linq:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Customer](
[CustomerId] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [varchar](6sql-join-query-with-linq) NULL,
[Email] [varchar](1sql-join-query-with-linqsql-join-query-with-linq) NULL,
[Address] [varchar](255) NULL,
[MobileNo] [bigint] NULL,
CONSTRAINT [PK__Customer__A4AE64D8D4F5Bsql-join-query-with-linq13] PRIMARY KEY CLUSTERED
(
[CustomerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Orders] Script Date: 1/4/2sql-join-query-with-linq16 11:2sql-join-query-with-linq:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
[OrderId] [int] IDENTITY(1,1) NOT NULL,
[OrderNumber] [int] NULL,
[ProductId] [int] NULL,
[CustomerId] [int] NULL,
[Quantity] [int] NULL,
[TotalAmount] [int] NULL,
[OrderDate] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[OrderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Product] Script Date: 1/4/2sql-join-query-with-linq16 11:2sql-join-query-with-linq:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Product](
[ProductId] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [varchar](5sql-join-query-with-linq) NOT NULL,
[UnitPrice] [int] NULL,
[CategoryId] [int] NULL,
[AddedDate] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Customer] ON
INSERT [dbo].[Customer] ([CustomerId], [CustomerName], [Email], [Address], [MobileNo]) VALUES (1, N'Mukesh Kumar', N'[email protected]', N'New Delhi', 9898767654)
INSERT [dbo].[Customer] ([CustomerId], [CustomerName], [Email], [Address], [MobileNo]) VALUES (2, N'Rahul Singh', N'[email protected]', N'Noida', 7878787865)
INSERT [dbo].[Customer] ([CustomerId], [CustomerName], [Email], [Address], [MobileNo]) VALUES (3, N'SatishGupta', N'[email protected]', N'Mumbai', 9198765432)
INSERT [dbo].[Customer] ([CustomerId], [CustomerName], [Email], [Address], [MobileNo]) VALUES (4, N'VishalSingh', N'VishalSingh', N'Patna', 7654324566)
INSERT [dbo].[Customer] ([CustomerId], [CustomerName], [Email], [Address], [MobileNo]) VALUES (5, N'VinayPathak', N'[email protected]', N'Kanpur', 9898989765)
SET IDENTITY_INSERT [dbo].[Customer] OFF
SET IDENTITY_INSERT [dbo].[Orders] ON
INSERT [dbo].[Orders] ([OrderId], [OrderNumber], [ProductId], [CustomerId], [Quantity], [TotalAmount], [OrderDate]) VALUES (1, 8sql-join-query-with-linqsql-join-query-with-linq1, 4, 1, 4, 12sql-join-query-with-linqsql-join-query-with-linqsql-join-query-with-linqsql-join-query-with-linq, CAST(N'2sql-join-query-with-linq15-11-sql-join-query-with-linq3 22:21:13.143' AS DateTime))
INSERT [dbo].[Orders] ([OrderId], [OrderNumber], [ProductId], [CustomerId], [Quantity], [TotalAmount], [OrderDate]) VALUES (2, 8sql-join-query-with-linqsql-join-query-with-linq2, 4, 2, 1, 3sql-join-query-with-linqsql-join-query-with-linqsql-join-query-with-linqsql-join-query-with-linq, CAST(N'2sql-join-query-with-linq15-11-13 22:21:13.143' AS DateTime))
INSERT [dbo].[Orders] ([OrderId], [OrderNumber], [ProductId], [CustomerId], [Quantity], [TotalAmount], [OrderDate]) VALUES (3, 8sql-join-query-with-linqsql-join-query-with-linq3, 2, 3, 2, 4sql-join-query-with-linqsql-join-query-with-linqsql-join-query-with-linqsql-join-query-with-linq, CAST(N'2sql-join-query-with-linq15-12-15 22:21:13.143' AS DateTime))
SET IDENTITY_INSERT [dbo].[Orders] OFF
SET IDENTITY_INSERT [dbo].[Product] ON
INSERT [dbo].[Product] ([ProductId], [ProductName], [UnitPrice], [CategoryId], [AddedDate]) VALUES (1, N'Samsung', 3sql-join-query-with-linqsql-join-query-with-linqsql-join-query-with-linqsql-join-query-with-linq, 3, CAST(N'2sql-join-query-with-linq15-sql-join-query-with-linq5-13 22:21:13.143' AS DateTime))
INSERT [dbo].[Product] ([ProductId], [ProductName], [UnitPrice], [CategoryId], [AddedDate]) VALUES (2, N'Noika', 2sql-join-query-with-linqsql-join-query-with-linqsql-join-query-with-linqsql-join-query-with-linq, 4, CAST(N'2sql-join-query-with-linq15-sql-join-query-with-linq5-sql-join-query-with-linq3 22:21:13.143' AS DateTime))
INSERT [dbo].[Product] ([ProductId], [ProductName], [UnitPrice], [CategoryId], [AddedDate]) VALUES (3, N'Sony', 15sql-join-query-with-linqsql-join-query-with-linqsql-join-query-with-linq, 5, CAST(N'2sql-join-query-with-linq15-sql-join-query-with-linq1-24 22:21:13.143' AS DateTime))
INSERT [dbo].[Product] ([ProductId], [ProductName], [UnitPrice], [CategoryId], [AddedDate]) VALUES (4, N'Apple', 45sql-join-query-with-linqsql-join-query-with-linqsql-join-query-with-linq, 6, CAST(N'2sql-join-query-with-linq16-sql-join-query-with-linq1-sql-join-query-with-linq3 22:21:13.143' AS DateTime))
SET IDENTITY_INSERT [dbo].[Product] OFF

LINQ Inner Join

Sometimes, it is required to send data from two or more tables or objects in a single unit based on your provided information. Inner Join produces the result from two or more than two tables. So, basically we are meant to get the records from both tables based on matching conditions.

Basically in SQL, we use the INNER JOIN keyword to make relationship between both tables.

SELECT [t1].[OrderId], [t1].[OrderNumber], [tsql-join-query-with-linq].[ProductName], [t1].[Quantity], [t1].[TotalAmount], [t1].[OrderDate]
FROM [Product] AS [tsql-join-query-with-linq]
INNER JOIN [Orders] AS [t1]
ON ([tsql-join-query-with-linq].[ProductId]) = [t1].[ProductId]

The following is the Linq query for above SQL query.

var result = (from p in Products join o in Orders on p.ProductId equals o.ProductId select new
{
o.OrderId,
o.OrderNumber,
p.ProductName,
o.Quantity,
o.TotalAmount,
o.OrderDate
}).ToList();

Inner Join more than two tables in LINQ

SELECT [t1].[OrderId], [t1].[OrderNumber], [tsql-join-query-with-linq].[ProductName], [t1].[Quantity], [t1].[TotalAmount], [t1].[OrderDate], [t2].[CustomerName], [t2].[MobileNo], [t2].[Address]
FROM [Product] AS [tsql-join-query-with-linq]
INNER JOIN [Orders] AS [t1]
ON ([tsql-join-query-with-linq].[ProductId]) = [t1].[ProductId]
INNER JOIN [Customer] AS [t2]
ON [t1].[CustomerId] = ([t2].[CustomerId])

The following is the Linq query for the above SQL query.

var result = (from p in Products join o in Orders on p.ProductId equals o.ProductId join c in Customers on o.CustomerId equals c.CustomerId select new
{
o.OrderId,
o.OrderNumber,
p.ProductName,
o.Quantity,
o.TotalAmount,
o.OrderDate,
c.CustomerName,
c.MobileNo,
c.Address
}).ToList();

LINQ Left Outer Join

It includes all rows from the left table and matching rows from the right table. We process the query with some condition which contains the unique column in both tables. So, first it takes all the data from left side table and then checks the condition and on the basis of condition brings the matching data from right table.

SELECT [tsql-join-query-with-linq].[ProductId], [t1].[OrderId] AS [OrderId], [t1].[OrderNumber] AS [OrderNumber], [tsql-join-query-with-linq].[ProductName], [t1].[Quantity] AS [Quantity], [t1].[TotalAmount] AS [TotalAmount], [t1].[OrderDate] AS [OrderDate]
FROM [Product] AS [tsql-join-query-with-linq]
LEFT OUTER JOIN [Orders] AS [t1] ON ([tsql-join-query-with-linq].[ProductId]) = [t1].[ProductId]

The following is the Linq query for above SQL query.

var result = (from p in Products join o in Orders on p.ProductId equals o.ProductId into temp from t in temp.DefaultIfEmpty() select new
{
p.ProductId,
OrderId = (int ? ) t.OrderId,
t.OrderNumber,
p.ProductName,
Quantity = (int ? ) t.Quantity,
t.TotalAmount,
t.OrderDate
}).ToList();

LINQ Right Outer Join

In this join, all the data from right tables come in records as well as matching records with left table. Right Join basically brings all the records from right table, either the matching records found in left table or not.

SELECT [t1].[ProductId] AS [ProductId], [tsql-join-query-with-linq].[OrderId], [t1].[ProductName] AS [ProductName], [tsql-join-query-with-linq].[Quantity], [tsql-join-query-with-linq].[TotalAmount], [tsql-join-query-with-linq].[OrderDate]
FROM [Orders] AS [tsql-join-query-with-linq]
RIGHT OUTER JOIN [Product] AS [t1] ON [tsql-join-query-with-linq].[ProductId] = ([t1].[ProductId])

Actually Linq query is not possible for right outer join. Some developers say that only changes the order of tables as entity in Linq query, we can get the output as right outer join but this is not true.

You can see the following Linq query and the result for this query and above SQL query is not the same.

var result = (from o in Orders join p in Products on o.ProductId equals p.ProductId into temp from t in temp.DefaultIfEmpty() select new
{
t.ProductId,
o.OrderId,
t.ProductName,
o.Quantity,
o.TotalAmount,
o.OrderDate
}).ToList();

LINQ Cross Join

It is nothing but only a Cartesian product of the two or more tables which are participating in join. The resulting records are equal to the product of the number of records in the first table and the number of record in the second table. If you will use the where clause with cross join, it will behave like Inner Join;

SELECT [t1].[ProductId], [tsql-join-query-with-linq].[OrderId], [t1].[ProductName], [tsql-join-query-with-linq].[Quantity], [tsql-join-query-with-linq].[TotalAmount], [tsql-join-query-with-linq].[OrderDate]
FROM [Orders] AS [tsql-join-query-with-linq] CROSS JOIN [Product] AS [t1]

The following is the Linq query for above SQL query.

var result = from o in Orders
from p in Products
select new
{
p.ProductId,
o.OrderId,
p.ProductName,
o.Quantity,
o.TotalAmount,
o.OrderDate
};

Thanks for reading this article, hope you enjoyed it.