带有数据表的Linq查询

Question

我在c#中有这个数据表结果

Date    Employee Job1   Job2   Job3
1/1/2012    a    1      1      1 
1/1/2012    b    2      2      2
1/1/2012    c    2      1      4
1/1/2012    d    4      2      1
1/2/2012    a    3      2      5
1/2/2012    b    2      2      2
1/2/2012    c    3      3      3
1/2/2012    d    1      1      1
1/3/2012    a    5      5      5
1/3/2012    b    2      2      6
1/3/2012    c    1      1      1
1/3/2012    d    2      3      4
2/1/2012    a    2      2      2
2/1/2012    b    5      5      2
2/1/2012    c    2      2      2
2/2/2012    a    3      3      3
2/2/2012    b    2      3      3
3/1/2012    a    4      4      2

现在我想要这样的结果:

Job1:

Employee      January       February            March
A             9             5                   4
B             6             7
C             6             2
D             7

拜托，谁能告诉我如何在c#中使用"Linq"?

Answer 1

这可能有用(或者至少给你一个想法):

var monthEmpGroups = tblEmpJobs.AsEnumerable()
    .Select(r => new
    {
        Row = r,
        Employee = r.Field<String>("Employee"),
        Year = r.Field<DateTime>("Date").Year,
        Month = r.Field<DateTime>("Date").Month
    })
    .GroupBy(x => x.Employee);
DataTable tblMonthResultJob1 = new DataTable();
tblMonthResultJob1.Columns.Add("Employee", typeof(string));
var dtf = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat;
foreach (var empGroup in monthEmpGroups)
{
    string employee = empGroup.Key;
    var newRow = tblMonthResultJob1.Rows.Add();
    newRow["Employee"] = employee;
    var empMonthGroup = empGroup.GroupBy(mg => new { mg.Year, mg.Month });
    foreach (var empYearMonthGroup in empMonthGroup)
    {
        int year = empYearMonthGroup.Key.Year;
        int month = empYearMonthGroup.Key.Month;
        string colName = string.Format("{0} {1}", dtf.GetMonthName(month), year);
        if (!tblMonthResultJob1.Columns.Contains(colName))
            tblMonthResultJob1.Columns.Add(colName, typeof(int));
        int empJob1Count = empYearMonthGroup.Sum(x => x.Row.Field<int>("Job1"));
        newRow[colName] = empJob1Count;
    }
}
// do the same for the other job types

更新如果您需要在"end"添加"total-row"，如注释:

DataRow totalRow = tblMonthResultJob1.Rows.Add();
totalRow["Employee"] = "ALL";
var monthGroups = tblEmpJobs.AsEnumerable()
    .Select(r => new {
        Row = r,
        Year = r.Field<DateTime>("Date").Year,
        Month = r.Field<DateTime>("Date").Month
    })
    .GroupBy(x => new { x.Year, x.Month });
foreach (var monthGroup in monthGroups)
{
    int yearAll = monthGroup.Key.Year;
    int monthAll = monthGroup.Key.Month;
    string colName = string.Format("{0} {1}", dtf.GetMonthName(monthAll), yearAll);
    if (!tblMonthResultJob1.Columns.Contains(colName))
        tblMonthResultJob1.Columns.Add(colName, typeof(int));
    int allJob1Count = monthGroup.Sum(x => x.Row.Field<int>("Job1"));
    totalRow[colName] = allJob1Count;
}

更新2

我得到错误系统。这一行的Dbnull int

empJob1Count = empYearMonthGroup.Sum(x => x.Row.Field<int>("Job1"));

我想是的是一些空值，它不能转换空值，而做总结。请帮忙，我该如何解决这个

您可以使用以下代码将int?默认为零:

var empYearMonthCount = empYearMonthGroup.Sum(x => 
{ 
    int? job1 = x.Row.Field<int?>("Job1"); 
    int value = 0; 
    if(job1.HasValue) value = job1.Value; 
    return value; 
});