How to C#: Creating an API with a database (using EFCore)
Tags: tutorial, dotnet, beginners, webdev
Published: 2022-03-21 09:26:27

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'
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/agvz6pf62tlf5yzyql31.png)

![Setup project](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/be6nxrnzp4w3if2b9txj.png)

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#
1. Create a new folder called 'Models'
2. Create a new file (select the folder and press 'CTRL + SHIFT + A') called BloggingContext.cs.
3. This class will inherit from `DbContext`.
`public class BloggingContext : DbContext`
4. 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; }
```
5. 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.


6. 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` .
![api step 1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f4uiyxxscppgl149lsa6.PNG)
Select the Post.cs model and our BloggingDbContext and leave it called `PostController.cs`.

![api step 2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5khq4kbzzuppxe7qingr.PNG)
Click add and wait for the scaffolding to finish.


![api step 3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gy2660z8a109ysbn7apx.PNG)
All Done! You can now run the application and test the endpoint using swagger.




Special thanks to the microsoft guide:
https://docs.microsoft.com/en-us/ef/core/get-started/overview/first-app?tabs=visual-studio