How to convert a LINQ query result to a DataTable dynamically?
There are solutions where you create another class and specify the column names, but I want the flexibility to change the LINQ structure like column names, column quantities, and have a DataTable generated with the columns names automatically.
Thanks
I've included an extension method that I use with SqlBulkCopy that should do the job, but I'd like to ask why you want to this conversion. There are a very limited number of cases (SqlBulkCopy being one) where a list of objects can't do everything a datatable can. You can use them as binding sources for most controls ... just curious.
public static DataTable toDataTable<T>(this IEnumerable<T> value, List<string> exclusionList) where T : class { var dataTable = new DataTable(); var type = typeof(T); var properties = type.GetProperties().Where(x => !exclusionList.Contains(x.Name)).ToList(); foreach (var propertyInfo in properties) { var propertyType = propertyInfo.PropertyType; if (!propertyType.IsScalar()) continue; var nullableType = Nullable.GetUnderlyingType(propertyType); propertyType = nullableType ?? propertyType; var dataColumn = new DataColumn(propertyInfo.Name, propertyType); if (nullableType != null) dataColumn.AllowDBNull = true; dataTable.Columns.Add(dataColumn); } foreach (var row in value) { var dataRow = dataTable.NewRow(); foreach (var property in properties) { var safeValue = property.GetValue(row, null) ?? DBNull.Value; dataRow[property.Name] = safeValue; } dataTable.Rows.Add(dataRow); } return dataTable; } 
Look into the MoreLinq Nuget package. It has a function ToDataTable()
var LinqResults = from ......; DataTable dt_Results = LinqResults.ToDataTable(); https://code.google.com/p/morelinq/
It has other VERY useful functions as well: https://code.google.com/p/morelinq/wiki/OperatorsOverview
They key is to use the LINQ query result as its Implemented IList interface. If you receive the result as a parameter on a method as an IList object, you can access its columns and rows, this way:
var props = item.GetType().GetProperties(); Refer to this example, it's a small class which please note the it just abstracts the creation of the DataTable, and there is a static method inside called "LINQToDataTable" which you should use.
Step 1, create a class called "GridHelper" (uses System.Data for DataTable structure)
public class GridHelper { private DataTable baseDt; public GridHelper(string tableName) { baseDt = new DataTable(tableName); } public DataTable getDataTable() { return baseDt; } public object[,] getObjToFill() { object[,] obj = new object[baseDt.Columns.Count, 2]; for (int i = 0; i < baseDt.Columns.Count; i++) { obj[i, 0] = baseDt.Columns[i].ColumnName; } return obj; } public void addColumn(string colName, Type valueType) { baseDt.Columns.Add(colName, valueType); } public void addRow(object[,] values) { DataRow newRow = baseDt.NewRow(); for (int i = 0; i < values.Length / 2; i++) { bool colFound = false; for (int j = 0; j < baseDt.Columns.Count; j++) { if (baseDt.Columns[j].ColumnName == values[i, 0].ToString()) { colFound = true; break; } } if (colFound == false) { throw new Exception("The column " + values[i, 0].ToString() + " has not been added yet."); } newRow[values[i, 0].ToString()] = values[i, 1]; } baseDt.Rows.Add(newRow); } public static DataTable LINQToDataTable<T>(T objToList) where T : System.Collections.IList { GridHelper ghResult = new GridHelper("Report"); foreach (Object item in objToList) { var props = item.GetType().GetProperties(); foreach (var prop in props) { ghResult.addColumn(prop.Name, typeof(string)); //prop.Name //prop.GetValue(item) } break; } object[,] obj = ghResult.getObjToFill(); foreach (Object item in objToList) { var props = item.GetType().GetProperties(); int index = 0; foreach (var prop in props) { //ReportValue(prop.Name, prop.GetValue(item, null)); //prop.Name obj[index, 1] = prop.GetValue(item); index++; } ghResult.addRow(obj); } return ghResult.getDataTable(); } } Usage:
var listaReporte = (from t in dbContext.TablaPruebas select new { Name = t.name, Score = t.score } ) .ToList(); DataTable dt = Library.GridHelper.LINQToDataTable(listaReporte); And that is, use your DataTable as you wish, on a GridView or DataGridView