Linq query with datatable

Question

I have this datatable result in 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

Now I want a result like this:

Job1:

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

Please, can anybody suggest me how to do this with "Linq" in c#?

Answer 1

This might work (or give you at least an idea):

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

Update If you need to add a "total-row" at the "end" as commented:

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;
}

Update2

I am getting error system.dbnull on this line int

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

I think there are some null values and it is not able to cast null values while doing sum. Please help, how should i resolve this

You can use this code to default an int? to zero:

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