SQL Join Query With LINQ

Mukesh Kumar
8y
10.9k
0
7

Article

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 betwee 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.

SQL Join Query with Linq
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

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

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();

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 checs 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();

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();

Cross Join

It is nothing but onlya Cartesian product of the two or more tables which are participating in join. The resulting records are equal tothe product of the number of records i the first table andthe number of record inthe 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.