Post
Ask Question

Join Two DataTables Using LINQ In ASP.Net C#

Background
 
I have read many forum posts regarding how to join two DataTables, so by considering those requirements I have decided to write this article. So let us learn step-by-step how to Join two DataTables using  LINQ
 
LINQ
 
Language-Integrated Query (LINQ) is a set of features introduced in Visual Studio 2008 that extends powerful query capabilities to the language syntax of C# and Visual Basic. Using LINQ you can manipulate the data similar to as is done using SQL queries . Let us learn practically how to convert a LINQ query result into a DataTable by creating a sample application.
 
Now create the project as:
  1. "Start" - "All Programs" - "Microsoft Visual Studio 2010".
  2. "File" - "New Project" - "C#" - "Empty Project" (to avoid adding a master page).
  3. Provide the Project name such as "JoinDataTableUsingLINQ" or another as you wish and specify the location.
  4. Then right-click on Solution Explorer and select "Add New Item" then select Default.aspx page.
  5. Drag and drop three Grid view to bind the records after Joining the two data table .
Now the Default.aspx source code will be as follows:
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>    
  2.     
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">    
  4. <html xmlns="http://www.w3.org/1999/xhtml">    
  5. <head runat="server">    
  6.     <title></title>    
  7. </head>    
  8. <body style="background-color: Blue">    
  9.     <h4 style="color: White">    
  10.         Article by Vithal Wadje</h4>    
  11.     <form id="form1" runat="server">    
  12.     <div>    
  13.         <h4 style="color: White">    
  14.             Product Table Records Before joining    
  15.         </h4>    
  16.         <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">    
  17.             <AlternatingRowStyle BackColor="White" />    
  18.             <EditRowStyle BackColor="#7C6F57" />    
  19.             <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />    
  20.             <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />    
  21.             <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />    
  22.             <RowStyle BackColor="#E3EAEB" />    
  23.             <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />    
  24.             <SortedAscendingCellStyle BackColor="#F8FAFA" />    
  25.             <SortedAscendingHeaderStyle BackColor="#246B61" />    
  26.             <SortedDescendingCellStyle BackColor="#D4DFE1" />    
  27.             <SortedDescendingHeaderStyle BackColor="#15524A" />    
  28.         </asp:GridView>    
  29.         <br />    
  30.         <h4 style="color: White">    
  31.             Tax Master Table Records Before joining    
  32.         </h4>    
  33.         <asp:GridView ID="GridView2" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">    
  34.             <AlternatingRowStyle BackColor="White" />    
  35.             <EditRowStyle BackColor="#7C6F57" />    
  36.             <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />    
  37.             <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />    
  38.             <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />    
  39.             <RowStyle BackColor="#E3EAEB" />    
  40.             <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />    
  41.             <SortedAscendingCellStyle BackColor="#F8FAFA" />    
  42.             <SortedAscendingHeaderStyle BackColor="#246B61" />    
  43.             <SortedDescendingCellStyle BackColor="#D4DFE1" />    
  44.             <SortedDescendingHeaderStyle BackColor="#15524A" />    
  45.         </asp:GridView>    
  46.         <br />    
  47.     </div>    
  48.     <h4 style="color: White">    
  49.         Tax and Product Table Records after joining    
  50.     </h4>    
  51.     <asp:GridView ID="GridView3" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">    
  52.         <AlternatingRowStyle BackColor="White" />    
  53.         <EditRowStyle BackColor="#7C6F57" />    
  54.         <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />    
  55.         <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />    
  56.         <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />    
  57.         <RowStyle BackColor="#E3EAEB" />    
  58.         <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />    
  59.         <SortedAscendingCellStyle BackColor="#F8FAFA" />    
  60.         <SortedAscendingHeaderStyle BackColor="#246B61" />    
  61.         <SortedDescendingCellStyle BackColor="#D4DFE1" />    
  62.         <SortedDescendingHeaderStyle BackColor="#15524A" />    
  63.     </asp:GridView>    
  64.     <br />    
  65.     </form>    
  66. </body>    
  67. </html> 
Now let's use the scenario that we have one Product table and one Tax Master Table and we want to join these tables depending on Tax Id, so let us create the tables instead of going to the DataBase.
 
Open the Default.aspx.cs Page and provide the following code to create the Product table as in the following:
  1. //creating Product DataTable    
  2.  DataTable dt = new DataTable();    
  3.  DataRow dr = null;    
  4.  dt.TableName = "Product";    
  5.  dt.Columns.Add("Id",typeof(int));    
  6.  dt.Columns[0].AutoIncrementSeed=1;    
  7.  dt.Columns[0].AutoIncrement = true;    
  8.  dt.Columns.Add("Product Name");    
  9.  dt.Columns.Add("Brand Name");    
  10.  dt.Columns.Add("Tax Id",typeof(int));    
  11.  dr = dt.NewRow();    
  12.  dr["Product Name"] = "Laptop";    
  13.  dr["Brand Name"] = "Samsung";    
  14.  dr["Tax Id"] = 1;    
  15.  dt.Rows.Add(dr);    
  16.  DataRow dr1 = null;    
  17.  dr1 = dt.NewRow();    
  18.  dr1["Product Name"] = "Mouse";    
  19.  dr1["Brand Name"] = "Dell";    
  20.  dr1["Tax Id"] = 1;    
  21.  dt.Rows.Add(dr1);
  22.  DataRow dr2 = null;    
  23.  dr2 = dt.NewRow();    
  24.  dr2["Product Name"] = "Mobile";    
  25.  dr2["Brand Name"] = "Apple";    
  26.  dr2["Tax Id"] = 1;    
  27.  dt.Rows.Add(dr2);    
  28.   
  29.  DataRow dr3 = null;    
  30.  dr3 = dt.NewRow();    
  31.  dr3["Product Name"] = "Book";    
  32.  dr3["Brand Name"] = "C# Corner Press";    
  33.  dr3["Tax Id"] = 2;    
  34.  dt.Rows.Add(dr3);
