Creating an API with a database (using EFCore)

#tutorial #dotnet #beginners #webdev

In this tutorial I want to create a new project with a database using entity framework core. For this example we will create a custom blog API. Entity Framework is a very useful package to quickly build code-first database applications with C#.

There are different packages available if you require a different connection type. For example, if you want to host your database on a MSSQL instance you use Microsoft.EntityFrameworkCore.SqlServer.
If you want to store the database in a local file within the project you use Microsoft.EntityFrameworkCore.Sqlite.
And you can use a database which exists only in memory and you would want to use Microsoft.EntityFrameworkCore.InMemory.

For this tutorial we are using the following:

Visual Studio 2022
.NET version 5
Nuget: Microsoft.EntityFrameworkCore.Sqlite
Nuget: Microsoft.EntityFrameworkCore.Tools

Create a new project

Create a new solution in visual studio and add a new project.
We are using the default 'ASP.NET Core Web API' template. For this tutorial my project name is simply 'EntityFrameworkCoreDemo'

Next, we will add the nuget package for entity framework core.

Add the package

To add the package right click on your newly added project and click on Manage Nuget Package -> Browse tab -> Search for 'Microsoft.EntityFrameworkCore.Sqlite' and select Microsoft.EntityFrameworkCore.Sqlite. We are using version '5.0.15'. Click on install.
Do the same for Microsoft.EntityFrameworkCore.Tools

Creating a new database model file

Create a new folder called 'Models'
Create a new file (select the folder and press 'CTRL + SHIFT + A') called BloggingContext.cs.
This class will inherit from DbContext. public class BloggingContext : DbContext
The database will consist of a single table called 'Posts'.

Create a new file called Post.cs

public class Post
{
    [Key]
    public Guid Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

}

Now we need to tell the DbContext that these are part of the database by adding a DbSet to the file BloggingContext.

        public DbSet<Post> Posts { get; set; }

Because SQLite will store the database file locally we need to add the code where the file is stored. Create a constructor and add the code:

        public BloggingContext()
        {
            var folder = Environment.SpecialFolder.LocalApplicationData;
            var path = Environment.GetFolderPath(folder);
            DbPath = System.IO.Path.Join(path, "blogging.db");
        }

In the code above we are setting the database location to C:\Users\username\AppData\Local\blogging.db. Which is going to work fine on our local development machine, but not so much on a live production environment.

We need to modify the default OnConfiguring function to use the database located in the DbPath location. Add this code to the bloggingContext.cs.

protected override void OnConfiguring(DbContextOptionsBuilder options) => options.UseSqlite($"Data Source={DbPath}");

We are now done with the DbContext!

The file will look like this:

    public class BloggingContext : DbContext
    {
        public DbSet<Post> Posts { get; set; }

        public string DbPath { get; }

        public BloggingContext()
        {
            var folder = Environment.SpecialFolder.LocalApplicationData;
            var path = Environment.GetFolderPath(folder);
            DbPath = System.IO.Path.Join(path, "blogging.db");
        }

        protected override void OnConfiguring(DbContextOptionsBuilder options)
            => options.UseSqlite($"Data Source={DbPath}");
    }

Creating the Database

We run the following command to create the database.
Open Tools -> Nuget Package Manager -> Package manager console
Add-Migration init.

EntityFramework uses migration files to keep track of the state of the database.

To apply the migration to the database use:
update-database

Configure Startup

Open the startup.cs file and add the DbContext within the ConfigureServices functions. Add the newly created BloggingContext.

   services.AddDbContext<BloggingContext>();

Creating a controller

Next, we will create a controller for all create, read, update and delete operations for our posts.
Right click on the controller folder
Add a new File
Select API Controller with Actions, using Entity Framework .

Select the Post.cs model and our BloggingDbContext and leave it called PostController.cs.