In LINQ, there is no option to casting IEnumerable to DataTable. We need to convert manually. For this, I have created one healper class which convert any type of IEnumerable to DataTable. Use the code below for this kind of requirements.
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Reflection;
namespace KannanDotNetReference.HelperClasses
{
static public class ConvertDataTable
{
public static DataTable ConvertToDataTable<T>(this System.Collections.Generic.IEnumerable<T> varList, CreateRowDelegate<T> fn)
{
DataTable dataTable = new DataTable();
// Variable for column names.
PropertyInfo[] tableColumns = null;
// To check whether more than one elements there in varList.
foreach (T rec in varList)
{
// Use reflection to get column names, to create table.
if (tableColumns == null)
{
tableColumns = ((Type)rec.GetType()).GetProperties();
foreach (PropertyInfo pi in tableColumns)
{
Type columnType = pi.PropertyType;
if ((columnType.IsGenericType) && (columnType.GetGenericTypeDefinition() == typeof(Nullable<>)))
{
columnType = columnType.GetGenericArguments()[0];
}
dataTable.Columns.Add(new DataColumn(pi.Name, columnType));
}
}
// Copying the IEnumerable value to DataRow and then added into DataTable.
DataRow dataRow = dataTable.NewRow();
foreach (PropertyInfo pi in tableColumns)
{
dataRow[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null);
}
dataTable.Rows.Add(dataRow);
}
return (dataTable);
}
public delegate object[] CreateRowDelegate<T>(T t);
}
}
See the below sample which fetch all the country with their customers count from Northwind database by using LINQ.
I have created Sample.aspx with one gridview control for displaying the result. See the code below: Sample.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Sample.aspx.cs" Inherits="KannanDotNetReference.Sample" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Kannan Samples : IEnumerabl to DataTable convertor</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
See the sample.aspx.cs code below which fetch the data from Northwind by using LINQ technique. In the code GetCountry() method is using ConvertToDataTable() helper method for converting IEnumerable result to datable. See the complete codeing of Sample.aspx.cs :
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using KannanDotNetReference.HelperClasses;
namespace KannanDotNetReference
{
public partial class Sample : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
GridView1.DataSource = GetCountry();
GridView1.DataBind();
}
private DataTable GetCountry()
{
var db = new NorthwindDataContext();
var table = db.KannanGetCountry();
DataTable dt = table.ConvertToDataTable(record => new object[] { table });
return dt;
}
}
}
Once we finished our coding part, next run this application. See the result below.
Note:
I have created one stored procedure for fetching the country with number of customers belongs to by using the below code:
CREATE PROCEDURE [dbo].[KannanGetCountry]
AS
SELECT
Country as 'CountryName',
COUNT(CustomerId) as 'CountryCount'
FROM Customers
GROUP BY Country
Happy Coding!!!