How to populate DataTable with anonymous LINQ result

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 into a DataTable which I can then bind to a DataGridView control?

This is what I’m currently doing:

    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 I tested in SSMS (which should be the equivalent of the 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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

If you really want to populate 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 way:

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

Or in same query syntax. Implement a method:

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

Then query itself:

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

Method 2

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

Method 3

I’m using FastMember for this purpose. It uses IL instead of reflection (much faster) to iterate over all of the property and field values automatically. Code sample from the site:

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0