ASP.NET

Convert LINQ Query Result to Datatable

Background
 
I have read many forum post regarding how to convert LINQ var Query Result to Data table and also there is always need to Convert LINQ Query Result To Data table so by considering above requirement I have decided to write this article, so let us learn step by step how to Convert LINQ Query Result To Data table
 
 What is LINQ ?
LINQ stands for Language-Integrated Query 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 as similar SQL queries .
 
Let us learn it practically how to convert LINQ query result to Datatable by creating one simple application as

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 "ConvertLinqResultToDataTable" 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 One Button and Grid view to bind the records after creating the data table from LINQ query.
Now the Default.aspx source code will be as follows:
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ConvertLinqResultToDataTable.Default" %>  
  2.   
  3. <!DOCTYPE html>  
  4.   
  5. <html xmlns="http://www.w3.org/1999/xhtml">  
  6. <head runat="server">  
  7.     <title></title>  
  8. </head>  
  9. <body style="background-color: Blue">  
  10.     <h4 style="color: White">Article by Vithal Wadje</h4>  
  11.     <form id="form1" runat="server">  
  12.         <table style="margin-top: 60px; color: White">  
  13.   
  14.             <tr>  
  15.                 <td></td>  
  16.                 <td>  
  17.                     <asp:Button ID="btngetresult" runat="server" Text="Get Result"  
  18.                          />  
  19.                 </td>  
  20.             </tr>  
  21.   
  22.             <tr>  
  23.                 <td>  
  24.                     <asp:GridView ID="GridView1" runat="server"></asp:GridView>  
  25.                 </td>  
  26.             </tr>  
  27.         </table>  
  28.   
  29.     </form>  
  30. </body>  
  31. </html> 
Now open the Default.aspx.cs class file and create class named Customer with properties as
  1. public class Customer   
  2.     {  
  3.         private string _Name, _City, _Address;  
  4.   
  5.         public string Name  
  6.         {  
  7.             get { return _Name; }  
  8.             set { _Name = value; }  
  9.         }  
  10.   
  11.         public string City  
  12.         {  
  13.             get { return _City; }  
  14.             set { _City = value; }  
  15.         }  
  16.   
  17.         public string Address  
  18.         {  
  19.             get { return _Address; }  
  20.             set { _Address = value; }  
  21.         }  
  22.                     
  23.     } 
Now create the Array of type customer class and assign the values to the each property as
  1. Customer[] cust = new Customer[]  
  2.         {  
  3.           
  4.         new Customer{Name="Vithal Wadje",City="Mumbai",Address="Boriwali"},  
  5.         new Customer{Name="Sudhir Wadje",City="Latur",Address="Kabansangvi"},  
  6.         
  7.           
  8.         }; 
Now we have records to fire the LINQ query, you can also take the records from database but for ease of understanding I have not used database records.
 
Now create function to named LINQResultToDataTable which convert LINQ Result To DataTable as
  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 colType = GetProperty.PropertyType;  
  19.   
  20.                        if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()  
  21.                        == typeof(Nullable<>)))  
  22.                        {  
  23.                            colType = colType.GetGenericArguments()[0];  
  24.                        }  
  25.   
  26.                        dt.Columns.Add(new DataColumn(GetProperty.Name, colType));  
  27.                    }  
  28.                }  
  29.   
  30.                DataRow dr = dt.NewRow();  
  31.   
  32.                foreach (PropertyInfo pinfo in columns)  
  33.                {  
  34.                    dr[pinfo.Name] = pinfo.GetValue(Record, null) == null ? DBNull.Value : pinfo.GetValue  
  35.                    (Record, null);  
  36.                }  
  37.   
  38.                dt.Rows.Add(dr);  
  39.            }  
  40.            return dt;  
  41.        } 
The above function takes the LINQ query result and convert it into the Data Table, if you wants to learn more about IEnumerable and  above function how it work, refer my following article
Now Double click on Get Result Button and write the following code on Click event of button as
  1. protected void btngetResult_Click(object sender, EventArgs e)  
  2.       {  
  3.           //linq Query  
  4.           var query = from Customer s in cust  
  5.                       select s;  
  6.           
  7.           //stored result into datatable  
  8.           DataTable dt = LINQResultToDataTable(query);  
  9.           //bind gridview  
  10.           GridView1.DataSource = dt;  
  11.           GridView1.DataBind();  
  12.       } 
