ASP.NET CORE MVC TUTORIAL SERIES · PART 15

Building a Dashboard with Aggregate Data

Use Entity Framework Core and LINQ aggregate queries to calculate totals, averages and category summaries, then present those results through a dedicated Dashboard ViewModel and Razor view.

Objective

By the end of this tutorial, authenticated users will have a Dashboard showing total Products, total Categories, total quantity, total inventory value, average Product price and per-Category summaries.

Starting point

Part 14 reorganised the shared UI with layouts and partial views. Part 15 adds reporting-style queries without changing the database schema.

In this tutorial

Understand aggregate queries
Create DashboardViewModel
Create DashboardController
Use CountAsync()
Use SumAsync()
Use AverageAsync()
Use GroupBy()
Create the Dashboard view
Add Dashboard navigation
Test and verify the calculations
Review full final code in the appendix

1. Open and Verify the Part 14 Project

cd ~/aspnet-mvc-tutorial/ProductManagement
pwd
dotnet build
code .

The expected path is:

/home/xubuntu/aspnet-mvc-tutorial/ProductManagement

Checkpoint

Continue only when Part 14 builds successfully.

2. What Is Aggregate Data?

Aggregate queries summarize many database rows into useful values.

Operation	Example
`Count`	How many Products exist?
`Sum`	What is the total quantity?
`Average`	What is the average Product price?
`GroupBy`	How many Products are in each Category?

Products table ↓ Aggregate query ↓ Summary values ↓ Dashboard

3. Create DashboardViewModel.cs

Create:

touch ViewModels/DashboardViewModel.cs
code ViewModels/DashboardViewModel.cs

Add:

namespace ProductManagement.ViewModels;

public class DashboardViewModel
{
    public int TotalProducts { get; set; }

    public int TotalCategories { get; set; }

    public int TotalQuantity { get; set; }

    public decimal InventoryValue { get; set; }

    public decimal AveragePrice { get; set; }

    public List<CategorySummaryViewModel> CategorySummaries { get; set; }
        = new();
}

public class CategorySummaryViewModel
{
    public string CategoryName { get; set; } = string.Empty;

    public int ProductCount { get; set; }

    public int TotalQuantity { get; set; }

    public decimal InventoryValue { get; set; }
}

4. Understand the Dashboard ViewModel

The Dashboard view needs several values that do not belong to one database row. The ViewModel combines them into one strongly typed object.

Products Categories Aggregate results ↓ DashboardViewModel ↓ Dashboard View

5. Create DashboardController.cs

Create:

touch Controllers/DashboardController.cs
code Controllers/DashboardController.cs

Add the complete controller:

using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using ProductManagement.Data;
using ProductManagement.ViewModels;

namespace ProductManagement.Controllers;

[Authorize]
public class DashboardController : Controller
{
    private readonly ApplicationDbContext _context;

    public DashboardController(
        ApplicationDbContext context)
    {
        _context = context;
    }

    public async Task<IActionResult> Index()
    {
        var totalProducts =
            await _context.Products.CountAsync();

        var totalCategories =
            await _context.Categories.CountAsync();

        var totalQuantity =
            await _context.Products
                .SumAsync(p => (int?)p.Quantity) ?? 0;

        var inventoryValue =
            await _context.Products
                .SumAsync(
                    p => (decimal?)(p.Price * p.Quantity))
            ?? 0m;

        var averagePrice =
            await _context.Products
                .AverageAsync(p => (decimal?)p.Price)
            ?? 0m;

        var categorySummaries =
            await _context.Products
                .GroupBy(
                    p => p.Category != null
                        ? p.Category.Name
                        : "Unassigned")
                .Select(g => new CategorySummaryViewModel
                {
                    CategoryName = g.Key,
                    ProductCount = g.Count(),
                    TotalQuantity = g.Sum(
                        p => p.Quantity),
                    InventoryValue = g.Sum(
                        p => p.Price * p.Quantity)
                })
                .OrderBy(x => x.CategoryName)
                .ToListAsync();

        var viewModel = new DashboardViewModel
        {
            TotalProducts = totalProducts,
            TotalCategories = totalCategories,
            TotalQuantity = totalQuantity,
            InventoryValue = inventoryValue,
            AveragePrice = averagePrice,
            CategorySummaries = categorySummaries
        };

        return View(viewModel);
    }
}

