How to create a charts in Razor Pages pulling data from the database?

Question

How do I create charts pulling from the database? I've tried MorrisJS, ChartJS, CanvasJS, HighCharts. But I think the closest I got is with ChartJS so hopefully we can stick to that.

I need to create a chart that pulls data from the db and using more than 1 table in the database, for example, Database name "Finance", I need a bar chart that compares sum of Amount in dbo.Revenue and Sum of Amount in dbo.Expense by month. Month is the x-axis. Something like this , but I did this manually by adding the values in the code.

What I have tried to do to pull from the db, ar is the table name (revenue)

public async Task<JsonResult> OnGetRevenueData()
        {
            ar21 =  await _context.ar21.ToListAsync();
            var arChart = new arChartModel();
            arChart.AmountList = new List<decimal?>();
            arChart.CategoryList = new List<string>();

            foreach (var r in ar21)
            {
                arChart.AmountList.Add(r.Amount);
                arChart.MonthList.Add(r.Month);
            }

            return new JsonResult(arChart);
        }

Answer 1

Here is a working demo you could follow:

Model:

public class Revenue
{
    public int Id { get; set; }
    public decimal Amount { get; set; }
    public string Month { get; set; }
}
public class Expense
{
    public int Id { get; set; }
    public decimal Amount { get; set; }
    public string Month { get; set; }
}

Page:

@page
@model IndexModel

<canvas id="Chart1" width="500" height="300"></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>
        $(document).ready(function () {
            $.ajax({
                type: "Get",
                url: "/?handler=RevenueData",   
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (response) {
                    console.log(response.revenue);
                    console.log(response.revenue.length);
                    var data = response.revenue;
                    var data2 = response.expense;
                    var labels = response.month;
                    var color1 = [];
                    var color2 = [];
                    for (var i = 0; i < response.month.length; i++)
                    {
                        color1.push("rgba(255, 99, 132, 0.2)");
                        color2.push("rgba(54, 162, 235, 0.2)");
                    }
  
                    var ctx = document.getElementById("Chart1");
                    var myChart = new Chart(ctx, {
                        type: 'bar',
                        data: {
                            labels: labels,
                            datasets: [{
                                label: 'Revenue',
                                data: data,
                                backgroundColor: color1,
                            },
                            {
                                label: 'Expense',
                                data: data2,
                                backgroundColor: color2
                            }]
                        },
                        options: {
                            responsive: false,
                            scales: {
                                xAxes: [{
                                    ticks: {
                                        maxRotation: 90,
                                        minRotation: 80
                                    },
                                    gridLines: {
                                        offsetGridLines: true 
                                    }
                                },
                                {
                                    position: "top",
                                    ticks: {
                                        maxRotation: 90,
                                        minRotation: 80
                                    },
                                    gridLines: {
                                        offsetGridLines: true 
                                    }
                                }],
                                yAxes: [{
                                    ticks: {
                                        beginAtZero: true
                                    }
                                }]
                            }
                        }
                    });
                  
                },
                error: function (response) {
                    alert(response.responseText);
                }
            });
        });
    </script>
}

Handler:

public class IndexModel : PageModel
{
    private readonly YourDbContext _context;

    public IndexModel(YourDbContext context)
    {
        _context = context;
    }
    public void OnGet()
    {

    }
    public JsonResult OnGetRevenueData()
    {
        var countRevenue = (from t in _context.Revenue
                           group t by new
                           {
                               Month = t.Month
                           } into g
                           select  g.Sum(a=>a.Amount)
                           ).ToArray();
        var countExpense = (from t in _context.Expense
                            group t by new
                            {
                                Month = t.Month
                            } into g
                            select g.Sum(a => a.Amount)
                           ).ToArray();
        var monthList = _context.Revenue.Select(a => a.Month).Distinct().ToArray();
        return new JsonResult(new { revenue = countRevenue, expense = countExpense, month = monthList });
    }
}