Tuesday, March 14, 2023

Data Caching in ASP.NET 7.0

Introduction

We will explore server-side data caching in ASP.NET 7.0.

Caching refers to the process of storing frequently used data so it can be served much faster for any future requests. So, we take the most frequently used data and copy it into temporary storage so that it can be accessed much faster in future calls from the client.

There are two important terms used with cache, cache hit and cache miss. A cache hit occurs when data can be found in cache and a cache miss occurs when data can't be found in cache.

Companion Video: https://youtu.be/HRNmMwztBg8

Source Code: https://github.com/medhatelmasry/DataCacheDemo

Pre-requisites

In order to follow with this tutorial you will need:

  • Visual Studio Code
  • .net 7.0

Let's do it

Consider the following API endpoint that returns some product data. 

https://northwind.vercel.app/api/products

Structure of the returned JSON object looks like this:

This data rarely changes. It would be nice if we can preserve this data locally in cache to save on the latency penalty of making an online request for the data. Well, this can be achieved by doing some ASP.NET server-side data caching. Let’s see how this works.

We first create an ASP.NET Razor Pages with the following terminal window instructions:

dotnet new razor -f net7.0 --no-https -o DataCacheDemo
cd DataCacheDemo
mkdir Models
code .

In the above instructions, we create a razor pages application with .NET 7.0 in folder DataCacheDemo. We change directory to DataCacheDemo, create a Models folder, then open the project in VS Code.

In the Models folder, create a class named Product with the following code:

public partial class Product {
    [JsonPropertyName("id")]
    public int Id { get; set; }

    [JsonPropertyName("quantityPerUnit")]
    public string? QuantityPerUnit { get; set; }

    [JsonPropertyName("unitPrice")]
    public decimal? UnitPrice { get; set; }

    [JsonPropertyName("name")]
    public string Name { get; set; } = null!;
}

The namespaces for JsonPropertyName should resolve to: using System.Text.Json.Serialization;

Open Pages/Index.cshtml.cs in the editor. 

Add the following bindable property at the top of the IndexModel class definition:

[BindProperty]
public Product[]? Products { get; set; }

Add the following helper method that accesses endpoint https://northwind.vercel.app/api/products, reads the JSON object, hydrates an array of Product objects with data, then returns an array of Product objects.

private async Task<Product[]> GetProductsAsync() {
    HttpClient client = new HttpClient();
    var stream = client.GetStreamAsync("https://northwind.vercel.app/api/products");
    var productList = await JsonSerializer.DeserializeAsync<Product[]>(await stream);

    return productList!;
}

Replace the “public void OnGet()” method with the following “public async Task OnGet()” method:

public async Task OnGet() {
    var T1 = DateTime.Now;

    Products = await GetProductsAsync();
    var T2 = DateTime.Now;


    TimeSpan duration = T2 - T1;

    Console.WriteLine($"Duration in milliseconds: {duration.TotalMilliseconds}");
}


In the above code, we obtain the time before we call the GetProductsAsync() method (T1) and the time after the method is called (T2) to determine how long the request to read the Products API data takes (T2-T1). The duration in milliseconds is then printed in the terminal window.

Replace Pages/Index.cshtml with the following markup code:

@page
@model IndexModel
@{
    ViewData["Title"] = "Northwind Products List";
}

<h3>@ViewData["Title"]</h3>

<table class="table table-striped">
    <tr>
        <th>ID</th>
        <th>Name</th>
        <th>Quantity</th>
        <td>Unit Price</td>
    </tr>
    @foreach(var item in Model.Products!) {
        <tr>
            <td>@item.Id</td>
            <td>@item.Name</td>
            <td>@item.QuantityPerUnit</td>
            <td>@item.UnitPrice</td>
        </tr>
    }
</table>

Now, when you run the application with 'dotnet watch', it should look like this:

Have a look at the output console window. It will reveal how long it took to get a response from the API service. I got the following results:

Duration in milliseconds: 393.65

Using Data Caching

Let us make use of caching so that data received from the API endpoint is cached for a certain amount of time. 

Add the following code to Program.cs, just before “var app = builder.Build();”:

builder.Services.AddMemoryCache();

Back in Pages/Index.cshtml.cs, Add the following instance variable at the top of the IndexModel class definition:

private readonly IMemoryCache _memoryCache;

Modify the constructor by adding these highlighted items of code:

public IndexModel(ILogger<IndexModel> logger, IMemoryCache memoryCache) {
    _logger = logger;
    _memoryCache = memoryCache;
}

Replace the GetProductsAsync() helper method with the following code that uses ASP.NET server-side data caching:

private async Task<Product[]> GetProductsAsync() {
    var cacheKey = "productList";
    //checks if cache entries exists
    if (!_memoryCache.TryGetValue(cacheKey, out Product[]? productList)) {
        //calling the server
        HttpClient client = new HttpClient();
        var stream = client.GetStreamAsync("https://northwind.vercel.app/api/products");
        productList = await JsonSerializer.DeserializeAsync<Product[]>(await stream);

        //setting up cache options
        var cacheExpiryOptions = new MemoryCacheEntryOptions {
            AbsoluteExpiration = DateTime.Now.AddSeconds(50),
            Priority = CacheItemPriority.High,
            SlidingExpiration = TimeSpan.FromSeconds(20)
        };
        //setting cache entries
        _memoryCache.Set(cacheKey, productList, cacheExpiryOptions);

        Console.WriteLine("Data from API (cache miss)");
    } else {
        Console.WriteLine("Data from CACHE (cache hit)");
    }

    return productList!;
}

In the above code, products data is refreshed every 50 seconds, or 20 seconds if there is no access to the page during that time period. Otherwise, the data is served from cache. 

If data is served from the online API, then message "Data from API (cache miss)" will display in the output console window. Otherwise, if data is served from cache, then message "Data from CACHE (cache hit)" will display in the output console window.

Here is a proper explanation of what these values mean:

Priority Priority defines the priority of keeping cache entry in the cache. The default value is set to Normal. The values for priority can be High, Normal, Low, and NeverRemove.
Sliding Expiration A specific timespan within which the cache will expire if it is not used by anyone. As we set the sliding expiration to 20 seconds, it means that if there is no client request for 20 seconds the cache will be expired.
Absolute Expiration It refers to the actual expiration of the cache entry without considering the sliding expiration. In our code, we set the absolute expiration to 50 seconds. It means the cache will expire every 50 seconds.

Run the application and repeatedly refresh your browser. You will discover that there is a huge improvement in efficiency with caching:

Data from API (cache miss)
Duration in milliseconds: 265.364
Data from CACHE (cache hit)
Duration in milliseconds: 0.073
Data from CACHE (cache hit)
Duration in milliseconds: 0.024
Data from CACHE (cache hit)
Duration in milliseconds: 0.044
Data from CACHE (cache hit)
Duration in milliseconds: 0.025

It is pretty obvious that there is much performance improvement by using some basic data caching in your ASP.NET application. This applies to both Razor Pages and MVC web applications.

Sunday, January 29, 2023

Visualizing data with Chart.js in ASP.NET 7.0

With time, the volume of data in our databases increases exponentially. Excessive raw data is simply noise if we cannot summarize, examine, and visualize the information. In today’s tutorial, we will look at one way of visualizing data using Chart.js. This is a free JavaScript library that you can use within any web application technology. We will, of course, see how we can use Chart.js in an ASP.NET 7.0 web app. I will be using Visual Studio Code & the SQLite database in this tutorial.

Source Code: https://github.com/medhatelmasry/ChartJsRazor 
Companion Video: https://youtu.be/ya2XyvRqJIw

VS Code extensions needed:

  • C#
  • C# Extensions

Razor Pages

In a suitable working directory, create a Razor Pages web application with:

dotnet new razor -f net7.0 --auth individual -o ChartJsRazor
cd ChartJsRazor
mkdir Models
code .
dotnet watch

I chose to create a web app with individual authentication simply because it automatically adds support for Entity Framework and SQLite. 

Sample Data