6. Understand CountAsync()

Inside DashboardController.cs, find:

var totalProducts =
    await _context.Products.CountAsync();

var totalCategories =
    await _context.Categories.CountAsync();

These queries count rows in the Products and Categories sets.

7. Understand SumAsync()

Find:

var totalQuantity =
    await _context.Products
        .SumAsync(p => (int?)p.Quantity) ?? 0;

The nullable cast allows an empty Products table to produce a null aggregate result that can safely fall back to zero.

8. Calculate Inventory Value

Find:

var inventoryValue =
    await _context.Products
        .SumAsync(
            p => (decimal?)(p.Price * p.Quantity))
    ?? 0m;

For each Product:

Price × Quantity ↓ Product inventory value ↓ Sum all Products ↓ Total inventory value

9. Calculate Average Price

Find:

var averagePrice =
    await _context.Products
        .AverageAsync(p => (decimal?)p.Price)
    ?? 0m;

The nullable projection again lets an empty table return a safe zero value rather than failing because there are no rows to average.

10. Group Products by Category

Find the categorySummaries query:

var categorySummaries =
    await _context.Products
        .GroupBy(
            p => p.Category != null
                ? p.Category.Name
                : "Unassigned")
        .Select(g => new CategorySummaryViewModel
        {
            CategoryName = g.Key,
            ProductCount = g.Count(),
            TotalQuantity = g.Sum(
                p => p.Quantity),
            InventoryValue = g.Sum(
                p => p.Price * p.Quantity)
        })
        .OrderBy(x => x.CategoryName)
        .ToListAsync();

This groups Product rows by Category name, then calculates a count, total quantity and inventory value for each group.

11. Why Use a Separate CategorySummaryViewModel?

Each grouped result contains reporting data rather than a database entity.

CategoryName
ProductCount
TotalQuantity
InventoryValue

That makes CategorySummaryViewModel appropriate for presenting grouped results.

12. Protect the Dashboard

At the top of DashboardController, the controller uses:

[Authorize]

This means the dashboard is available to authenticated users only. The public Product catalogue can remain accessible while application summary information requires login.

13. Create the Dashboard View Folder

mkdir -p Views/Dashboard

14. Create Views/Dashboard/Index.cshtml

touch Views/Dashboard/Index.cshtml
code Views/Dashboard/Index.cshtml

Add:

@model ProductManagement.ViewModels.DashboardViewModel

@{
    ViewData["Title"] = "Dashboard";
}

<h1>Dashboard</h1>

<div class="row g-3 mb-4">

    <div class="col-md-4">
        <div class="card h-100">
            <div class="card-body">
                <h5 class="card-title">Total Products</h5>
                <p class="display-6">@Model.TotalProducts</p>
            </div>
        </div>
    </div>

    <div class="col-md-4">
        <div class="card h-100">
            <div class="card-body">
                <h5 class="card-title">Total Categories</h5>
                <p class="display-6">@Model.TotalCategories</p>
            </div>
        </div>
    </div>

    <div class="col-md-4">
        <div class="card h-100">
            <div class="card-body">
                <h5 class="card-title">Total Quantity</h5>
                <p class="display-6">@Model.TotalQuantity</p>
            </div>
        </div>
    </div>

    <div class="col-md-6">
        <div class="card h-100">
            <div class="card-body">
                <h5 class="card-title">Inventory Value</h5>
                <p class="display-6">
                    RM @Model.InventoryValue.ToString("N2")
                </p>
            </div>
        </div>
    </div>

    <div class="col-md-6">
        <div class="card h-100">
            <div class="card-body">
                <h5 class="card-title">Average Product Price</h5>
                <p class="display-6">
                    RM @Model.AveragePrice.ToString("N2")
                </p>
            </div>
        </div>
    </div>

</div>

<h2>Category Summary</h2>

