Introduction
LINQ is already popular with developers and it is getting more popular day by day for the certain advantages LINQ has. Now this tip is not about LINQ, but about something we need frequently for our day to day development - Converting LINQ query to DataTable.
First Things First, Planning
We will be developing a method which will accept the LINQ query, without any idea what it would be, as a parameter. Process its contents into a "DataTable" and return it. So, to receive the query, we will be using "dynamic" datatype which is very new to the context. Then, we will find out its "Property Information" and ready our DataTable for inserting data. After that, we will iterate through the query and populate our DataTable.
Second Thing Always Comes after the First, Coding
public static DataTable LinqQueryToDataTable(IEnumerable<dynamic> v)
{
var firstRecord = v.FirstOrDefault();
if (firstRecord == null)
return null;
PropertyInfo[] infos = firstRecord.GetType().GetProperties();
DataTable table = new DataTable();
foreach (var info in infos)
{
Type propType = info.PropertyType;
if (propType.IsGenericType
&& propType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
table.Columns.Add(info.Name, Nullable.GetUnderlyingType(propType));
}
else
{
table.Columns.Add(info.Name, info.PropertyType);
}
}
DataRow row;
foreach (var record in v)
{
row = table.NewRow();
for (int i = 0; i < table.Columns.Count; i++)
{
row[i] = infos[i].GetValue(record) != null ? infos[i].GetValue(record) : DBNull.Value;
}
table.Rows.Add(row);
}
table.AcceptChanges();
return table;
}
Last Thing
The last thing I can think of is calling the freshly baked method. We will begin with a class:
public class Item
{
public int Id { get; set; }
public decimal Price { get; set; }
public string Genre { get; set; }
public string Author { get; set; }
public string Director { get; set; }
}
Now, the LINQ query:
Item[] items = new Item[] {
new Item{Id = 1, Price = (decimal)13.50, Genre = "Comedy", Author = "Gustavo Achong"},
new Item{Id = 2, Price = (decimal)8.50, Genre = "Drama", Author = "Jessie Zeng"},
new Item{Id = 3, Price = (decimal)22.99, Genre = "Comedy", Director = "Marissa Barnes"},
new Item{Id = 4, Price = (decimal)13.40, Genre = "Action", Director = "Emmanuel Fernandez"}
};
var v = from i in items
where i.Genre == "Comedy"
select i;
DataTable dt = LinqQueryToDataTable(v);
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