We will be importing sample data from a CSV file, students.csv, that contains some sample student data. Therefore, we will add this package to help us with the CSV file import process. 

dotnet add package CsvHelper

Here is a sample of what data in Data/students.csv looks like:

StudentId,FirstName,LastName,School
1,Tom,Max,Nursing
2,Ann,Fay,Mining
3,Joe,Sun,Nursing
4,Sue,Fox,Computing
5,Ben,Ray,Mining
6,Zoe,Cox,Business
7,Sam,Ray,Mining
8,Dan,Ash,Medicine
9,Pat,Lee,Computing
10,Kim,Day,Nursing
11,Tim,Rex,Computing
12,Rob,Ram,Business
13,Jan,Fry,Mining
14,Jim,Tex,Nursing
15,Ben,Kid,Business
16,Mia,Chu,Medicine
17,Ted,Tao,Computing
18,Amy,Day,Business
19,Ian,Roy,Nursing
20,Liz,Kit,Nursing
21,Mat,Tan,Medicine
22,Deb,Roy,Medicine
23,Ana,Ray,Mining
24,Lyn,Poe,Computing
25,Amy,Raj,Nursing
26,Kim,Ash,Mining
27,Bec,Kid,Nursing
28,Eva,Fry,Computing
29,Eli,Lap,Business
30,Sam,Yim,Nursing
31,Joe,Hui,Mining
32,Liz,Jin,Nursing
33,Ric,Kuo,Business
34,Pam,Mak,Computing
35,Cat,Yao,Medicine
36,Lou,Zhu,Mining
37,Tom,Dag,Business
38,Stu,Day,Business
39,Tom,Gad,Mining
40,Bob,Bee,Business
41,Jim,Ots,Business
42,Tom,Mag,Business
43,Hal,Doe,Mining
44,Roy,Kim,Mining
45,Vis,Cox,Nursing
46,Kay,Aga,Nursing
47,Reo,Hui,Nursing
48,Bob,Roe,Mining
49,Jay,Eff,Computing
50,Eva,Chu,Business
51,Lex,Rae,Nursing
52,Lin,Dex,Mining
53,Tom,Dag,Business
54,Ben,Shy,Computing
55,Rob,Bos,Nursing
56,Ali,Mac,Business
57,Ken,Sim,Medicine
58,May,Chu,Business

Student.cs

Create a class file named Student.cs in the Models folder with the following content.

public class Student {
   public int StudentId { get; set; }
   public string? LastName { get; set; }
   public string? FirstName { get; set; }
   public string? School { get; set; }
}

Add the following code to Data/ApplicationDbContext.cs to specify the Students DbSet and to also import sample data from Data/students.csv:

public DbSet<Student> Students => Set<Student>();
private static IEnumerable<Student> GetStudents() {
    string[] p = { Directory.GetCurrentDirectory(), "Data", "students.csv" };
    var csvFilePath = Path.Combine(p);
    var config = new CsvConfiguration(CultureInfo.InvariantCulture) {
        PrepareHeaderForMatch = args => args.Header.ToLower(),
    };
    var data = new List<Student>().AsEnumerable();
    using (var reader = new StreamReader(csvFilePath)) {
        using (var csvReader = new CsvReader(reader, config)) {
            data = (csvReader.GetRecords<Student>()).ToList();
        }
    }
    return data;
}
protected override void OnModelCreating(ModelBuilder modelBuilder) {
    base.OnModelCreating(modelBuilder);
    modelBuilder.Entity<Student>().HasData(GetStudents());
}

In the above code, students data is being seeded in the OnModelCreating() method by reading contents of students.csv file.

Let us add a migration and subsequently update the database. Execute the following CLI commands in a terminal window.

dotnet ef migrations add Student -o Data/Migrations
dotnet ef database update

At this point the database and tables are created.

Chart.js library

The official website for Chart.js is https://www.chartjs.org/ where you can find documentation and examples. You can either point to the Chart.js JavaScript library on an online CDN (Content Delivery Network) or download and install the library directly into your application. 

Chart.js library online CDN

This is the link you can use to point to an online library:

<script src="https://cdn.jsdelivr.net/npm/chart.js@3.9.1/dist/chart.min.js"></script>

Download Chart.js library

Run the following command if you do not already have the LibMan .NET tool:

dotnet tool install -g Microsoft.Web.LibraryManager.Cli

Run the following command to get the Chart.js client library by using LibMan

libman install chartjs --provider jsdelivr --destination wwwroot/lib/chartjs --files chart.min.js --files chart.js

This places the chart.js JavaScript files in the wwwroot/lib/chartjs folder.

Vertical Bar Chart

We will re-purpose the Pages/Index.cshtml & Pages/Index.cshtml.cs files so that they render charts that reveal how many students are in each school. We will add a class Models/SchoolCount.cs that simply holds two properties: School (string) and Count (int). Add the following SchoolCount class to the Models folder:

public class SchoolCount {
    public string? Name { get; set; }
    public int Count { get; set; }
}

Add the following highlighted code to Pages/Index.cshtml.cs:

class IndexModel : PageModel
{
    private readonly ILogger<IndexModel> _logger;
    private readonly ApplicationDbContext _context;

    [BindProperty]
    public List<SchoolCount> ChartData { get; set; } = default!;

    public IndexModel(ILogger<IndexModel> logger,
    ApplicationDbContext context)
    {
        _logger = logger;
        _context = context;
    }

    public void OnGet() {
        List<SchoolCount> studentsInSchool = new List<SchoolCount>();

        var data = _context.Students
           .GroupBy(_ => _.School)
           .Select(g => new {
               Name = g.Key,
               Count = g.Count()
           })
           .OrderByDescending(cp => cp.Count)
           .ToList();

        foreach (var item in data) {
            studentsInSchool.Add(new SchoolCount() {
                Name = item.Name,
                Count = item.Count
            });
        }

        ChartData = studentsInSchool;
    }
}

If you put a breakpoint to inspect the data that is being assigned to ChartData, you will find out the following:


SchoolCount
Computing 15
Business 15
Nursing 13
Mining 9
Medicing 6

The real interaction with the Chart.js JavaScript library will happen the Pages/Index.cshtml file. We will first display a simple vertical bar chart. Replace the contents of Pages/Index.cshtml with the following:

@page
@model IndexModel
@{
    ViewData["Title"] = "Chart.js";
}

<div class="text-center">
    <h4 class="display-4">Students by school</h4>
        <div class="card">
            <div class="card-header">Vertical Bar Chart</div>
            <div class="chart-container">
                <canvas id="bar-chart" width="100" height="300"></canvas>
            </div>                     
        </div>
</div>

<script src="https://cdn.jsdelivr.net/npm/chart.js@3.9.1/dist/chart.min.js"></script>
@* <script src="lib/chartjs/chart.min.js"></script> *@

<script>

    var Count = [];
    var Schools = [];

    @if (Model.ChartData.Count > 0)
    {
        foreach (var item in Model.ChartData)
        {
            @:Count.push(@item.Count);
            @:Schools.push('@item.Name');
        }
    }
    
    var School = {
        label: "School",
        data: Count,
        backgroundColor: [
            'rgba(255, 99, 132, 0.2)',
            'rgba(54, 162, 235, 0.2)',
            'rgba(255, 206, 86, 0.2)',
            'rgba(75, 192, 192, 0.2)',
            'rgba(153, 102, 255, 0.2)',
            'rgba(255, 159, 64, 0.2)'
        ],
        borderColor: [
            'rgba(255, 99, 132, 1)',
            'rgba(54, 162, 235, 1)',
            'rgba(255, 206, 86, 1)',
            'rgba(75, 192, 192, 1)',
            'rgba(153, 102, 255, 1)',
            'rgba(255, 159, 64, 1)'
        ],        
        borderWidth: 1,
        lineTension: 0,
    };

    var datasetvalues = {
        labels: Schools, // x-azis label values
        datasets: [School] // y-axis
    };

    barChartOptions = {
        indexAxis: 'x',
        responsive: true,
        maintainAspectRatio: false,
        scales: {
            x: {
                barPercentage: 1,
                categoryPercentage: 0.6,
            },
            y: {
                barPercentage: 1,
                categoryPercentage: 0.6,
                ticks: {
                    beginAtZero: true
                }
            }
        },
        plugins: {
            title: {
                display: true,
                text: 'Students by school'
            }
        }
    }

    var verticalBarChart = new Chart(
        document.getElementById('bar-chart').getContext('2d'), {
        type: 'bar',
        data: datasetvalues,
        options: barChartOptions
    });