@if (!Model.CategorySummaries.Any())
{
    <p>No product data is available yet.</p>
}
else
{
    <table class="table table-striped">
        <thead>
            <tr>
                <th>Category</th>
                <th>Products</th>
                <th>Total Quantity</th>
                <th>Inventory Value</th>
            </tr>
        </thead>
        <tbody>
        @foreach (var item in Model.CategorySummaries)
        {
            <tr>
                <td>@item.CategoryName</td>
                <td>@item.ProductCount</td>
                <td>@item.TotalQuantity</td>
                <td>RM @item.InventoryValue.ToString("N2")</td>
            </tr>
        }
        </tbody>
    </table>
}

15. Understand the Summary Cards

The view displays summary values such as:

@Model.TotalProducts
@Model.TotalCategories
@Model.TotalQuantity
@Model.InventoryValue
@Model.AveragePrice

The controller performs the database work. The view focuses only on presentation.

16. Add Dashboard to the Shared Navigation

Open:

code Views/Shared/_Layout.cshtml

Find the Products navigation item. Immediately after it, add:

<li class="nav-item">
    <a class="nav-link text-dark"
       asp-area=""
       asp-controller="Dashboard"
       asp-action="Index">
        Dashboard
    </a>
</li>

If you want the Dashboard link visible only to authenticated users, wrap it with:

@if (User.Identity?.IsAuthenticated == true)
{
    <li class="nav-item">
        <a class="nav-link text-dark"
           asp-area=""
           asp-controller="Dashboard"
           asp-action="Index">
            Dashboard
        </a>
    </li>
}

Recommended version

Use the conditional version so anonymous visitors do not see a link that immediately redirects them to Login.

17. Build the Project

Save:

ViewModels/DashboardViewModel.cs
Controllers/DashboardController.cs
Views/Dashboard/Index.cshtml
Views/Shared/_Layout.cshtml

Then run:

dotnet build

Checkpoint

Continue only when the project builds successfully.

18. Run and Test the Dashboard

dotnet run

/Dashboard

You should see summary values generated from your current SQLite data.

19. Verify Total Products in SQLite

Stop the application if necessary and run:

sqlite3 ProductManagement.db

Then:

SELECT COUNT(*) FROM Products;

Compare the result with Total Products on the Dashboard.

20. Verify Total Quantity

SELECT SUM(Quantity) FROM Products;

Compare it with the Dashboard's total quantity.

21. Verify Inventory Value

SELECT SUM(Price * Quantity) FROM Products;

Compare the result with the Dashboard inventory value.

22. Verify Average Price

SELECT AVG(Price) FROM Products;

Compare the result with Average Product Price.

23. Verify Category Summaries

Run:

SELECT
    COALESCE(Categories.Name, 'Unassigned') AS Category,
    COUNT(Products.Id) AS ProductCount,
    SUM(Products.Quantity) AS TotalQuantity,
    SUM(Products.Price * Products.Quantity) AS InventoryValue
FROM Products
LEFT JOIN Categories
    ON Products.CategoryId = Categories.Id
GROUP BY COALESCE(Categories.Name, 'Unassigned')
ORDER BY Category;

Compare the result with the Category Summary table.

24. No Migration Is Required

Part 15 adds a ViewModel, controller and view only. It does not alter Product, Category or ApplicationDbContext.

Do not create a migration

No database schema change occurs in this part.

25. Troubleshooting

CountAsync, SumAsync or AverageAsync cannot be found

Open DashboardController.cs and confirm:

using Microsoft.EntityFrameworkCore;

Average throws an error when there are no Products

Use the nullable projection shown in this tutorial:

await _context.Products.AverageAsync(p => (decimal?)p.Price) ?? 0m;

Dashboard returns 404

Confirm Controllers/DashboardController.cs and Views/Dashboard/Index.cshtml both exist.

Dashboard redirects to Login

This is expected when the visitor is anonymous because the controller has [Authorize].

Category summary does not match SQLite

Check whether some Products have CategoryId = NULL. These are intentionally grouped under Unassigned.

26. Hands-On Exercise

