Last Updated on September 28, 2022 by Aram
In this tutorial we will learn how to build RESTful APIs using ASP.NET Core 6. The APIs will be connected to an SQL Server Express Database Instance using Entity Framework Core 6.
We will be building RESTful APIs for Movies Database; the APIs will be responsible to connect to the database and get/save/update/delete records to and from the database and return the results back to the client.
All the testing will happen through Postman, so make sure you install the latest desktop version of it from here, it is an amazing tool to simulate and test your RESTful APIs.
We will be using the latest version of ASP.NET Core (At the time of writing the current version is 6)
and for this we will be using the latest update of Visual studio 2022 (at the time of writing the current update version is 17.4.4).
This tutorial assumes that you have a basic knowledge and understanding of http, REST and database Object Relational mapping (entity framework) as well as ASP.NET and C#, and if you previously worked on ASP.NET Web API that would be even better, because ASP.NET Core Web API has been raised on top of the powerful and robust ASP.NET Web API.
So, let’s get started with building our Movies RESTful APIs using ASP.NET Core Web API 6 and Entity Framework Core 6 connected to an SQL Server Express database.
Preparation
We will begin by preparing our development environment.
You will need to have the latest update of Microsoft Visual Studio 2022 installed on your machine. It comes bundled with Dot Net Core 6
From the official Visual Studio website, you can download the latest version of Visual Studio 2022.
Tip: I am using the community version for my personal use; It is a fully-functional, lightweight, cloud-connected version of Visual Studio and better than that it is free! , so unless you already have the other versions already installed, then feel free to download the community version
For our database, we will be using SQL Server Express as our database engine and SQL Server Management studio (SSMS) as the tool that will ease creating, managing and exploring the SQL server express database, so let’s go ahead and download the latest versions of SQL Server Express and SQL Server Management Studio.
Once both SQL Server Express and SQL Server Management Studio are installed, start SSMS and then put the server name as your computer name with slash and the SQL Express server instance name (when you installed it) {Computer Name}\SQLEXPRESS, and press connect
This will connect the SSMS to your SQL Server Express Database Engine.
Creating the SQL Server Express Database
From the object explorer, right click on databases, then choose New Database, and enter the database name as MoviesDb, then click ok
You will notice that the database is now created in the object explorer
Now let’s create our Movie table and insert some initial data in it
To make this quick, I have prepared some SQL commands that will easy the creation of the table and insertion of data, so open a new query window (ctrl + n) , and execute the below command (f5)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
|
USE [MoviesDb]
GO
/****** Object: Table [dbo].[Movie] Script Date: 6/13/2020 10:55:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Movie](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[Genre] [nvarchar](255) NOT NULL,
[Duration] [nvarchar](255) NOT NULL,
[ReleaseDate] [smalldatetime] NOT NULL,
CONSTRAINT [PK_Movie] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Movie]
([Name]
,[Genre]
,[Duration]
,[ReleaseDate])
VALUES
('Bad Boys for Life'
,'Action/Comedy'
,'2h 5 mins'
,'2020-01-23 00:00:00')
GO
INSERT INTO [dbo].[Movie]
([Name]
,[Genre]
,[Duration]
,[ReleaseDate])
VALUES
('John Wick'
,'Action/Thriller'
,'1h 41 mins'
,'2014-10-24 00:00:00')
GO
INSERT INTO [dbo].[Movie]
([Name]
,[Genre]
,[Duration]
,[ReleaseDate])
VALUES
('The Martian'
,'Sci-fi/Drama'
,'2h 31 mins'
,'2015-10-02 00:00:00')
GO
INSERT INTO [dbo].[Movie]
([Name]
,[Genre]
,[Duration]
,[ReleaseDate])
VALUES
('How to Train Your Dragon: The Hidden World'
,'Animation/Family'
,'1h 45 mins'
,'2019-02-22 00:00:00')
GO
|
Now if you do a select query on the new table, you will get the data inserted in the above commands.
Both our database and table are now ready.
Note: a movies database should typically include more tables and relationships to better represent the proper information structure of movies, which usually includes more details in the movie table, also you will have a table for actor, for director and for genre, and then you will have some surrogate table for multiple genre movies and so on. But for the sake of this tutorial and to not prolong it with extra details, I just wanted to elaborate the concept with a single table.
Creating ASP.NET Core Web API
Now let’s go ahead and start visual studio.
Create a new project and choose ASP.NET Core Web API
Give the project a name, like MoviesApi
Visual studio will show you the project template that you want to choose, for this tutorial we will be using the API template
Click Create, then wait until visual studio finishes project initialization.
Let’s do a quick test by running the template API locally, press F5 or click on Run button to start and host the API under your local IIS Express. You might get a message about trusting the self-signed SSL certificate, just press Yes.
After that, your default browser will start and you should see a swagger documentation for your newly created APIs (added by the API Template when we created the new ASP.NET Core Web API Project) hosted under localhost ( IIS Express )
Now this is great, with a few clicks, we have an up and running IIS Express hosting your ASP.NET Core Web API.
In the following section of the tutorial, we will prepare a new controller that will represent the movies endpoint that will allow GET, POST Operations to be done on our SQL Express Database through the Entity Framework Core.
Referencing Entity Framework Core and SqlServer Nuget Packages
To be able to use EF Core and connect to SqlServer, we will need to include 2 nuget packages:
From the solution explorer, right click on your project and choose manage nuget Packages
Go to browse tab, and type in ‘EntityFrameworkCore’ , then install the first item ‘Microsoft.EntityFrameworkCore’ continue with the installation process.
Then search for EntityFrameworkCore.SqlServer and install it, you will need it to configure your Entity Framework Core DbContext to connect to SqlServer Express:
Now let’s create our data model.
Add the below class to your project, in order to have a more organized API folder structure, we need to create a new folder ‘Models’, this will hold all your data models. Now, create a new class, give it a name ‘Movie’.
Now inside this class, we will add the properties that will match and map to the table columns structure that we created in our SQL Express database previously in this tutorial, we will use this model as the data entity that will map to the database table to do all the database operations from our ASP.NET core web API.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
using System;
namespace MoviesApi.Models
{
public class Movie
{
public int Id { get; set; }
public string Name { get; set; }
public string Genre { get; set; }
public string Duration { get; set; }
public DateTime ReleaseDate { get; set; }
}
}
|
Create the EntityFramework Core DbContext
To be able to connect to SQL Server Express using Entity Framework Core, we need to have a class that would inherit the EF Core DbContext class and hold the DbSets of tables and any configurations that might come along the mapping between the models and the actual Database tables, so to do this we need to create a new class under the Models folder, and name it MoviesDbContext
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
using Microsoft.EntityFrameworkCore;
namespace MoviesApi.Models
{
public class MoviesDbContext : DbContext
{
public DbSet<Movie> Movies { get; set; }
public MoviesDbContext(DbContextOptions<MoviesDbContext> options) : base(options)
{
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Movie>().ToTable("Movie");
}
}
}
|
To be able to use the MoviesDbContext directly in your controllers and also give it the required connection string to do the connection, you will need to add it in the service collection of your ASP.NET Core Project
So, open your startup.cs file, make sure to include the namespaces of your models and EntityFrameworkCore
|
1
2
|
using MoviesApi.Models;
using Microsoft.EntityFrameworkCore;
|
then in the ConfigureServices method, you will need to add
|
1 |
services.AddDbContext<MoviesDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("MoviesDbConnectionString")));
|
Currently, we don’t have such connection string ‘MoviesDbConnectionString’ in our configuration file, so in order to add it in ASP.NET Core Web Project, you need to open the appsettings.json file, and include the new connection string section:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
{
"ConnectionStrings": {
"MoviesDbConnectionString": "Server=Home\\SQLEXPRESS;Database=MoviesDb;Trusted_Connection=True;MultipleActiveResultSets=true"
},
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*"
}
|
As you can notice above, the server name is the name of the database host, which is currently an SQL Express instance residing on the local machine, hence the name Home\SQLEXPRESS
And the database name is MoviesDb, the one that we created early in this tutorial
Preparing the Controller
Note: Just before creating our new controller, we just need to remove the template generated controller and any dependent class just to clean up the project and keep it in context of what we are building now.
Now from the Controller folder, right click and choose add then controller, from the selection window, there are multiple options that you can select, for this tutorial I would go with selecting the API Controller with actions, using Entity Framework.
once you choose this option, you will get another dialog that will ask you to choose your data model, DbContext class, and controller name.
See the below for more details:
Once you click add, you will notice that you have a new controller with name MoviesController and inside it you will have the controller with all the main REST functions (GET, POST, PUT, DELETE) each mapped with the relevant CRUD operations (get, save, update, remove) using the entity framework core MoviesDbContext that we have added, all mapped using our Movie data model.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
|
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using MoviesApi.Models;
namespace MoviesApi.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class MoviesController : ControllerBase
{
private readonly MoviesDbContext _context;
public MoviesController(MoviesDbContext context)
{
_context = context;
}
// GET: api/Movies
[HttpGet]
public async Task<ActionResult<IEnumerable<Movie>>> GetMovies()
{
return await _context.Movies.ToListAsync();
}
// GET: api/Movies/5
[HttpGet("{id}")]
public async Task<ActionResult<Movie>> GetMovie(int id)
{
var movie = await _context.Movies.FindAsync(id);
if (movie == null)
{
return NotFound();
}
return movie;
}
// PUT: api/Movies/5
// To protect from overposting attacks, enable the specific properties you want to bind to, for
// more details, see https://go.microsoft.com/fwlink/?linkid=2123754.
[HttpPut("{id}")]
public async Task<IActionResult> PutMovie(int id, Movie movie)
{
if (id != movie.Id)
{
return BadRequest();
}
_context.Entry(movie).State = EntityState.Modified;
try
{
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!MovieExists(id))
{
return NotFound();
}
else
{
throw;
}
}
return NoContent();
}
// POST: api/Movies
// To protect from overposting attacks, enable the specific properties you want to bind to, for
// more details, see https://go.microsoft.com/fwlink/?linkid=2123754.
[HttpPost]
public async Task<ActionResult<Movie>> PostMovie(Movie movie)
{
_context.Movies.Add(movie);
await _context.SaveChangesAsync();
return CreatedAtAction("GetMovie", new { id = movie.Id }, movie);
}
// DELETE: api/Movies/5
[HttpDelete("{id}")]
public async Task<ActionResult<Movie>> DeleteMovie(int id)
{
var movie = await _context.Movies.FindAsync(id);
if (movie == null)
{
return NotFound();
}
_context.Movies.Remove(movie);
await _context.SaveChangesAsync();
return movie;
}
private bool MovieExists(int id)
{
return _context.Movies.Any(e => e.Id == id);
}
}
}
|
You will notice that you can now inject MoviesDbContext through the controller’s constructor since you’ve added previously in your ASP.NET Core application’s startup configuration file. Correct, this might not be the best practice to have your database model directly used by your controller, instead you should have a separate repository class that abstracts the CRUD operations and another services layer that wraps the business logic and calls to your repository class, so that the controller would only integrate with the service class without knowing what will happen in it or in the repository class.
But for the sake of this tutorial, we just want to showcase how we can easily build RESTful APIs using ASP.NET Core and connect it to an SQL express through Entity Framework Core in no time.
Our Movies API is now ready. Save your changes and press F5 and wait for your default browser to open and show the swagger UI documentation, as the below:
Now this is an initial run test, it works!
Testing the APIs on Postman
In order for us to better test the APIs and simulate a real client, we need to use a tool (postman) to help us verify that each RESTful method is working properly. So again, make sure you downloaded and installed Postman on your machine.
Of course you can always test through Swagger UI that was shown previously, however I prefer Postman since it has a comprehensive and advanced testing and simulation for client’s communication with the APIs.
So, let’s open postman and start our simulation.
Let’s first create a postman collection so that we save our API requests. This can come very handy whenever you are doing regular changes on your APIs and you want to test and verify your work, it would be absolutely easy to come back to postman and verify your changes there.
So in the left menu, click Create a Collection, give it a name ‘MoviesApi’
You should see this
Now click Add requests, now we want our first request to do a GET operation on all the movies, so let’s name it Get Movies then click save
So this is our first API request, click on it, postman will open for you a new panel that doesn’t have any details inside, this is where we will have to put the API request details include address URL, Headers, Request Body …etc.
For our first request, since it is a GET operation, we just need to put the address URL of our movies API like we saw in the browser earlier.
https://localhost:5001/api/movies
Now press send, you will get an error saying ‘Unable to verify the first certificate’ ,
This is happening because if you notice the API URL is https, however postman needs to verify the https certificate and since it is installed on your local machine, it cannot find a matching certificate there, so the easy way here to test it is to Disable SSL Verification in postman, by setting the SSL certification verification switch to OFF, in the Settings dialog access through the File -> Settings in the top-left menu, as the below:
Now, once you disabled the SSL verification, postman will re-send your request, and now you should get a 200 ok response with all the movies in json format.
Perfect, this is our first request, press Ctrl+s to save your request update on our MoviesApi Collection
Now let’s add the second request, it would be a Get request as well, but this time for a single Id. If you check the code again
|
1
2
3
4
5
6
7
8
9
10
|
[HttpGet("{id}")]
public async Task<ActionResult<Movie>> GetMovie(int id)
{
var movie = await _context.Movies.FindAsync(id);
if (movie == null)
{
return NotFound();
}
return movie;
}
|
See, in this method, we have an attribute template defined {id} in the HttpGet attribute, this means that this action will take the id from the Url and pass it as an argument in the method’s parameter (int id)
Then it will use this id to select from the database where the id = the id value as taken from the method’s parameter.
So going back to postman, hover the mouse on the collection folder, you will see 3 dots button … click on it and choose add request.
Let’s give it a name, Get Movie By Id.
This request would look similar to the before, we only need to append a slash id to it, like the below:
https://localhost:5001/api/movies/3
this will get the movie with id 3, see the below:
Great, you now have 2 requests.
Let’s add a POST request now, this will insert a new movie into our database.
Add a new request the same way you did the one before, name it Add Movie
Now you will notice that it was created as GET for you, we want to change this to POST
So click on the Add Movie request, then in the center panel, locate the GET dropdown, and change it to POST
And now let’s put our address Url as
https://localhost:5001/api/movies
but since this is a POST request, we must add a body to it in order for it to do the add movie operation that we want, so click on Body tab
then choose raw option, then choose JSON (in the newly popped-up dropdown text in the right)
this time we will add a fantasy/adventure movie with the below details:
|
1
2
3
4
5
6
|
{
"name": "The Lord of the Rings: The Fellowship of the Ring",
"genre": "Fantasy/Adventure",
"duration": "3h 28 mins",
"releaseDate": "2001-12-19T00:00:00"
}
|
Notice that we are not sending an id in the request, because our database is generating the id for us (auto-increment)
Now hit send, you will get a 201 Created http code with the below response:
See the database has generated a new id and assigned it to the new row, and then our API returned the saved row, this is because in the code we have a logic that returns the saved row once it is created.
Let’s see the code again:
|
1
2
3
4
5
6
7
|
[HttpPost]
public async Task<ActionResult<Movie>> PostMovie(Movie movie)
{
_context.Movies.Add(movie);
await _context.SaveChangesAsync();
return CreatedAtAction("GetMovie", new { id = movie.Id }, movie);
}
|
Now, for PUT and DELETE I will leave it for you as an exercise to add them on your postman collection and test them, just remember that PUT is used to update a field or more for a given id in the database and delete is used to physically remove a row for a given id in the database.
Please continue and let me know how it goes with you.
You can find the full updated source code in this Github repository
I have another tutorial that explains how you can Deploy ASP.NET Core Web Api on IIS. Check it as well and let me know if it helps you.
Also check this helpful article by David Grace about how to Create CRUD API Endpoint with ASP.NET Core and Entity Framework
Another great tutorial by freecodespot.com related to Entity Framework Core about how to Create a model from an existing database in Entity Framework Core
For more tutorials related to ASP.NET Core Web API, updated to .NET 6, see the below:
- File Upload with Data using ASP.NET Core Web API
- Logging with Serilog in ASP.NET Core Web API
- Secure Angular Site using JWT Authentication with ASP.NET Core Web API
- Localization in ASP.NET Core Web API
- Google reCAPTCHA v3 Server Verification in ASP.NET Core Web API
- Apply JWT Access Tokens and Refresh Tokens in ASP.NET Core Web API 6
Summary
I hope you have enjoyed this tutorial, it was pretty long I know, but the topic is huge and this is only the tip of the iceberg, if you want more resources on this material, you can see the below resources:
Bonus
Thank you for taking your time to read this tutorial, please enjoy this masterpiece from the baroque era
Is there a way you may share the actual project?
Sure. You can find the full source code in this Github repository
Many thanks for this well explained, no nonsense tutorial which got me off the ground with ASP.Net Core (Web Api for SQLServer) without too much pain, and it was just what I needed. Sharing a bit of music is a nice bonus too!
Very nice article with very clear steps, I personally liked it. Really appreciated.
Congratulations on the didactics of explaining in a practical (linear) way and simple to understand.
Music is something that touches our soul, it showed great taste in the choice.
Hi, i’m unable to add a controller, please find error message below
There was an error running the selected code generator: ‘could not load file or assembly ‘Microsoft.Bci.Asyncinterface, Version-1.0.0.0, Culture=neutral, PublicKeytoken=cc7b13ffcd2ddd51’ The system cannot find the file path specified
This is a dependency for Entity Framework Core 3.x , I think there is something wrong with your NuGet packages, can you try to do dotnet restore nuget in Package Manager Console and if it didnt’t work, try reinstalling EntityFramework Core package, and then restart visual studio and rebuild the solution
Nice and easy to understand tuto. However, when I added the controller, it added sqlite nuget package by itself, any idea why? Thanks
Thanks for your comment, that is really strange, can you please tell me exactly how did you add the controller, which option did you select?
Can you please let me know why when typing “https://localhost:44373/api/movies” Getmovies method gets executed, what is the reason behind and how applications know? Also when I added the controller as you indicated, it added index, details,….. and not REST functions (GET, POST, PUT, DELETE) , thanks for your help
Thanks for your comment. Whenever you call the endpoint /movies the ASP.NET Core will know, by design and convention, that you are requesting the GET method of the MoviesController so your request will be routed to the getMovies method. Also, usually the default or the first method used in browsers or http clients is GET unless otherwise specified by you or by the calling service. About adding the controller, I am not totally sure why that happened, can you please tell me, in steps, how exactly you added the controller to regenerate your issue at my side and check.
Thank. I found error in OnModelCreating when you have not PK on table.
This Error : Microsoft.EntityFrameworkCore.Internal.EntityFinder.FindTracked(object[] keyValues, out IReadOnlyList keyProperties)
I can find solution fix in web >> https://stackoverflow.com/questions/52441156/entity-type-identityusertokenstring-is-defined-with-a-single-key-property-b
Thank. I found error in OnModelCreating when you have not PK on table.
I can find solution serch for word entity-type-identityusertokenstring-is-defined-with-a-single-key-property
enjoy!
Great tutorial, ever
This is a unique and very useful tutorial. Everything is working perfectly. we are expecting more similar tutorial from you.
I would like to get a help from your side. In this article you have mentioned that
“this might not be the best practice to have your database model directly used by your controller, instead you should have a separate repository class that abstracts the CRUD operations and another services layer that wraps the business logic and calls to your repository class, so that the controller would only integrate with the service class without knowing what will happen in it or in the repository class.”
Could you share a similar implementation details ?. That will be very useful for us.
Thank you