How to create chartjs chart with data from database C#

I am trying to create a chart.js graph in my .Net Core Web app, with data from the database. I am using ajax to call a method that will pull the data from the database, but I’m unsure how to group the data to display in the graph.

At the moment I have a database that looks like so:

I am looking to show the time along the bottom and count how many jobs success and how m any jobs exception. At the moment my graph is hard coded.

// Area Chart Example
var ctx = document.getElementById("canvas")
var lineChartData = {
labels: ['January', 'February', 'March', 'April', 'May', 'June', 'July'],
datasets: [{
    label: 'Failed',
    borderColor: "MediumSeaGreen",
    backgroundColor: "MediumSeaGreen",
    fill: false,
    data: [
        30000, 30162, 26263, 18394, 18287, 28682, 31274, 33259, 25849, 
 24159, 32651, 31984, 38451
    ],
    yAxisID: 'y-axis-1',
}, {
    label: 'Exceptioned',
    borderColor: "Tomato",
    backgroundColor: "Tomato",
    fill: false,
    data: [
        20000, 30162, 26263, 33259, 18287, 28682, 25849, 18394, 25849, 
 24159, 32651, 31984, 38451
    ],
    yAxisID: 'y-axis-2'
}]
};

window.myLine = Chart.Line(ctx, {
    data: lineChartData,
    options: {
        responsive: true,
        hoverMode: 'index',
        stacked: false,
        title: {
            display: true,
            text: 'Processes'
        },
        scales: {
            yAxes: [{
                type: 'linear', 
                display: true,
                position: 'left',
                id: 'y-axis-1',
            }, {
                type: 'linear',
                display: true,
                position: 'right',
                id: 'y-axis-2',

                // grid line settings
                gridLines: {
                    drawOnChartArea: false, // only want the grid lines for one axis to show up
                },
            }],
        }
    }
});

Answer

Here is a simple demo like below:

1.Model:

public class Job
{
    public int JobId { get; set; }
    public string JobName { get; set; }
    public string JobStatus { get; set; }
    public DateTime JobCompletion { get; set; }
}

2.View:

<canvas id="canvas" width="400" height="400"></canvas>
@section Scripts{
    <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.8.0/Chart.bundle.js" integrity="sha256-qSIshlknROr4J8GMHRlW3fGKrPki733tLq+qeMCR05Q=" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.8.0/Chart.bundle.min.js" integrity="sha256-xKeoJ50pzbUGkpQxDYHD7o7hxe0LaOGeguUidbq6vis=" crossorigin="anonymous"></script>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.8.0/Chart.css" integrity="sha256-IvM9nJf/b5l2RoebiFno92E5ONttVyaEEsdemDC6iQA=" crossorigin="anonymous" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.8.0/Chart.js" integrity="sha256-arMsf+3JJK2LoTGqxfnuJPFTU4hAK57MtIPdFpiHXOU=" crossorigin="anonymous"></script>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.8.0/Chart.min.css" integrity="sha256-aa0xaJgmK/X74WM224KMQeNQC2xYKwlAt08oZqjeF0E=" crossorigin="anonymous" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.8.0/Chart.min.js" integrity="sha256-Uv9BNBucvCPipKQ2NS9wYpJmi8DTOEfTA/nH2aoJALw=" crossorigin="anonymous"></script>
    <script>
        function GetJSON_Simple() {
          var resp = [];
              $.ajax({
                  type: "GET",
                  url: '/Jobs/Index',
                  async: false,
                  contentType: "application/json",
                  success: function (data) {
                      resp.push(data);
                  },
                  error: function (req, status, error) {
                      // do something with error
                      alert("error");
                  }
              });
          return resp;
     }
        var simpleData = GetJSON_Simple();
        var ctx = document.getElementById("canvas")
        var lineChartData = {
            labels: simpleData[0].myDate, 
            datasets: [{
                label: 'Sucess',
                borderColor: "MediumSeaGreen",
                backgroundColor: "MediumSeaGreen",
                fill: false,
                data:   simpleData[0].mySuccess,
                yAxisID: 'y-axis-1',
            }, {
                label: 'Exceptioned',
                borderColor: "Tomato",
                backgroundColor: "Tomato",
                fill: false,
                data: simpleData[0].myException,
                yAxisID: 'y-axis-2'
            }]
        };

        window.myLine = Chart.Line(ctx, {
            data: lineChartData,
            options: {
                responsive: true,
                hoverMode: 'index',
                stacked: false,
                title: {
                    display: true,
                    text: 'Processes'
                },
                scales: {
                    yAxes: [{
                        type: 'linear',
                        display: true,
                        position: 'left',
                        id: 'y-axis-1',
                    }, {
                        type: 'linear',
                        display: true,
                        position: 'right',
                        id: 'y-axis-2',

                        // grid line settings
                        gridLines: {
                            drawOnChartArea: false, // only want the grid lines for one axis to show up
                        },
                    }],
                }
            }
        });

    </script>
}

3.Controller:

public class JobsController : Controller
{
    private readonly YourContext _context;

    public JobsController(YourContext context)
    {
        _context = context;
    }

    // GET: Jobs
    public async Task<ActionResult> Index()
    {
        var date = await _context.Job.Select(j => j.JobCompletion).Distinct().ToListAsync();
        var success =_context.Job
            .Where(j => j.JobStatus == "Success")
            .GroupBy(j => j.JobCompletion)
            .Select(group=>new {
                JobCompletion = group.Key,
                Count=group.Count()
            });
        var countSuccess = success.Select(a => a.Count).ToArray();
        var exception = _context.Job
            .Where(j => j.JobStatus == "Exception")
            .GroupBy(j => j.JobCompletion)
            .Select(group => new {
                JobCompletion = group.Key,
                Count = group.Count()
            });
        var countException = exception.Select(a => a.Count).ToArray();
        return new JsonResult(new { myDate=date,mySuccess= countSuccess, myException= countException });
    }
}

4.Database:

5.Result: