ASP.NET Core – Expense Manager Using EF Core and Highcharts

Introduction

In this article, we will be creating a personal expense manager using Asp.NET Core 2.1 and Entity Framework core Code first approach. An expense manager tracks your daily expenses and provides comparative charts to show expense summary. Since we are using modal dialog to handle user inputs and to show monthly and weekly expense summary chart using Highcharts. Hence this application will be a Single Page Application (SPA) .

We will be using Visual Studio 2017 and SQL Server 2017 for our demo.

Let us look at the final application.

Prerequisites

• Install .NET Core 2.1 SDK from here
• Install the latest version of Visual Studio 2017 from here
• SQL Server 2008 or above

Source Code

Before proceeding, I would recommend you to get the source code from Github.

Create ASP.NET Core project

Open Visual Studio and select File >> New >> Project.

After selecting the project, a “New Project” dialog will open. Select .NET Core inside Visual C# menu from the left panel.

Then, select “ASP.NET Core Web Application” from available project types. Put the name of the project as ExpenseManager and press OK to create the ASP.NET Core Project.

After clicking on OK, a new dialog will open asking to select the project template. You can observe two drop-down menus at the top left of the template window. Select “.NET Core” and “ASP.NET Core 2.1” from these dropdowns. Then, select “Web application (Model-View-Controller)” template and press OK.

Adding the Model to the Application

Since we are using Entity Framework Code first approach, so first we will create our model class and then generate our database tables using the model.

Right click on Models folder and select Add >> Class. Name your class ExpenseReport.cs. This class will contain our Employee model properties.

Open ExpenseReport.cs file and put in the following code into it.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;

namespace ExpenseManager.Models
{
    public class ExpenseReport
    {
        [Key]
        public int ItemId { get; set; }
        [Required]
        public string ItemName { get; set; }

        [Required]
        [DataType(DataType.Currency)]
        [Column(TypeName = "decimal(10, 2)")]
        public decimal Amount { get; set; }

        [DataType(DataType.Date)]
        [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}", ApplyFormatInEditMode = true)]
        [Required]
        public DateTime ExpenseDate { get; set; } = DateTime.Now;

        [Required]
        public string Category { get; set; }
    }
}

We have used the [Key] attribute with ItemId to consider it as the primary key while creating the database table.

Creating the database table using EF Core code first approach

In order to create our tables using EF Core code first approach we need to install few nuget packages.

Navigate to Tools >> NuGet Package Manager >> Package Manager Console.

We have to install the package for the database provider that we are targeting which is SQL Server in this case. Hence, run the following command:

Install-Package Microsoft.EntityFrameworkCore.SqlServer

Since we are using Entity Framework Tools to create a table from the existing model, we will install the tools package as well. Hence, run the following command:

Install-Package Microsoft.EntityFrameworkCore.Tools

After the package installations are successful, we will create a dbcontext class. Add a file ExpenseDBContext.cs in the Modals folder and put in the following code into it:

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace ExpenseManager.Models
{
    public class ExpenseDBContext : DbContext
    {
        public virtual DbSet<ExpenseReport> ExpenseReport { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer("Your connection string");
            }
        }
    }
}

Do not forget to put your own connection string (inside ” “).

We will create a dataset migration which is used to keep the database schema in sync with the model. There is no database at this moment, so the first migration will create it and add tables for the entities represented by the DbSet properties on the ExpenseDBContext that we have created.

To create the dataset migration, navigate to the project folder and open the powershell window. Execute the following command in it.

dotnet ef  migrations add ExpenseMigration

Refer to the image below

This will create a folder named Migrations into our project, which contains the code for the migration and a model snapshot. Refer to the image below:

Enter the following command in the powershell window to execute the migration:

dotnet ef database update

Refer to the image below:

This will create the table “ExpenseReport” in to our database that we have mentioned in the connection string. You can observe that the column ItemId is the primary key here.

Hence, the database creation is completed successfully using Entity Framework code first approach.

Adding the Data Access layer to our application

Add a class file ExpensesDataAcessLayer.cs into the Modals folder and put the following code in to it.

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace ExpenseManager.Models
{
    public class ExpensesDataAcessLayer
    {
        ExpenseDBContext db = new ExpenseDBContext();
        public IEnumerable<ExpenseReport> GetAllExpenses()
        {
            try
            {
                return db.ExpenseReport.ToList();
            }
            catch
            {
                throw;
            }
        }

        // To filter out the records based on the search string 
        public IEnumerable<ExpenseReport> GetSearchResult(string searchString)
        {
            List<ExpenseReport> exp = new List<ExpenseReport>();
            try
            {
                exp = GetAllExpenses().ToList();
                return exp.Where(x => x.ItemName.IndexOf(searchString, StringComparison.OrdinalIgnoreCase) != -1);
            }
            catch
            {
                throw;
            }
        }

        //To Add new Expense record       
        public void AddExpense(ExpenseReport expense)
        {
            try
            {
                db.ExpenseReport.Add(expense);
                db.SaveChanges();
            }
            catch
            {
                throw;
            }
        }

        //To Update the records of a particluar expense  
        public int UpdateExpense(ExpenseReport expense)
        {
            try
            {
                db.Entry(expense).State = EntityState.Modified;
                db.SaveChanges();

                return 1;
            }
            catch
            {
                throw;
            }
        }

        //Get the data for a particular expense  
        public ExpenseReport GetExpenseData(int id)
        {
            try
            {
                ExpenseReport expense = db.ExpenseReport.Find(id);
                return expense;
            }
            catch
            {
                throw;
            }
        }

        //To Delete the record of a particular expense  
        public void DeleteExpense(int id)
        {
            try
            {
                ExpenseReport emp = db.ExpenseReport.Find(id);
                db.ExpenseReport.Remove(emp);
                db.SaveChanges();

            }
            catch
            {
                throw;
            }
        }

        // To calculate last six months expense
        public Dictionary<string, decimal> CalculateMonthlyExpense()
        {
            ExpensesDataAcessLayer objexpense = new ExpensesDataAcessLayer();
            List<ExpenseReport> lstEmployee = new List<ExpenseReport>();

            Dictionary<string, decimal> dictMonthlySum = new Dictionary<string, decimal>();

            decimal foodSum = db.ExpenseReport.Where
                (cat => cat.Category == "Food" && (cat.ExpenseDate > DateTime.Now.AddMonths(-7)))
                .Select(cat => cat.Amount)
                .Sum();

            decimal shoppingSum = db.ExpenseReport.Where
               (cat => cat.Category == "Shopping" && (cat.ExpenseDate > DateTime.Now.AddMonths(-7)))
               .Select(cat => cat.Amount)
               .Sum();

            decimal travelSum = db.ExpenseReport.Where
               (cat => cat.Category == "Travel" && (cat.ExpenseDate > DateTime.Now.AddMonths(-7)))
               .Select(cat => cat.Amount)
               .Sum();

            decimal healthSum = db.ExpenseReport.Where
               (cat => cat.Category == "Health" && (cat.ExpenseDate > DateTime.Now.AddMonths(-7)))
               .Select(cat => cat.Amount)
               .Sum();

            dictMonthlySum.Add("Food", foodSum);
            dictMonthlySum.Add("Shopping", shoppingSum);
            dictMonthlySum.Add("Travel", travelSum);
            dictMonthlySum.Add("Health", healthSum);

            return dictMonthlySum;
        }

        // To calculate last four weeks expense
        public Dictionary<string, decimal> CalculateWeeklyExpense()
        {
            ExpensesDataAcessLayer objexpense = new ExpensesDataAcessLayer();
            List<ExpenseReport> lstEmployee = new List<ExpenseReport>();

            Dictionary<string, decimal> dictWeeklySum = new Dictionary<string, decimal>();

            decimal foodSum = db.ExpenseReport.Where
                (cat => cat.Category == "Food" && (cat.ExpenseDate > DateTime.Now.AddDays(-7)))
                .Select(cat => cat.Amount)
                .Sum();

            decimal shoppingSum = db.ExpenseReport.Where
               (cat => cat.Category == "Shopping" && (cat.ExpenseDate > DateTime.Now.AddDays(-28)))
               .Select(cat => cat.Amount)
               .Sum();

            decimal travelSum = db.ExpenseReport.Where
               (cat => cat.Category == "Travel" && (cat.ExpenseDate > DateTime.Now.AddDays(-28)))
               .Select(cat => cat.Amount)
               .Sum();

            decimal healthSum = db.ExpenseReport.Where
               (cat => cat.Category == "Health" && (cat.ExpenseDate > DateTime.Now.AddDays(-28)))
               .Select(cat => cat.Amount)
               .Sum();

            dictWeeklySum.Add("Food", foodSum);
            dictWeeklySum.Add("Shopping", shoppingSum);
            dictWeeklySum.Add("Travel", travelSum);
            dictWeeklySum.Add("Health", healthSum);

            return dictWeeklySum;
        }
    }
}

This file will have methods to handle CRUD operations on our database. We are also calculating the last six months expense and last four weeks expense for each category.

Adding the Controller to the Application

Right click on Controllers folder and select Add >> New Item. An “Add New Item” dialog box will open. Select “ASP.NET Core” from the left panel, then select “Controller class” from templates panel, and put the name as ExpenseController.cs. Press OK.

This will create our controller ExpenseController inside Controllers folder. Open the ExpenseController.cs file and put in the following code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using ExpenseManager.Models;
using Microsoft.AspNetCore.Mvc;

namespace ExpenseManager.Controllers
{
    public class ExpenseController : Controller
    {
        ExpensesDataAcessLayer objexpense = new ExpensesDataAcessLayer();
        public IActionResult Index(string searchString)
        {
            List<ExpenseReport> lstEmployee = new List<ExpenseReport>();
            lstEmployee = objexpense.GetAllExpenses().ToList();

            if (!String.IsNullOrEmpty(searchString))
            {
                lstEmployee = objexpense.GetSearchResult(searchString).ToList();
            }
            return View(lstEmployee);
        }

        public ActionResult AddEditExpenses(int itemId)
        {
            ExpenseReport model = new ExpenseReport();
            if (itemId > 0)
            {
                model = objexpense.GetExpenseData(itemId);
            }
            return PartialView("_expenseForm", model);
        }

        [HttpPost]
        public ActionResult Create(ExpenseReport newExpense)
        {
            if (ModelState.IsValid)
            {
                if (newExpense.ItemId > 0)
                {
                    objexpense.UpdateExpense(newExpense);
                }
                else
                {
                    objexpense.AddExpense(newExpense);
                }
            }
            return RedirectToAction("Index");
        }

        [HttpPost]
        public IActionResult Delete(int id)
        {
            objexpense.DeleteExpense(id);
            return RedirectToAction("Index");
        }

        public ActionResult ExpenseSummary()
        {
            return PartialView("_expenseReport");
        }

        public JsonResult GetMonthlyExpense()
        {
            Dictionary<string, decimal> monthlyExpense = objexpense.CalculateMonthlyExpense();
            return new JsonResult(monthlyExpense);
        }

        public JsonResult GetWeeklyExpense()
        {
            Dictionary<string, decimal> weeklyExpense = objexpense.CalculateWeeklyExpense();
            return new JsonResult(weeklyExpense);
        }
    }
}

The controller will have the methods that will call our data access layer methods to handle database operations.

Adding Views to the Application

We will create three view files

1. Index.cshtml: – this view will display all the expense data and contains a search box to search for a particular item.
2. _expenseForm.cshtml: – this is a partial view, which contains the form to handle user inputs. This is used for both add and edit functionality and will be rendered in a modal dialog.
3. _expenseReport.cshtml: – this is also a partial view, which will show the expense summary in a bar chart using Highcharts. It is also rendered as a modal dialog.

Index View

To create the view file, right click on the Index method in our controller and select “Add View…”. This will open an “Add MVC View” dialog box. Put in the name of view as Index and click Add. Make sure that “Create as a partial view” check box is not checked. Refer to image below:

This will create the Index.cshtml file inside “Expense” folder under the Views folder. Open Index.cshtml file and put in the following code:

@model IEnumerable<ExpenseManager.Models.ExpenseReport>

@{
    ViewData["Title"] = "Personal Expense Manager";
}
<link href="~/lib/bootstrap/dist/css/bootstrap.css" rel="stylesheet" />
<script src="~/lib/jquery/dist/jquery.min.js"></script>
<script src="~/lib/bootstrap/dist/js/bootstrap.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.8.0/js/bootstrap-datepicker.js"></script>
<link href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.8.0/css/bootstrap-datepicker.css" rel="stylesheet">

<h2>Personal Expense Manager</h2>
<br />
<div>
    <div style="float:left">
        <button class="btn btn-primary" >
Let us understand this code. At the top we have included the bootstrap and jQuery references. After that we have added two buttons for adding a new expense and for creating the expense summary. We have also included a form containing a search box to filter out the records. On clicking of “Filter” button, the form is submitted and it invoked the Index method in our controller which will return the items matching the search criteria. The search functionality is provided only on item name field.
 
We are using a table to display all the expense records in our database and each record has two action button corresponding to it – Edit and Delete.
 
We have also created two modal dialog, one for adding/editing the expense data and another to display the expense summary report.
 
In the script section, we have defined AddEditExpenses function. This function will be invoked when “Add Expense” or “Edit” button is clicked. We are passing the itemId as parameter in this method. If the “ItemId” value is not set then it is considered as Add function and if the “ItemId” is set then it is an Edit function. We will call “AddEditExpenses” in our controller which will return the partial view “_expenseForm” and bind it to ExpenseReport model. The modal will be empty for “Add” call and contain the expense item data in case of “Edit” call. Since we are using bootstrap datepicker to select the expense date, hence we have set the datepicker properties on modal dialog load.
 
The ReportExpense function will call ExpenseSummary method in our controller, which will return the partial view “_expenseReport” to be displayed as a modal dialog. This partial view will display the monthly and weekly expense summary chart using Highcharts.
 
The DeleteExpense function is used to delete the record of a particular expense. This will invoke the “Delete” method in our controller to remove the expense record from our database.
 
We are also using dynamic binding to bind the submit event of “expenseForm” modal. This form is defined in “_expenseForm.cshtml” view. On submitting of form, we are invoking an ajax call to the “Create” method in our controller class. Since we are using same form for both Edit and Add functionality so we need to distinguish among both using the ItemId value. In the “Create” method of controller, we will check if the ItemId is set then we will invoke UpdateExpense method, else invoke AddExpense method. After successful submit, we will close the modal and redirect to Index view to show the updated list of expenses.
 
ExpenseForm View
 
This is a partial view that will be displayed in a modal dialog on clicking of “Add Expense” button in Index view.
 
To create the view file, right click anywhere inside our controller file and select “Add View…”. This will open an “Add MVC View” dialog box. Put in the name of view as ““_expenseForm” and click Add. Make sure that “Create as a partial view” check box is selected. Refer to image below:
 
 
Open the “_expenseForm.cshtml file” and put in the following code in it
 
@model ExpenseManager.Models.ExpenseReport

<script src="//cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.3.0/js/bootstrap-datepicker.js"></script>
<link href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.8.0/css/bootstrap-datepicker.css" rel="stylesheet">

<div>
    <div class="row">
        <div class="col-md-8">
            <form id="expenseForm">
                <input type="hidden" asp-for="ItemId" />
                <div class="form-group">
                    <label asp-for="ItemName" class="control-label"></label>
                    <input asp-for="ItemName" class="form-control" />
                </div>
                <div class="form-group">
                    <label asp-for="Category" class="control-label"></label>
                    <select asp-for="Category" class="form-control">
                        <option value="">-- Select Category --</option>
                        <option value="Food">Food</option>
                        <option value="Shopping">Shopping</option>
                        <option value="Travel">Travel</option>
                        <option value="Health">Health</option>
                    </select>
                </div>
                <div class="form-group">
                    <label asp-for="Amount" class="control-label"></label>
                    <input asp-for="Amount" class="form-control" />
                </div>
                <div class="form-group" id="calender-container">
                    <label asp-for="ExpenseDate" class="control-label"></label>
                    <div class="input-group date">
                        <input asp-for="ExpenseDate" type="text" class="form-control"><span class="input-group-addon"><i class="glyphicon glyphicon-calendar"></i></span>
                    </div>
                </div>
                <div class="form-group">
                    <button type="button" id="btnSubmit" class="btn btn-block btn-info">Save</button>
                </div>
            </form>
        </div>
    </div>
</div>
At the top, we are including the cdn reference to the bootstrap-datepicker so that we can use it in our modal dialog. Then we have a form element, which binds to our modal. We also have a submit button which will post the form data to the Create method in our controller using ajax call.
ExpenseReport View
This is a partial view that is displayed in the modal dialog on clicking “Expense Report” button in Index view. This view will display the expense report summary as bar graph with the help of Highcharts.
Create a new partial view ““_expenseReport.cshtml” and put in the following code in it.
<script src="https://code.highcharts.com/highcharts.js"></script>

<button id="btnMonthlyReport" class="btn btn-info">Monthly Report</button>
<button id="btnWeeklyReport" class="btn btn-warning">Weekly Report</button>
<div id="container" style="min-width: 400px; height: 400px; margin: 0 auto">

</div>

<script>

    $(document).ready(function () {
        $("#btnWeeklyReport").click(function () {
            var titleMessage = "Expenses in last four weeks is : ";

            $.ajax({
                type: "GET",
                url: "/Expense/GetWeeklyExpense",
                contentType: "application/json",
                dataType: "json",
                success: function (result) {
                    var keys = Object.keys(result);
                    var weeklydata = new Array();
                    var totalspent = 0.0;
                    for (var i = 0; i < keys.length; i++) {
                        var arrL = new Array();
                        arrL.push(keys[i]);
                        arrL.push(result[keys[i]]);
                        totalspent += result[keys[i]];
                        weeklydata.push(arrL);
                    }
                    createCharts(weeklydata, titleMessage, totalspent.toFixed(2));
                }
            })
        })

        $("#btnMonthlyReport").click(function () {
            var titleMessage = "Expenses in last six months is : ";

            $.ajax({
                type: "GET",
                url: "/Expense/GetMonthlyExpense",
                contentType: "application/json",
                dataType: "json",
                success: function (result) {
                    var keys = Object.keys(result);
                    var monthlydata = new Array();
                    var totalspent = 0.0;
                    for (var i = 0; i < keys.length; i++) {
                        var arrL = new Array();
                        arrL.push(keys[i]);
                        arrL.push(result[keys[i]]);
                        totalspent += result[keys[i]];
                        monthlydata.push(arrL);
                    }
                    createCharts(monthlydata, titleMessage, totalspent.toFixed(2));
                }
            })
        })
    })

    function createCharts(sum, titleText, totalspent) {
        Highcharts.chart('container', {
            chart: {
                type: 'column'
            },
            title: {
                text: titleText + ' ' + totalspent
            },
            xAxis: {
                type: 'category',
                labels: {
                    rotation: -45,
                    style: {
                        fontSize: '13px',
                        fontFamily: 'Verdana, sans-serif'
                    }
                }
            },
            yAxis: {
                min: 0,
                title: {
                    text: 'Money spent'
                }
            },
            legend: {
                enabled: false
            },
            tooltip: {
                pointFormat: 'Total money spent: <b>{point.y:.2f} </b>'
            },
            series: [{
                type: 'column',
                data: sum,
            }]
        });
    }

</script>
On the top we have included the cdn reference to Highcharts. We have also provided two buttons to view monthly report of last six months and weekly report for last four weeks. The report will be generated as a bar chart to provide a comparative study of expense summary.
On click weekly report button, we will invoke the GetWeeklyExpense method of our controller that will return the data in Json format and we will pass this to createCharts function to create the weekly expense bar chart using Highcharts.
Similarly, we will invoke GetMonthlyExpense method of our controller on clicking of “Monthly Report” button and pass the Json result to createCharts function to create the monthly expense bar chart using Highcharts.
Configure route URL
Open Startup.cs file to set the format for routing.Scroll down to app.UseMvc method, where you can set the route url.
Make sure that your route url is set like this
app.UseMvc(routes =>
{
    routes.MapRoute(
        name: "default",
        template: "{controller=Expense}/{action=Index}");
});
This url pattern sets ExpenseController as default controller and Index method as default action method. Default route parameters need not be present in the URL path for a match. If we do not append any controller name in the URL then it will take ExpenseController as default controller and Index method of ExpenseController as default action method. Similarly, if we append only “/Expense” to the URL, it will navigate to Index action method of Expense controller.
Execution Demo
Press F5 to launch the application. You can see a page similar to the one shown below.
Here we have “Add Expense” button to add a new expense report. The “Expense Report” button will open a dialog box to show the bar chart of monthly and weekly expense data. This bar chart is created with the hep of Highcharts. On the top right corner, we have a search box to search the records using item name.
Look at the below Gif image for the demo of application
Conclusion
We created a personal expense manager application using ASP.NET Core and Entity Framework Core with the help of Visual Studio 2017 and SQL Server 2017. We have also used Highcharts to create a bar chart for monthly and weekly expense summary.
Please download the source code from GitHub and play around to get a better understanding.
You can also read this article at C# Corner
You can read my other articles on ASP .NET Core here
See Also
 
CRUD Operation With ASP.NET Core MVC Using Visual Studio Code and EF
 
ASP.NET Core – CRUD With React.js And Entity Framework Core
 
ASP.NET Core – CRUD Using Angular 5 And Entity Framework Core
 
CRUD Operation With ASP.NET Core MVC Using ADO.NET and Visual Studio 2017
 
CRUD Operation With ASP.NET Core MVC using Visual Studio Code and ADO.NET
 
ASP.NET Core – Using Highcharts With Angular 5