How to populate a DataTable with an anonymous LINQ result

Question

I have the following LINQ query:

var timesheets = from timesheet in entities.Timesheets join timesheetTask in entities.Timesheet_Task on timesheet.Id equals timesheetTask.Timesheet_Id join task in entities.Tasks on timesheetTask.Task_Id equals task.Id join project in entities.Projects on task.Project_Id equals project.Id join department in entities.Departments on project.Department_Id equals department.Id where timesheet.Employee_Id == employeeId select new { date = timesheet.Date, taskName = task.Name, projectName = project.Name, projectDesc = project.Description, departmentName = department.Name, taskEstimatedHours = task.Estimated_Hours, timesheetHours = timesheetTask.Hours }; 

How can I put these results in a DataTable, which I can then bind to a DataGridView control?

This is what I am doing now:

  table.Columns.Add("date"); table.Columns.Add("taskName"); table.Columns.Add("projectName"); table.Columns.Add("projectDesc"); table.Columns.Add("departmentName"); table.Columns.Add("taskEstimatedHours"); table.Columns.Add("timesheetHours"); foreach (var item in timesheets) { table.Rows.Add(item.date, item.taskName, item.projectName, item.projectDesc, item.departmentName, item.taskEstimatedHours, item.timesheetHours); } } 

Update: Here is my updated code:

 DataTable table = new DataTable(); using (PTMS_DataEntities entities = new PTMS_DataEntities()) { var timesheets = from timesheet in entities.Timesheets join timesheetTask in entities.Timesheet_Task on timesheet.Id equals timesheetTask.Timesheet_Id join task in entities.Tasks on timesheetTask.Task_Id equals task.Id join project in entities.Projects on task.Project_Id equals project.Id join department in entities.Departments on project.Department_Id equals department.Id where timesheet.Employee_Id == employeeId select new { date = timesheet.Date, taskName = task.Name, projectName = project.Name, projectDesc = project.Description, departmentName = department.Name, taskEstimatedHours = task.Estimated_Hours, timesheetHours = timesheetTask.Hours }; table.Columns.Add("date", typeof(DateTime)); table.Columns.Add("taskName", typeof(string)); table.Columns.Add("projectName", typeof(string)); table.Columns.Add("projectDesc", typeof(string)); table.Columns.Add("departmentName", typeof(string)); table.Columns.Add("taskEstimatedHours", typeof(int)); table.Columns.Add("timesheetHours", typeof(int)); List<DataRow> list = new List<DataRow>(); foreach (var item in timesheets) { //table.Rows.Add(item.date, item.taskName, item.projectName, // item.projectDesc, item.departmentName, item.taskEstimatedHours, // item.timesheetHours); var row = table.NewRow(); row.SetField<DateTime>("date", item.date); row.SetField<string>("taskName", item.taskName); row.SetField<string>("projectName", item.projectName); row.SetField<string>("projectDesc", item.projectDesc); row.SetField<string>("departmentName", item.departmentName); row.SetField<int>("taskEstimatedHours", item.taskEstimatedHours); row.SetField<int>("timesheetHours", item.timesheetHours); list.Add(row); } table = list.CopyToDataTable(); } 

Here is the SQL query that I tested in SSMS (which should be the equivalent of a LINQ query):

 SELECT dbo.Department.Name, dbo.Task.Name AS Expr1, dbo.Task.Estimated_Hours, dbo.Timesheet.Date, dbo.Project.Name AS Expr2, dbo.Project.Description, dbo.Timesheet_Task.Date AS Expr3 FROM dbo.Department INNER JOIN dbo.Project ON dbo.Department.Id = dbo.Project.Department_Id INNER JOIN dbo.Task ON dbo.Project.Id = dbo.Task.Project_Id INNER JOIN dbo.Timesheet_Task ON dbo.Task.Id = dbo.Timesheet_Task.Task_Id INNER JOIN dbo.Timesheet ON dbo.Timesheet_Task.Timesheet_Id = dbo.Timesheet.Id 

Answer 1

If you really want to populate a DataTable:

 // your query var timesheets = ... // design table first DataTable table = new DataTable(); table.Columns.Add(new DataColumn { ColumnName = "TaskName", DataType = typeof(String); }); ... List<DataRow> list = new List<DataRow>(); foreach (var t in timesheets) { var row = table.NewRow(); row.SetField<string>("TaskName", t.taskName); // extension method from System.Data.DataSetExtensions.dll ... list.Add(row); } DataTable table = list.CopyToDataTable(); // extension method too 

---

Or more LINQ:

 timesheets .Select(t => { var row = table.NewRow(); ... return row; }) .CopyToDataTable(); 

---

Or in the same query syntax. Implement the method:

 static DataRow NewRow(DataRow row, string taskName, ....) { ... } 

Then run the query:

 (from ... where ... select NewRow(table.NewRow(), task.Name, ...) ).CopyToDataTable(); 

Answer 2

Call .ToList() .
The resulting List<T> can also be associated with a DataGridView and is easier to work with than with a DataTable.

Answer 3

I use FastMember for this purpose. It uses IL instead of reflection (much faster) to automatically iterate over all property and field values. Sample code from the site:

 IEnumerable<SomeType> data = ... var table = new DataTable(); using(var reader = ObjectReader.Create(data)) { table.Load(reader); }