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

Customer Table

Order Table

Order Table

Product Table

Product Table

Use the following scripts to create database and corresponding tables,

  1. USE [Inventory]  
  2. GO  
  3. /****** Object:  Table [dbo].[Customer]    Script Date: 1/4/2sql-join-query-with-linq16 11:2sql-join-query-with-linq:47 PM ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. SET ANSI_PADDING ON  
  9. GO  
  10. CREATE TABLE [dbo].[Customer](  
  11.     [CustomerId] [int] IDENTITY(1,1) NOT NULL,  
  12.     [CustomerName] [varchar](6sql-join-query-with-linq) NULL,  
  13.     [Email] [varchar](1sql-join-query-with-linqsql-join-query-with-linq) NULL,  
  14.     [Address] [varchar](255) NULL,  
  15.     [MobileNo] [bigintNULL,  
  16.  CONSTRAINT [PK__Customer__A4AE64D8D4F5Bsql-join-query-with-linq13] PRIMARY KEY CLUSTERED   
  17. (  
  18.     [CustomerId] ASC  
  19. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  20. ON [PRIMARY]  
  21.   
  22. GO  
  23. SET ANSI_PADDING OFF  
  24. GO  
  25. /****** Object:  Table [dbo].[Orders]    Script Date: 1/4/2sql-join-query-with-linq16 11:2sql-join-query-with-linq:47 PM ******/  
  26. SET ANSI_NULLS ON  
  27. GO  
  28. SET QUOTED_IDENTIFIER ON  
  29. GO  
  30. CREATE TABLE [dbo].[Orders](  
  31.     [OrderId] [int] IDENTITY(1,1) NOT NULL,  
  32.     [OrderNumber] [intNULL,  
  33.     [ProductId] [intNULL,  
  34.     [CustomerId] [intNULL,  
  35.     [Quantity] [intNULL,  
  36.     [TotalAmount] [intNULL,  
  37.     [OrderDate] [datetime] NULL,  
  38. PRIMARY KEY CLUSTERED   
  39. (  
  40.     [OrderId] ASC  
  41. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  42. ON [PRIMARY]  
  43.   
  44. GO  
  45. /****** Object:  Table [dbo].[Product]    Script Date: 1/4/2sql-join-query-with-linq16 11:2sql-join-query-with-linq:47 PM ******/  
  46. SET ANSI_NULLS ON  
  47. GO  
  48. SET QUOTED_IDENTIFIER ON  
  49. GO  
  50. SET ANSI_PADDING ON  
  51. GO  
  52. CREATE TABLE [dbo].[Product](  
  53.     [ProductId] [int] IDENTITY(1,1) NOT NULL,  
  54.     [ProductName] [varchar](5sql-join-query-with-linq) NOT NULL,  
  55.     [UnitPrice] [intNULL,  
  56.     [CategoryId] [intNULL,  
  57.     [AddedDate] [datetime] NULL,  
  58. PRIMARY KEY CLUSTERED   
  59. (  
  60.     [ProductId] ASC  
  61. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  62. ON [PRIMARY]  
  63.   
  64. GO  
  65. SET ANSI_PADDING OFF  
  66. GO  
  67. SET IDENTITY_INSERT [dbo].[Customer] ON   
  68.   
  69. INSERT [dbo].[Customer] ([CustomerId], [CustomerName], [Email], [Address], [MobileNo]) VALUES (1, N'Mukesh Kumar', N'MukeshKumar@gmail.com', N'New Delhi', 9898767654)  
  70. INSERT [dbo].[Customer] ([CustomerId], [CustomerName], [Email], [Address], [MobileNo]) VALUES (2, N'Rahul Singh', N'RahulSingh@gmail.com', N'Noida', 7878787865)  
  71. INSERT [dbo].[Customer] ([CustomerId], [CustomerName], [Email], [Address], [MobileNo]) VALUES (3, N'SatishGupta', N'SatishGupta@gmail.com', N'Mumbai', 9198765432)  
  72. INSERT [dbo].[Customer] ([CustomerId], [CustomerName], [Email], [Address], [MobileNo]) VALUES (4, N'VishalSingh', N'VishalSingh', N'Patna', 7654324566)  
  73. INSERT [dbo].[Customer] ([CustomerId], [CustomerName], [Email], [Address], [MobileNo]) VALUES (5, N'VinayPathak', N'VinayPathak@gmail.com', N'Kanpur', 9898989765)  
  74. SET IDENTITY_INSERT [dbo].[Customer] OFF  
  75. SET IDENTITY_INSERT [dbo].[Orders] ON   
  76.   
  77. 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))  
  78. 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))  
  79. 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))  
  80. SET IDENTITY_INSERT [dbo].[Orders] OFF  
  81. SET IDENTITY_INSERT [dbo].[Product] ON   
  82.   
  83. 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))  
  84. 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))  
  85. 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))  
  86. 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))  
  87. 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.

