ASP.NET

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.   
  23.   
  24.        DataRow dr2 = null;  
  25.        dr2 = dt.NewRow();  
  26.        dr2["Product Name"] = "Mobile";  
  27.        dr2["Brand Name"] = "Apple";  
  28.        dr2["Tax Id"] = 1;  
  29.        dt.Rows.Add(dr2);  
  30.   
  31.        DataRow dr3 = null;  
  32.        dr3 = dt.NewRow();  
  33.        dr3["Product Name"] = "Book";  
  34.        dr3["Brand Name"] = "C# Corner Press";  
  35.        dr3["Tax Id"] = 2;  
  36.        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.          
  4.        dtTax.TableName = "taxmaster";  
  5.        dtTax.Columns.Add("Tax Id", typeof(int));  
  6.        dtTax.Columns[0].AutoIncrementSeed = 1;  
  7.        dtTax.Columns[0].AutoIncrement = true;  
  8.        dtTax.Columns.Add("Product Category");  
  9.        dtTax.Columns.Add("Charge", typeof(int));  
  10.        DataRow drtax = null;  
  11.        drtax = dtTax.NewRow();  
  12.        drtax["Product Category"] = "Electronics";  
  13.        drtax["Charge"] = 10;  
  14.        dtTax.Rows.Add(drtax);  
  15.   
  16.        DataRow drtax1 = null;  
  17.        drtax1 = dtTax.NewRow();  
  18.        drtax1["Product Category"] = "Educational";  
  19.        drtax1["Charge"] = 8;  
  20.        dtTax.Rows.Add(drtax1);  
  21.        
 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.   
  13.   
  14.                           }).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.     
  5.                
  6.             PropertyInfo[] columns = null;    
  7.     
  8.             if (Linqlist == null) return dt;    
  9.     
  10.             foreach (T Record in Linqlist)    
  11.             {    
  12.                     
  13.                 if (columns == null)    
  14.                 {    
  15.                     columns = ((Type)Record.GetType()).GetProperties();    
  16.                     foreach (PropertyInfo GetProperty in columns)    
  17.                     {    
  18.                         Type IcolType = GetProperty.PropertyType;    
  19.     
  20.                         if ((IcolType.IsGenericType) && (IcolType.GetGenericTypeDefinition()    
  21.                         == typeof(Nullable<>)))    
  22.                         {    
  23.                             IcolType = IcolType.GetGenericArguments()[0];    
  24.                         }    
  25.     
  26.                         dt.Columns.Add(new DataColumn(GetProperty.Name, IcolType));    
  27.                     }    
  28.                 }    
  29.     
  30.                 DataRow dr = dt.NewRow();    
  31.     
  32.                 foreach (PropertyInfo p in columns)    
  33.                 {    
  34.                     dr[p.Name] = p.GetValue(Record, null) == null ? DBNull.Value : p.GetValue    
  35.                     (Record, null);    
  36.                 }    
  37.     
  38.                 dt.Rows.Add(dr);    
  39.             }    
  40.             return dt;    
  41.         }   
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.
 

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs