Post
Ask Question

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.
 
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] [bigint] NULL,  
  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 = ON) ON [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] [int] NULL,  
  33.     [ProductId] [int] NULL,  
  34.     [CustomerId] [int] NULL,  
  35.     [Quantity] [int] NULL,  
  36.     [TotalAmount] [int] NULL,  
  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 = ON) ON [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] [int] NULL,  
  56.     [CategoryId] [int] NULL,  
  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 = ON) ON [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'[email protected]', N'New Delhi', 9898767654)  
  70. INSERT [dbo].[Customer] ([CustomerId], [CustomerName], [Email], [Address], [MobileNo]) VALUES (2, N'Rahul Singh', N'[email protected]', N'Noida', 7878787865)  
  71. INSERT [dbo].[Customer] ([CustomerId], [CustomerName], [Email], [Address], [MobileNo]) VALUES (3, N'SatishGupta', N'[email protected]', 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'[email protected]', 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  

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.
 
LINQ 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();  
LINQ inner join 
 
Inner Join more than two tables in LINQ
  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();  
LINQ Inner Join 
 

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.
 
LINQ 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();  
LINQ Left Outer Join 
 

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. 
 
LINQ 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])  
LINQ Right Outer Join 
 
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();  
LINQ Right Outer Join 
 

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;
 
LINQ 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. };  
LINQ Cross Join 
 
Thanks for reading this article, hope you enjoyed it.
 
Read more articles on SQL Server:


Similar Articles