How To Use Join Operation With Multiple SQL Database Tables Using LINQ

In this article, I will demonstrate how we can perform join operation with multiple SQL database tables using Language-Integrated Query (LINQ).

Introduction

In this article, I will demonstrate how we can perform join operation with multiple SQL database tables using Language-Integrated Query (LINQ).

Step 1

Open SQL Server 2014 and create a database table

Table 1 - Customer

  1. CREATE TABLE [dbo].[Customer](  
  2.     [CustId] [int] IDENTITY(1000,1) NOT NULL,  
  3.     [CustomerName] [nvarchar](50) NULL,  
  4.     [PhoneNumber] [nvarchar](50) NULL,  
  5.     [Email] [nvarchar](50) NULL,  
  6.     [ShippingAddress] [nvarchar](max) NULL,  
  7.     [City] [nvarchar](50) NULL,  
  8.     [State] [nvarchar](50) NULL,  
  9.     [Country] [nvarchar](50) NULL,  
  10.     [PostalCode] [nvarchar](50) NULL,  
  11.  CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED   
  12. (  
  13.     [CustId] ASC  
  14. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  15. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
  16.   
  17. GO  

Table 2 - Product

  1. CREATE TABLE [dbo].[Product](  
  2.     [ProductId] [int] IDENTITY(100,1) NOT NULL,  
  3.     [ProductName] [nvarchar](50) NULL,  
  4.     [ProductDescription] [nvarchar](max) NULL,  
  5.     [ProductPrice] [money] NULL,  
  6.     [ProductCategoryId] [int] NULL,  
  7.  CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED   
  8. (  
  9.     [ProductId] ASC  
  10. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  11. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
  12.   
  13. GO  
  14.   
  15. ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_Category1] FOREIGN KEY([ProductCategoryId])  
  16. REFERENCES [dbo].[Category] ([CategoryId])  
  17. GO  
  18.   
  19. ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Category1]  
  20. GO  

Table 3 - Category

  1. CREATE TABLE [dbo].[Category](  
  2.     [CategoryId] [int] IDENTITY(1,1) NOT NULL,  
  3.     [CategoryName] [nvarchar](50) NULL,  
  4.     [CategoryDecription] [nvarchar](50) NULL,  
  5.  CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED   
  6. (  
  7.     [CategoryId] ASC  
  8. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  9. ) ON [PRIMARY]  
  10.   
  11. GO  

Table 4 - CustomerOrder

  1. CREATE TABLE [dbo].[CustomerOrder](  
  2.     [OrderId] [int] IDENTITY(1,1) NOT NULL,  
  3.     [CustomerId] [int] NULL,  
  4.     [ProductId] [int] NULL,  
  5.     [CategoryId] [int] NULL,  
  6.     [Quantity] [int] NULL,  
  7.     [OrderDate] [date] NULL,  
  8.  CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED   
  9. (  
  10.     [OrderId] ASC  
  11. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  12. ) ON [PRIMARY]  
  13.   
  14. GO  
  15.   
  16. ALTER TABLE [dbo].[CustomerOrder]  WITH CHECK ADD  CONSTRAINT [FK_CustomerOrder_Category] FOREIGN KEY([CategoryId])  
  17. REFERENCES [dbo].[Category] ([CategoryId])  
  18. GO  
  19.   
  20. ALTER TABLE [dbo].[CustomerOrder] CHECK CONSTRAINT [FK_CustomerOrder_Category]  
  21. GO  
  22.   
  23. ALTER TABLE [dbo].[CustomerOrder]  WITH CHECK ADD  CONSTRAINT [FK_CustomerOrder_Customer] FOREIGN KEY([CustomerId])  
  24. REFERENCES [dbo].[Customer] ([CustId])  
  25. GO  
  26.   
  27. ALTER TABLE [dbo].[CustomerOrder] CHECK CONSTRAINT [FK_CustomerOrder_Customer]  
  28. GO  
  29.   
  30. ALTER TABLE [dbo].[CustomerOrder]  WITH CHECK ADD  CONSTRAINT [FK_CustomerOrder_Product] FOREIGN KEY([ProductId])  
  31. REFERENCES [dbo].[Product] ([ProductId])  
  32. GO  
  33.   
  34. ALTER TABLE [dbo].[CustomerOrder] CHECK CONSTRAINT [FK_CustomerOrder_Product]  
  35. GO  

Step 2

Open Visual Studio 2015 and create a new console application with a meaningful name.

Step 3

Add Entity Framework now. For that, right click on Models folder, select Add, then select New Item, then click on it.

Screenshot for adding Entity Framework 1

How to use Join operation with multiple SQL database tables Using LINQ

After clicking on the new item, you will get a window; from there, select Data from the left panel and choose ADO.NET Entity Data Model, give it the name DBModels (this name is not mandatory you can give any name) and click on Add.

Screenshot for adding Entity Framework 2

How to use Join operation with multiple SQL database tables Using LINQ 

After you click on "Add a window", the wizard will open, choose EF Designer from a database and click next.

Screenshot for adding Entity Framework 3

How to use Join operation with multiple SQL database tables Using LINQ 

After clicking on Next a window will appear. Choose New Connection. Another window will appear, add your server name if it is local then enter a dot (.). Choose your database and click on OK.

Screenshot for adding Entity Framework 4

How to use Join operation with multiple SQL database tables Using LINQ 

The connection will be added. If you wish to save, connect as you want. You can change the name of your connection below. It will save connection in web config then click on Next.

Screenshot for adding Entity Framework 5

How to use Join operation with multiple SQL database tables Using LINQ 

After clicking on NEXT another window will appear --  choose database table name as shown in the below screenshot then click on Finish.

Screenshot for adding Entity Framework 6

How to use Join operation with multiple SQL database tables Using LINQ 

Entity Framework will be added and respective class gets generated under the Models folder.

Screenshot for adding Entity Framework 7

How to use Join operation with multiple SQL database tables Using LINQ 

Example for Multiple Tables Join

  1. using System;  
  2. using System.Linq;  
  3.   
  4. namespace MultiTableJoin_Demo  
  5. {  
  6.     class Program  
  7.     {  
  8.         static void Main(string[] args)  
  9.         {  
  10.             using (DBModel db=new DBModel())  
  11.             {  
  12.                 var results = from o in db.CustomerOrders  
  13.                               join c in db.Customers on o.CustomerId equals c.CustId  
  14.                               join p in db.Products on o.ProductId equals p.ProductId  
  15.                               join cate in db.Categories on o.CategoryId equals cate.CategoryId  
  16.                               select new {  
  17.                                   o.OrderId,  
  18.                                   c.CustomerName,  
  19.                                   c.PhoneNumber,  
  20.                                   c.ShippingAddress,  
  21.                                   c.PostalCode,  
  22.                                   p.ProductName,  
  23.                                   p.ProductPrice,  
  24.                                   o.Quantity,  
  25.                                   o.OrderDate  
  26.                               };  
  27.   
  28.                 foreach (var customerOrder in results)  
  29.                 {  
  30.                     Console.WriteLine(" Order ID:\t\t" + customerOrder.OrderId + "\n");  
  31.                     Console.WriteLine(" Customer Name:\t\t" + customerOrder.CustomerName + "\n");  
  32.                     Console.WriteLine(" Phone Number:\t\t" + customerOrder.PhoneNumber + "\n");  
  33.                     Console.WriteLine(" Shipping Address:\t" + customerOrder.ShippingAddress + "\n");  
  34.                     Console.WriteLine(" Postal Code:\t\t" + customerOrder.PostalCode + "\n");  
  35.                     Console.WriteLine(" Product Name:\t\t" + customerOrder.ProductName + "\n");  
  36.                     Console.WriteLine(" Product Price:\t\t" + customerOrder.ProductPrice + "\n");  
  37.                     Console.WriteLine(" Quantity:\t\t" + customerOrder.Quantity + "\n");  
  38.                     Console.WriteLine(" Total Amount:\t\t"+customerOrder.ProductPrice*customerOrder.Quantity + "\n");  
  39.                     Console.WriteLine(" Order Date:\t\t" + customerOrder.OrderDate + "\n");  
  40.                     Console.WriteLine("-----------------------------------------------------");  
  41.                 }  
  42.                 Console.ReadLine();           
  43.             }  
  44.         }  
  45.     }  
  46. }  

Output

How to use Join operation with multiple SQL database tables Using LINQ