Record the current Dashboard totals.
Create a new Product with quantity 10 and price RM 50.00.
Return to Dashboard.
Confirm Total Products increases by 1.
Confirm Total Quantity increases by 10.
Confirm Inventory Value increases by RM 500.00.
Confirm the correct Category group changes.

27. Knowledge Check

What is an aggregate query?
What does CountAsync() return?
What does SumAsync() calculate?
Why use nullable projections for empty datasets?
How is inventory value calculated?
What does GroupBy() do?
Why use DashboardViewModel?
Why does Part 15 not require a migration?

28. Part 15 Summary

created DashboardViewModel;
created DashboardController;
used CountAsync();
used SumAsync();
used AverageAsync();
used GroupBy();
calculated inventory value;
built Category summaries;
created an authenticated Dashboard view; and
verified Dashboard values directly in SQLite.

Appendix — Full Code for Final Verification

Purpose

Use this appendix after completing Part 15 to compare the complete new files and the navigation change.

Appendix A — ViewModels/DashboardViewModel.cs

namespace ProductManagement.ViewModels;

public class DashboardViewModel
{
    public int TotalProducts { get; set; }

    public int TotalCategories { get; set; }

    public int TotalQuantity { get; set; }

    public decimal InventoryValue { get; set; }

    public decimal AveragePrice { get; set; }

    public List<CategorySummaryViewModel> CategorySummaries { get; set; }
        = new();
}

public class CategorySummaryViewModel
{
    public string CategoryName { get; set; } = string.Empty;

    public int ProductCount { get; set; }

    public int TotalQuantity { get; set; }

    public decimal InventoryValue { get; set; }
}

Appendix B — Controllers/DashboardController.cs

using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using ProductManagement.Data;
using ProductManagement.ViewModels;

namespace ProductManagement.Controllers;

[Authorize]
public class DashboardController : Controller
{
    private readonly ApplicationDbContext _context;

    public DashboardController(
        ApplicationDbContext context)
    {
        _context = context;
    }

    public async Task<IActionResult> Index()
    {
        var totalProducts =
            await _context.Products.CountAsync();

        var totalCategories =
            await _context.Categories.CountAsync();

        var totalQuantity =
            await _context.Products
                .SumAsync(p => (int?)p.Quantity) ?? 0;

        var inventoryValue =
            await _context.Products
                .SumAsync(
                    p => (decimal?)(p.Price * p.Quantity))
            ?? 0m;

        var averagePrice =
            await _context.Products
                .AverageAsync(p => (decimal?)p.Price)
            ?? 0m;

        var categorySummaries =
            await _context.Products
                .GroupBy(
                    p => p.Category != null
                        ? p.Category.Name
                        : "Unassigned")
                .Select(g => new CategorySummaryViewModel
                {
                    CategoryName = g.Key,
                    ProductCount = g.Count(),
                    TotalQuantity = g.Sum(
                        p => p.Quantity),
                    InventoryValue = g.Sum(
                        p => p.Price * p.Quantity)
                })
                .OrderBy(x => x.CategoryName)
                .ToListAsync();

        var viewModel = new DashboardViewModel
        {
            TotalProducts = totalProducts,
            TotalCategories = totalCategories,
            TotalQuantity = totalQuantity,
            InventoryValue = inventoryValue,
            AveragePrice = averagePrice,
            CategorySummaries = categorySummaries
        };

        return View(viewModel);
    }
}

Appendix C — Views/Dashboard/Index.cshtml

@model ProductManagement.ViewModels.DashboardViewModel

@{
    ViewData["Title"] = "Dashboard";
}

<h1>Dashboard</h1>