</script>

Explaining the code:

1) At first, we place a <canvas> element in our markup where the chart will be injected:

<canvas id="bar-chart" width="100" height="300"></canvas>

2) Load the Chart.js library from a CDN:

<script src="https://cdn.jsdelivr.net/npm/chart.js@3.9.1/dist/chart.min.js"></script>

We could, instead, use a locally available library with:

<script src="lib/chartjs/chart.min.js"></script>

3) We then declare two JavaScript arrays (Count & Schools), which will contain the count and names of schools respectively. The data in the C# ChartData collection will then populate these two arrays. You may find the “@:” operator in the foreach loop strange. This simply renders the line as HTML (in this case JavaScript).

var Count = [];
var Schools = [];

@if (Model.ChartData.Count > 0) {
    foreach (var item in Model.ChartData)
    {
        @:Count.push(@item.Count);
        @:Schools.push('@item.Name');
    }
}

4) The School object describes the design of the bars and the data source for the count in the chart

5) The datasetvalues object describes the data source of the school names

6) The barChartOptions object describes the chart options

7) Finally the new Chart(….) command is what actually creates the chart using the previously described objects.

This is what our bar-chart looks like:


From this chart we can gather that most of our students are studying Nursing & Business.  Medicine has the least students. We need more medical doctors.

Horizontal Bar Chart

The horizontal bar-chart is like the previous bar-chart, except that the bars display horizontally. To add a horizontal bar-chart, add this markup after the previous bar chart and inside <div class="card">:

<div class="card-header">Horizontal bar chart</div>
<div class="chart-container">
    <canvas id="horizontal-bar-chart" width="100" height="300"></canvas>
</div>

Add the following code below “var verticalBarChart . . . ” at the bottom of Index.chtml:

horizonntalBarChartOptions = barChartOptions;
horizonntalBarChartOptions['indexAxis'] = 'y' ;
delete horizonntalBarChartOptions.scales;
var horizonntalBarChart = new Chart(
    document.getElementById('horizontal-bar-chart').getContext('2d'), {
    type: 'bar',
    data: datasetvalues,
    options: horizonntalBarChartOptions
});

In the above code, we make a copy of the barChartOptions and modify it slightly:
  • Set 'indexAxis' to ‘y’
  • Delete scales
Our horizontal bar-chart looks like this:


Other types of charts

Using the same data, let us add these charts:
  • pie-chart
  • doughnut-chart
  • polar-area-chart
Add this HTML under similar markup for the horizontal bar-chart so that we have canvas elements for the three additional charts that we require:

<div class="card-header">Pie chart</div>
<div class="chart-container">
    <canvas id="pie-chart" width="100" height="300"></canvas>
</div>  
<div class="card-header">Doughnut chart</div>
<div class="chart-container">
    <canvas id="doughnut-chart" width="100" height="300"></canvas>
</div>  
<div class="card-header">Polar area chart</div>
<div class="chart-container">
    <canvas id="polar-area-chart" width="100" height="300"></canvas>
</div>

Add the following code below “var horizonntalBarChart . . . ” at the bottom of Index.chtml:

pieChartOptions = barChartOptions;
delete pieChartOptions.indexAxis;
delete pieChartOptions.scales;

var pieChart = new Chart(
    document.getElementById('pie-chart').getContext('2d'), {
    type: 'pie',
    data: datasetvalues,
    options: pieChartOptions
});

var doughnutChart = new Chart(
    document.getElementById('doughnut-chart').getContext('2d'), {
        type: 'doughnut',
        data: datasetvalues,
        options: pieChartOptions
});    