The Product table records will be such as follows:
 
 
Now let us create the Tax Master table using the following code:
  1. //creating Tax DataTable    
  2.  DataTable dtTax = new DataTable();  
  3.  dtTax.TableName = "taxmaster";    
  4.  dtTax.Columns.Add("Tax Id", typeof(int));    
  5.  dtTax.Columns[0].AutoIncrementSeed = 1;    
  6.  dtTax.Columns[0].AutoIncrement = true;    
  7.  dtTax.Columns.Add("Product Category");    
  8.  dtTax.Columns.Add("Charge", typeof(int));    
  9.  DataRow drtax = null;    
  10.  drtax = dtTax.NewRow();    
  11.  drtax["Product Category"] = "Electronics";    
  12.  drtax["Charge"] = 10;    
  13.  dtTax.Rows.Add(drtax);  
  14.  DataRow drtax1 = null;    
  15.  drtax1 = dtTax.NewRow();    
  16.  drtax1["Product Category"] = "Educational";    
  17.  drtax1["Charge"] = 8;    
  18.  dtTax.Rows.Add(drtax1); 
Now  the Tax Master table records will be such as follows: 
 
 
Now let us see the preceding two tables, one is Product and the other is Tax Master. From them we want to display the tax charge of each product and tax charges are in the Tax Master table, so to get it we need to join the preceding two tables using LINQ .
 
So let us write the code to join the Product and Tax Master tables as in the following:
  1. //joining Product and Tax DataTable   
  2.   
  3. var JoinResult = (from p in dt.AsEnumerable()  
  4.                   join t in dtTax.AsEnumerable()  
  5.                   on p.Field<int>("Tax Id") equals t.Field<int>("Tax Id")  
  6.                   select new  
  7.                   {  
  8.                       ProductName = p.Field<string>("Product Name"),  
  9.                       BrandName = p.Field<string>("Brand Name"),  
  10.                       ProductCategory = t.Field<string>("Product Category"),  
  11.                       TaxCharge = t.Field<int>("Charge")   
  12.                   }).ToList(); 
Now create a function to name the LINQResultToDataTable that converts the LINQ result to a table as in the following:
  1. public DataTable LINQResultToDataTable<T>(IEnumerable<T> Linqlist)      
  2. {      
  3.     DataTable dt = new DataTable();         
  4.     PropertyInfo[] columns = null;   
  5.     if (Linqlist == null) return dt;   
  6.     foreach (T Record in Linqlist)      
  7.     {  
  8.         if (columns == null)      
  9.         {      
  10.             columns = ((Type)Record.GetType()).GetProperties();      
  11.             foreach (PropertyInfo GetProperty in columns)      
  12.             {      
  13.                 Type IcolType = GetProperty.PropertyType;  
  14.                 if ((IcolType.IsGenericType) && (IcolType.GetGenericTypeDefinition()      
  15.                 == typeof(Nullable<>)))      
  16.                 {      
  17.                     IcolType = IcolType.GetGenericArguments()[0];      
  18.                 }  
  19.                 dt.Columns.Add(new DataColumn(GetProperty.Name, IcolType));      
  20.             }      
  21.         }  
  22.         DataRow dr = dt.NewRow();  
  23.         foreach (PropertyInfo p in columns)      
  24.         {      
  25.             dr[p.Name] = p.GetValue(Record, null) == null ? DBNull.Value : p.GetValue(Record, null);      
  26.         }  
  27.         dt.Rows.Add(dr);      
  28.     }      
  29.     return dt;      
  30. } 
The preceding function takes the LINQ query result and converts it into the Data Table. If you want to learn more about IEnumerable and the preceding function, refer to the following article of mine:
Now we have a common DataTable after joining the Product and Tax Master tables, so let us bind three grid views from the three Data Tables so we can understand the difference. The entire code of Defualt.aspx.cs will be such as follows.
 
Now run the application, then the GirdView records will be such as follows before joining: 
 
 
Now see the third GiridView records will be such as follows after joining:
 
 
From the preceding example it's clear that we can join two DataTables using LINQ.
 
Notes
  • Download the Zip file from the attachment for the full source code of the application.
  • You can also bind the DataTables from the database.
Summary
 
I hope this article is useful for all readers, if you have any suggestion then please contact me including beginners also. 


Similar Articles