<div class="row g-3 mb-4">

    <div class="col-md-4">
        <div class="card h-100">
            <div class="card-body">
                <h5 class="card-title">Total Products</h5>
                <p class="display-6">@Model.TotalProducts</p>
            </div>
        </div>
    </div>

    <div class="col-md-4">
        <div class="card h-100">
            <div class="card-body">
                <h5 class="card-title">Total Categories</h5>
                <p class="display-6">@Model.TotalCategories</p>
            </div>
        </div>
    </div>

    <div class="col-md-4">
        <div class="card h-100">
            <div class="card-body">
                <h5 class="card-title">Total Quantity</h5>
                <p class="display-6">@Model.TotalQuantity</p>
            </div>
        </div>
    </div>

    <div class="col-md-6">
        <div class="card h-100">
            <div class="card-body">
                <h5 class="card-title">Inventory Value</h5>
                <p class="display-6">
                    RM @Model.InventoryValue.ToString("N2")
                </p>
            </div>
        </div>
    </div>

    <div class="col-md-6">
        <div class="card h-100">
            <div class="card-body">
                <h5 class="card-title">Average Product Price</h5>
                <p class="display-6">
                    RM @Model.AveragePrice.ToString("N2")
                </p>
            </div>
        </div>
    </div>

</div>

<h2>Category Summary</h2>

@if (!Model.CategorySummaries.Any())
{
    <p>No product data is available yet.</p>
}
else
{
    <table class="table table-striped">
        <thead>
            <tr>
                <th>Category</th>
                <th>Products</th>
                <th>Total Quantity</th>
                <th>Inventory Value</th>
            </tr>
        </thead>
        <tbody>
        @foreach (var item in Model.CategorySummaries)
        {
            <tr>
                <td>@item.CategoryName</td>
                <td>@item.ProductCount</td>
                <td>@item.TotalQuantity</td>
                <td>RM @item.InventoryValue.ToString("N2")</td>
            </tr>
        }
        </tbody>
    </table>
}

Appendix D — _Layout.cshtml Dashboard Navigation Block

@if (User.Identity?.IsAuthenticated == true)
{
    <li class="nav-item">
        <a class="nav-link text-dark"
           asp-area=""
           asp-controller="Dashboard"
           asp-action="Index">
            Dashboard
        </a>
    </li>
}

Appendix E — Final Verification Commands

cd ~/aspnet-mvc-tutorial/ProductManagement

dotnet build
dotnet run

Browser check:

/Dashboard

SQLite verification:

sqlite3 ProductManagement.db

SELECT COUNT(*) FROM Products;
SELECT COUNT(*) FROM Categories;
SELECT SUM(Quantity) FROM Products;
SELECT SUM(Price * Quantity) FROM Products;
SELECT AVG(Price) FROM Products;

.quit

Final Part 15 checkpoint

If the Dashboard loads for authenticated users, all summary values match SQLite, and the Category Summary groups Products correctly, Part 15 is complete.

Next: Part 16 — Error Handling, Logging and Security

Part 16 will introduce ILogger, production error handling, secure error messages, anti-forgery review, overposting review, authorization checks and practical application-hardening techniques.

InfoTech

ASP.NET CORE MVC - Building a Dashboard with Aggregate Data

Building a Dashboard with Aggregate Data

1. Open and Verify the Part 14 Project

2. What Is Aggregate Data?

3. Create DashboardViewModel.cs

4. Understand the Dashboard ViewModel

5. Create DashboardController.cs

6. Understand CountAsync()

7. Understand SumAsync()

8. Calculate Inventory Value

9. Calculate Average Price

10. Group Products by Category

11. Why Use a Separate CategorySummaryViewModel?

12. Protect the Dashboard

13. Create the Dashboard View Folder

14. Create Views/Dashboard/Index.cshtml

15. Understand the Summary Cards

16. Add Dashboard to the Shared Navigation

17. Build the Project

18. Run and Test the Dashboard

19. Verify Total Products in SQLite

20. Verify Total Quantity

21. Verify Inventory Value

22. Verify Average Price

23. Verify Category Summaries

24. No Migration Is Required

25. Troubleshooting

26. Hands-On Exercise

27. Knowledge Check

28. Part 15 Summary

Appendix — Full Code for Final Verification

Appendix A — ViewModels/DashboardViewModel.cs

Appendix B — Controllers/DashboardController.cs

Appendix C — Views/Dashboard/Index.cshtml

Appendix D — _Layout.cshtml Dashboard Navigation Block

Appendix E — Final Verification Commands