var polarAreaChart = new Chart(
    document.getElementById('polar-area-chart').getContext('2d'), {
    type: 'polarArea',
    data: datasetvalues,
    options: pieChartOptions
});

We should see these additional charts on our home page:


Summary

In addition to the five chart types that we created, there are many more others that you can use such as: Area Chart, Bubble Chart, Line Chart, Radar Chart, Scatter Chart, etc…

You have control over the colors, scales, and other features. Best of all, it is easy and can be used with any backend technology such as ASP.NET, PHP, Node, etc…


ASP.NET 7 Minimal Web API with SQLite

This tutorial is about using ASP.NET 6.0 Minimal WebAPI. We will build a simple application that uses SQLite to save students data. We will then consume the API from an HTML page. 

Source Code: https://github.com/medhatelmasry/StudentsApi Companion Video: https://youtu.be/JG2TeGBs8MU

VS Code extensions needed:

  • C#
  • C# Extensions

In a suitable working directory, create a Web API web application with:

dotnet new webapi -f net7.0 --no-https --use-minimal-apis -o StudentsMinimalApi 
 
cd StudentsMinimalApi  

 code .

dotnet watch

The app will display in your browser, and you will see this:


Look at API code in Program.cs:

app.MapGet("/weatherforecast", () =>
{
    var forecast =  Enumerable.Range(1, 5).Select(index =>
        new WeatherForecast
        (
            DateTime.Now.AddDays(index),
            Random.Shared.Next(-20, 55),
            summaries[Random.Shared.Next(summaries.Length)]
        ))
        .ToArray();
    return forecast;
})
.WithName("GetWeatherForecast"); 
.WithOpenApi();

Add this tool if you do not already have it: 

dotnet tool install --global dotnet-ef 

 Let us add these packages that provide support for SQLite and reading a CSV file: 

dotnet add package Microsoft.EntityFrameworkCore.Design -v 7.0
dotnet add package Microsoft.EntityFrameworkCore.Tools -v 7.0
dotnet add package Microsoft.EntityFrameworkCore -v 7.0
dotnet add package Microsoft.EntityFrameworkCore.SQLite -v 7.0
dotnet add package Microsoft.EntityFrameworkCore.SQLite.Design -v 7.0
dotnet add package CsvHelper

Create three folders: wwwroot, Data & Models.

Inside the Models folder, add the following Student class: 

public class Student {
        public int StudentId { get; set; }
        public string? LastName { get; set; }
        public string? FirstName { get; set; }
        public string? School { get; set; }
}

Developers prefer having sample data when building data driven applications. Therefore, we will create some sample data to ensure that our application behaves as expected. Copy the following data and save it in a text file wwwroot/students.csv:

StudentId,FirstName,LastName,School
1,Tom,Max,Nursing
2,Ann,Fay,Mining
3,Joe,Sun,Nursing
4,Sue,Fox,Computing
5,Ben,Ray,Mining
6,Zoe,Cox,Business
7,Sam,Ray,Mining
8,Dan,Ash,Medicine
9,Pat,Lee,Computing
10,Kim,Day,Nursing
11,Tim,Rex,Computing
12,Rob,Ram,Business
13,Jan,Fry,Mining
14,Jim,Tex,Nursing
15,Ben,Kid,Business
16,Mia,Chu,Medicine
17,Ted,Tao,Computing
18,Amy,Day,Business
19,Ian,Roy,Nursing
20,Liz,Kit,Nursing
21,Mat,Tan,Medicine
22,Deb,Roy,Medicine
23,Ana,Ray,Mining
24,Lyn,Poe,Computing
25,Amy,Raj,Nursing
26,Kim,Ash,Mining
27,Bec,Kid,Nursing
28,Eva,Fry,Computing
29,Eli,Lap,Business
30,Sam,Yim,Nursing
31,Joe,Hui,Mining
32,Liz,Jin,Nursing
33,Ric,Kuo,Business
34,Pam,Mak,Computing
35,Cat,Yao,Medicine
36,Lou,Zhu,Mining
37,Tom,Dag,Business
38,Stu,Day,Business
39,Tom,Gad,Mining
40,Bob,Bee,Business
41,Jim,Ots,Business
42,Tom,Mag,Business
43,Hal,Doe,Mining
44,Roy,Kim,Mining
45,Vis,Cox,Nursing
46,Kay,Aga,Nursing
47,Reo,Hui,Nursing
48,Bob,Roe,Mining
49,Jay,Eff,Computing
50,Eva,Chu,Business
51,Lex,Rae,Nursing
52,Lin,Dex,Mining
53,Tom,Dag,Business
54,Ben,Shy,Computing
55,Rob,Bos,Nursing
56,Ali,Mac,Business
57,Ken,Sim,Medicine

Add the following connection string to appsettings.json:

"ConnectionStrings": {
  "DefaultConnection": "DataSource=school.db"
},

Next, we need to add an Entity Framework context class. Inside the Data folder, add a class file named SchoolContext with the following content: 

public class SchoolDbContext : DbContext {
    public DbSet<Student> Students => Set<Student>();

    public SchoolDbContext(DbContextOptions<SchoolDbContext> options)
            : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder) {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Student>().HasData(GetStudents());
    }

    private static IEnumerable<Student> GetStudents() {
        string[] p = { Directory.GetCurrentDirectory(), "wwwroot", "students.csv" };
        var csvFilePath = Path.Combine(p);

        var config = new CsvConfiguration(CultureInfo.InvariantCulture) {
            PrepareHeaderForMatch = args => args.Header.ToLower(),
        };

        var data = new List<Student>().AsEnumerable();
        using (var reader = new StreamReader(csvFilePath)) {
            using (var csvReader = new CsvReader(reader, config)) {
                data = (csvReader.GetRecords<Student>()).ToList();
            }
        }

        return data;
    }

}

In the above code, student data is being seeded in the OnModelCreating() method by reading contents of students.csv file.

We need to register the context class (SchoolDbContext) with dependency injection in Program.cs. Add the following code right before “var app = builder.Build();” in Program.cs

var connStr = builder.Configuration.GetConnectionString("DefaultConnection") 
    ?? throw new InvalidOperationException("Connection string 'DefaultConnection' not found.");
builder.Services.AddDbContext<SchoolDbContext>(option => option.UseSqlite(connStr));

Let us add a migration and subsequently update the database. Execute the following CLI commands in a terminal window.

dotnet ef migrations add M1 -o Data/Migrations
dotnet ef database update

At this point the database and tables are created.

Students API

Let us add API endpoints that:
  • Read all the students
  • Read students that belong to a particular school
  • Read student data by id
  • Add student data
  • Update student data
  • Delete student data
Therefore, add the following code to Program.cs just before the final statement “app.Run();”:

app.MapGet("/api/students", async (SchoolDbContext db) =>
    await db.Students.ToListAsync());

app.MapGet("/api/students/school/{school}", async (string school, SchoolDbContext db) =>
    await db.Students.Where(t => t.School!.ToLower() == school.ToLower()).ToListAsync());

app.MapGet("/api/students/{id}", async (int id, SchoolDbContext db) =>
    await db.Students.FindAsync(id)
        is Student student ? Results.Ok(student) : Results.NotFound());

app.MapPost("/api/students", async (Student student, SchoolDbContext db) =>
{
    db.Students.Add(student);
    await db.SaveChangesAsync();

    return Results.Created($"/students/{student.StudentId}", student);
});

app.MapPut("/api/students/{id}", async (int id, Student inputStudent, SchoolDbContext db) =>
{
    var student = await db.Students.FindAsync(id);

    if (student is null) return Results.NotFound();

    student.FirstName = inputStudent.FirstName;
    student.LastName = inputStudent.LastName;
    student.School = inputStudent.School;

    await db.SaveChangesAsync();

    return Results.NoContent();
});

app.MapDelete("/api/students/{id}", async (int id, SchoolDbContext db) =>
{
    if (await db.Students.FindAsync(id) is Student student)
    {
        db.Students.Remove(student);
        await db.SaveChangesAsync();
        return Results.Ok(student);
    }

    return Results.NotFound();
});

Run the app and point your browser to /api/students. You should see the following results:


Try endpoint /api/students/22


Try endpoint /api/students/school/medicine:


OPTIONAL: If you want migrations to be applied automatically, add the following code to Program.cs right before the last “app.Run()” statement

using (var scope = app.Services.CreateScope()) {
    var services = scope.ServiceProvider;

    var context = services.GetRequiredService<SchoolDbContext>();    
    context.Database.Migrate();
}

CORS (Cross-Origin Resource Sharing)

In wwwroot folder, create a file named show.html and add to it this HTML/JavaScript code:

<!DOCTYPE html>
<html>
  <html>
    <head>
      <meta charset="utf-8" />
      <title>Test API</title>
    </head>
    <body>
      <h3>Test API</h3>
      <button id="btnGetData">Get Data</button>
      <pre id="preOutput"></pre>
      <script>
        const url = "PUT-API-URL-HERE";

        var showResponse = function (object) {
          document.querySelector("#preOutput").innerHTML = JSON.stringify(
            object,
            null,
            4
          );
        };

        const button = document.querySelector("#btnGetData");
        button.addEventListener("click", (e) => {
          getData();
        });

        var getData = async function () {
          await fetch(url)
            .then((response) => {
              return response.json();
            })
            .then((data) => {
              showResponse(data);
            });

          return false;
        };
      </script>
    </body>
  </html>
</html>

Replace PUT-API-URL-HERE with the URL that gets all the students (Example: http://localhost:5143/api/students). 

From the file system, double-click on the wwwroot/show.html file. You will see the following page:

When you click on the “Get Data” button, nothing will appear because there is a JavaScript error. To understand where this error is coming from, hit F12 in your browser and check the console. This error will appear:


We need to enable CORS on the API project. This is done by adding the following code in Program.cs just before “var app = builder.Build();”:
 
// Add Cors
builder.Services.AddCors(o => o.AddPolicy("Policy", builder => {
  builder.AllowAnyOrigin()
    .AllowAnyMethod()
    .AllowAnyHeader();
}));

Also, in the same Program.cs file, add this code just after “var app = builder.Build();”: 

app.UseCors("Policy");

The above specifies a CORS policy simply named Policy. We can then selectively apply this policy for each endpoint in our API service. Therefore, add [EnableCors("Policy")] before the async keyword in each endpoint as shown below:

app.MapGet("/api/students", [EnableCors("Policy")] async (SchoolDbContext db) =>
    await db.Students.ToListAsync());

app.MapGet("/api/students/school/{school}", [EnableCors("Policy")] async (string school, SchoolDbContext db) =>
    await db.Students.Where(t => t.School!.ToLower() == school.ToLower()).ToListAsync());

app.MapGet("/api/students/{id}", [EnableCors("Policy")] async (int id, SchoolDbContext db) =>
    await db.Students.FindAsync(id)
        is Student student ? Results.Ok(student) : Results.NotFound());

app.MapPost("/api/students", [EnableCors("Policy")] async (Student student, SchoolDbContext db) =>
{
    db.Students.Add(student);
    await db.SaveChangesAsync();

    return Results.Created($"/students/{student.StudentId}", student);
});

app.MapPut("/api/students/{id}", [EnableCors("Policy")] async (int id, Student inputStudent, SchoolDbContext db) =>
{
    var student = await db.Students.FindAsync(id);

    if (student is null) return Results.NotFound();

    student.FirstName = inputStudent.FirstName;
    student.LastName = inputStudent.LastName;
    student.School = inputStudent.School;

    await db.SaveChangesAsync();

    return Results.NoContent();
});

app.MapDelete("/api/students/{id}", [EnableCors("Policy")] async (int id, SchoolDbContext db) =>
{
    if (await db.Students.FindAsync(id) is Student student)
    {
        db.Students.Remove(student);
        await db.SaveChangesAsync();
        return Results.Ok(student);
    }

    return Results.NotFound();
});

Save your code then make a new request for data from show.html. This time you should be successful:


Congrats that you got this far.