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
See Question&Answers more detail:
os