After creating the functions to convert LINQ query result to Data Table ,then the entire Default.aspx.cs code will be look like as follows
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data;  
  4. using System.Linq;  
  5. using System.Reflection;  
  6. using System.Web;  
  7. using System.Web.UI;  
  8. using System.Web.UI.WebControls;  
  9.   
  10. namespace ConvertLinqResultToDataTable  
  11. {  
  12.     public partial class Default : System.Web.UI.Page  
  13.     {  
  14.   
  15.         protected void Page_Load(object sender, EventArgs e)  
  16.         {  
  17.   
  18.         }  
  19.   
  20.   
  21.         Customer[] cust = new Customer[]  
  22.         {  
  23.           
  24.         new Customer{Name="Vithal Wadje",City="Mumbai",Address="Boriwali"},  
  25.         new Customer{Name="Sudhir Wadje",City="Latur",Address="Kabansangvi"},  
  26.         
  27.           
  28.         };  
  29.   
  30.         
  31.         protected void btngetResult_Click(object sender, EventArgs e)  
  32.         {  
  33.             //linq Query  
  34.             var query = from Customer s in cust  
  35.                         select s;  
  36.             
  37.             //stored result into datatable  
  38.             DataTable dt = LINQResultToDataTable(query);  
  39.             //bind gridview  
  40.             GridView1.DataSource = dt;  
  41.             GridView1.DataBind();  
  42.         }  
  43.   
  44.   
  45.   
  46.         public DataTable LINQResultToDataTable<T>(IEnumerable<T> Linqlist)  
  47.         {  
  48.             DataTable dt = new DataTable();  
  49.   
  50.              
  51.             PropertyInfo[] columns = null;  
  52.   
  53.             if (Linqlist == null) return dt;  
  54.   
  55.             foreach (T Record in Linqlist)  
  56.             {  
  57.                   
  58.                 if (columns == null)  
  59.                 {  
  60.                     columns = ((Type)Record.GetType()).GetProperties();  
  61.                     foreach (PropertyInfo GetProperty in columns)  
  62.                     {  
  63.                         Type colType = GetProperty.PropertyType;  
  64.   
  65.                         if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()  
  66.                         == typeof(Nullable<>)))  
  67.                         {  
  68.                             colType = colType.GetGenericArguments()[0];  
  69.                         }  
  70.   
  71.                         dt.Columns.Add(new DataColumn(GetProperty.Name, colType));  
  72.                     }  
  73.                 }  
  74.   
  75.                 DataRow dr = dt.NewRow();  
  76.   
  77.                 foreach (PropertyInfo pinfo in columns)  
  78.                 {  
  79.                     dr[pinfo.Name] = pinfo.GetValue(Record, null) == null ? DBNull.Value : pinfo.GetValue  
  80.                     (Record, null);  
  81.                 }  
  82.   
  83.                 dt.Rows.Add(dr);  
  84.             }  
  85.             return dt;  
  86.         }  
  87.   
  88.     }  
  89.   
  90.     public class Customer   
  91.     {  
  92.         private string _Name, _City, _Address;  
  93.   
  94.         public string Name  
  95.         {  
  96.             get { return _Name; }  
  97.             set { _Name = value; }  
  98.         }  
  99.   
  100.         public string City  
  101.         {  
  102.             get { return _City; }  
  103.             set { _City = value; }  
  104.         }  
  105.   
  106.         public string Address  
  107.         {  
  108.             get { return _Address; }  
  109.             set { _Address = value; }  
  110.         }  
  111.       
  112.     }  
  113. } 
Now we have written all logic to convert LINQ query result to Data Table, now run the application the initial screen will be look like as follows
 
 
 
Click on above Get Result button, then LINQ Result will convert into Data Table and it will displayed into the Grid view are as follows
 
 
 
From above example its clear that we can convert LINQ query Result to DataTable. 
 
Note
  • Download the Zip file from the attachment for the full source code of the application.
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