How to calculate the sum of hours worked and format the dates to only show the months in mvc using mvc chart helpers

Question

This is my code which is working currently I would like to know if these two things are possible when using mvc charts? And how to go about doing them

My controller

public ActionResult CharterColumn()
{
    ArrayList xValue = new ArrayList();
    ArrayList yValue = new ArrayList();

    var results = (from c in db.Timesheets select c);

    results.ToList().ForEach(rs => xValue.Add(rs.Date));
    //I want to format this to show the only the months on the x axis

    results.ToList().ForEach(rs => yValue.Add(rs.Hours));
    //And I want to calculate the sum of the hours for each month worked by a specific employee

    new Chart(width: 800, height: 400, theme: ChartTheme.Yellow)
    .AddTitle("Test")
    .AddSeries("Default", chartType: "Column", xValue: xValue, yValues: yValue)
    .Write("bmp");

    return null;
}

and my view

<div>
   <img src= "@Url.Action("CharterColumn")" alt="Chart"/>
</div>

Answer 1

You can group the TimeSheet records by month and year like this:

DateTimeFormatInfo dtfi = new DateTimeFormatInfo();

// gets the records grouped based on Year and Month. 
// Creates an object of IEnumerable<IGrouping<>> type
var groupedByMonth = result
                        .OrderByDescending(x => x.Date)
                        .GroupBy(x => new { x.Date.Year, x.Date.Month }).ToList();


// gets the months names in a list
List<string> monthNames = groupedByMonth
                    .Select(a => dtfi.GetAbbreviatedMonthName(a.Key.Month))
                    .ToList();

// gets the total hours per month
List<int> hoursPerMonth = groupedByMonth
                        .Select(a => a.Sum(p => p.Hours))
                        .ToList();


ArrayList xValue = new ArrayList(monthNames);
ArrayList yValue = new ArrayList(hoursPerMonth);

I'm using DateTimeFormatInfo to get the month's name. You can also achieve this without this:

List<string> monthNames = groupedByMonth
                    .Select(a => a.FirstOrDefault().Date.ToString("MMMM"))
                    .ToList();