如何填充的DataTable匿名LINQ结果 [英] How to populate DataTable with anonymous LINQ result

问题描述

我有以下LINQ查询：

  VAR时间表从时间表=在entities.Timesheets
    加入timesheetTask在entities.Timesheet_Task上timesheet.Id等于timesheetTask.Timesheet_Id
    参加任务entities.Tasks上timesheetTask.Task_Id等于task.Id
    参与项目entities.Projects上task.Project_Id等于project.Id
    加入entities.Departments部门对project.Department_Id等于department.Id
    其中，timesheet.Employee_Id ==雇员
    选择新
    {
       日期= timesheet.Date，
       TASKNAME = task.Name，
       项目名= project.Name，
       projectDesc = project.Description，
       DEPARTMENTNAME = department.Name，
       taskEstimatedHours = task.Estimated_Hours，
       timesheetHours = timesheetTask.Hours
    };
 

我怎样才能把这些成果转化为一个数据表，我就可以绑定到一个DataGridView控制？

这就是我正在做的：

  table.Columns.Add（日）;
    table.Columns.Add（TASKNAME）;
    table.Columns.Add（项目名称）;
    table.Columns.Add（projectDesc）;
    table.Columns.Add（DEPARTMENTNAME）;
    table.Columns.Add（taskEstimatedHours）;
    table.Columns.Add（timesheetHours）;

    的foreach（在时间表VAR项）
    {
        table.Rows.Add（item.date，item.taskName，item.projectName，
            item.projectDesc，item.departmentName，item.taskEstimatedHours，
            item.timesheetHours）;
    }
}
 

更新：这是我更新code：

 数据表表=新的DataTable（）;

使用（PTMS_DataEntities实体=新PTMS_DataEntities（））
{
    VAR时间表从时间表中entities.Timesheets =
                     加入timesheetTask在entities.Timesheet_Task上timesheet.Id等于timesheetTask.Timesheet_Id
                     参加任务entities.Tasks上timesheetTask.Task_Id等于task.Id
                     参与项目entities.Projects上task.Project_Id等于project.Id
                     加入entities.Departments部门对project.Department_Id等于department.Id
                     其中，timesheet.Employee_Id ==雇员
                     选择新
                     {
                         日期= timesheet.Date，
                         TASKNAME = task.Name，
                         项目名= project.Name，
                         projectDesc = project.Description，
                         DEPARTMENTNAME = department.Name，
                         taskEstimatedHours = task.Estimated_Hours，
                         timesheetHours = timesheetTask.Hours
                     };

    table.Columns.Add（日的typeof（DateTime的））;
    table.Columns.Add（TASKNAME的typeof（字符串））;
    table.Columns.Add（项目名中的typeof（字符串））;
    table.Columns.Add（projectDesc的typeof（字符串））;
    table.Columns.Add（DEPARTMENTNAME的typeof（字符串））;
    table.Columns.Add（taskEstimatedHours的typeof（INT））;
    table.Columns.Add（timesheetHours的typeof（INT））;

    名单＆LT; D​​ataRow的＆GT;名单=新的名单，其中，DataRow的＆GT;（）;
    的foreach（在时间表VAR项）
    {
        //table.Rows.Add(item.date，item.taskName，item.projectName，
        // item.projectDesc，item.departmentName，item.taskEstimatedHours，
        // item.timesheetHours）;

        VAR行= table.NewRow（）;

        row.SetField＆LT; D​​ateTime的＆GT;（日期，item.date）;
        row.SetField＆LT;字符串＆GT;（TASKNAME，item.taskName）;
        row.SetField＆LT;字符串＆GT;（项目名，item.projectName）;
        row.SetField＆LT;字符串＆GT;（projectDesc，item.projectDesc）;
        row.SetField＆LT;字符串＆GT;（DEPARTMENTNAME，item.departmentName）;
        row.SetField＆其中; INT＆GT;（taskEstimatedHours，item.taskEstimatedHours）;
        row.SetField＆其中; INT＆GT;（timesheetHours，item.timesheetHours）;

        list.Add（行）;
    }

    表= list.CopyToDataTable（）;
}
 

下面是SQL查询我在SSMS测试（这应该是LINQ查询的等价物）：

  SELECT dbo.Department.Name，dbo.Task.Name AS表达式1，dbo.Task.Estimated_Hours，dbo.Timesheet.Date，dbo.Project.Name AS EXPR2，dbo.Project 。描述，
                  dbo.Timesheet_Task.Date AS表达式3
从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
 

解决方案

如果你真的想填充数据表：

  //查询
VAR时间表= ...

//设计表第一
数据表表=新的DataTable（）;
table.Columns.Add（新的DataColumn
    {
        的ColumnName =TASKNAME
        数据类型= ty​​peof运算（字符串）;
    }）;
...

名单＆LT; D​​ataRow的＆GT;名单=新的名单，其中，DataRow的＆GT;（）;
的foreach（在时间表变种T）
{
    VAR行= table.NewRow（）;
    row.SetField＆LT;字符串＆GT;（TASKNAME，t.taskName）; //扩展名System.Data.DataSetExtensions.dll方法
    ...

    list.Add（行）;
}

数据表表= list.CopyToDataTable（）; //扩展方法太
 

---

或者更LINQ方式：

 时间表
    。选择（T =＆GT;
        {
            VAR行= table.NewRow（）;
            ...
            返回行;
        }）
    .CopyToDataTable（）;
 

---

或者在相同的查询语法。实现方法：

 静态的DataRow NEWROW（DataRow的排，串TASKNAME，....）
{
    ...
}
 

然后查询自己：

 （从...
 哪里 ...
 选择NEWROW（table.NewRow（），task.Name，...）
）.CopyToDataTable（）;
 

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

解决方案

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();

这篇关于如何填充的DataTable匿名LINQ结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！