Inner Join

Basically in SQL, we use the INNER JOIN keyword to make relationship between both tables.
  1. SELECT [t1].[OrderId], [t1].[OrderNumber], [tsql-join-query-with-linq].[ProductName], [t1].[Quantity], [t1].[TotalAmount], [t1].[OrderDate]  
  2. FROM [Product] AS [tsql-join-query-with-linq]  
  3. INNER JOIN [Orders] AS [t1]   
  4. ON ([tsql-join-query-with-linq].[ProductId]) = [t1].[ProductId]  
The following is the Linq query for above SQL query.
  1. var result = (from p in Products join o in Orders on p.ProductId equals o.ProductId select new  
  2. {  
  3.     o.OrderId,  
  4.         o.OrderNumber,  
  5.         p.ProductName,  
  6.         o.Quantity,  
  7.         o.TotalAmount,  
  8.         o.OrderDate  
  9. }).ToList();  
query reult

Inner Join more than two tables
  1. SELECT [t1].[OrderId], [t1].[OrderNumber], [tsql-join-query-with-linq].[ProductName], [t1].[Quantity], [t1].[TotalAmount], [t1].[OrderDate], [t2].[CustomerName], [t2].[MobileNo], [t2].[Address]  
  2. FROM [Product] AS [tsql-join-query-with-linq]  
  3. INNER JOIN [Orders] AS [t1]  
  4. ON ([tsql-join-query-with-linq].[ProductId]) = [t1].[ProductId]  
  5. INNER JOIN [Customer] AS [t2]  
  6. ON [t1].[CustomerId] = ([t2].[CustomerId])  
The following is the Linq query for the above SQL query.
  1. 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  
  2. {  
  3.     o.OrderId,  
  4.         o.OrderNumber,  
  5.         p.ProductName,  
  6.         o.Quantity,  
  7.         o.TotalAmount,  
  8.         o.OrderDate,  
  9.         c.CustomerName,  
  10.         c.MobileNo,  
  11.         c.Address  
  12. }).ToList();  
Check list

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.

Left Outer Join
  1. 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]  
  2. FROM [Product] AS [tsql-join-query-with-linq]  
  3. 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.
  1. 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  
  2. {  
  3.     p.ProductId,  
  4.         OrderId = (int ? ) t.OrderId,  
  5.         t.OrderNumber,  
  6.         p.ProductName,  
  7.         Quantity = (int ? ) t.Quantity,  
  8.         t.TotalAmount,  
  9.         t.OrderDate  
  10. }).ToList();  
See list

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.

Right Outer Join
  1. 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]  
  2. FROM [Orders] AS [tsql-join-query-with-linq]  
  3. RIGHT OUTER JOIN [Product] AS [t1] ON [tsql-join-query-with-linq].[ProductId] = ([t1].[ProductId])  
See result

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.
  1. 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  
  2. {  
  3.     t.ProductId,  
  4.         o.OrderId,  
  5.         t.ProductName,  
  6.         o.Quantity,  
  7.         o.TotalAmount,  
  8.         o.OrderDate  
  9. }).ToList();  
List

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;

Cross Join
  1. 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]  
  2. FROM [Orders] AS [tsql-join-query-with-linq] CROSS JOIN [Product] AS [t1]  
The following is the Linq query for above SQL query.
  1. var result = from o in Orders  
  2. from p in Products  
  3. select new  
  4. {  
  5.     p.ProductId,  
  6.         o.OrderId,  
  7.         p.ProductName,  
  8.         o.Quantity,  
  9.         o.TotalAmount,  
  10.         o.OrderDate  
  11. };  
Table

Thanks for reading this article, hope you enjoyed it.
 
Read more articles on SQL Server: