Set up a web API with SQL database with minimum effort using .NET Core and Entity Framework

Sometimes when starting work on a new project, a lot of time can be spent setting up the project, adding packages and dependencies, and setting up the database. And that’s if you know what the database is going to look like.

Sure it’s not too difficult to set up the SQL database with tables with relationships, foreign keys, cascading operations, and all the rest of it. But it still takes time, which can be greatly reduced.

This article will go over the basics of setting up a Web API with SQL database without writing much code and no SQL at all!

This follows the ‘code first’ approach, where you define your models and relationships, and then run a database migration that will create and update the database. You could also choose to create the database first, then use Entity Framework to create your models.

Get started

For this article, I’m using .NET Core 2.1 and Visual Studio 15.7, so make sure you’re up-to-date. First off, create a new .NET Core Web Application project in Visual Studio and select API as the project type. This article still applies to all .Net Core project types.

Create your models

Add a folder for Models if there isn’t one already and add a new Class for each of your models, and one for your database context. You should have something like this:

Add relationships to your models

Adding relationships to your models lets Entity Framework retrieve related data using very little code using navigation properties. In my example I’m creating a one-to-many relationship where a Country can have many States, but a State can only have one Country. This relationship can be created with just three lines of code.

Firstly, in Country.cs, which is the principal entity, we want a collection navigation property that will return a list of States added to the Country:

Then, in States.cs, which is the dependent entity, we add the Country foreign key, and the Country reference navigation property:

Retrieving a model with navigation properties then becomes very simple and could look something like:

_context.Countries.Include(c => c.States).SingleOrDefault (c => c.CountryId == id);

Register the database context with dependency injection

Services (such as ApiContext) are registered with dependency injection during application startup. A dependency is any object that another object requires, so since our API controllers will need to access the ApiContext, it’s registered as a service.

In Startup.cs, add using statement “using Microsoft.EntityFrameworkCore;”, and one for your models. Then add the following to the ConfigureServices method:

services.AddDbContext(options =>
options.UseSqlServer(Configuration.GetConnectionString("defaultConnection")));

The code above is getting the SQL database connection string from appsettings.json. See connection strings for more information.

Your Startup.cs file should now look like this:

Now create the database

So far, we’ve created our database models with relationships and registered the database context. Now we can go ahead and create our database! Following this article’s theme of minimum effort, we’ll do it with just two commands.
Open the Package Manager Console (Tools > NuGet Package Manager > Package Manager Console) and run the following commands:

PM> Add-Migration InitialCreate
PM> Update-Database

The Add-Migration command creates a migration file in your project. This migration file includes the initial set of tables as defined in your models. The Update-Database command will create the database and applies the new migration to it.

Create a controller

Right-click on the Controllers folder and select Add > Controller. Select API Controller with actions, using Entity Framework.

In the next dialog window, select your model from the Model class list, then select your database context from the Data context class list. The scaffolding engine will create the API Controller file with all the CRUD endpoints!

Add some swag to the API

Swagger adds useful documentation and help pages to your web API, as well as the ability to test the endpoints and view model information.

To get started, add the Swashbuckle package to your project:

PM> Install-Package Swashbuckle.AspNetCore

Now add the Swagger generator and middleware for serving the generated JSON document and UI to Startup.cs:

Add using statement “using Swashbuckle.AspNetCore.Swagger;”; then add to the ConfigureServices method:

services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new Info { Title = "My API", Version = "v1" });
});

and add to the Configure method:

app.UseSwagger();
app.UseSwaggerUI(c =>
{
c.SwaggerEndpoint("/swagger/v1/swagger.json", "My API V1");
});

Run the application

If all went to plan, you should now have a working Web API with SQL database. Run the application and navigate to /swagger.

You should see the Swagger UI and all your API endpoints from your controllers. You can test the endpoints by clicking the “Try it out” buttons or see example responses the would get returned.

Updating the database

During development your models might change. When you make changes to your models, the database will need to get updated to reflect these changes. Again, Entity Framework makes these updates very easy. Just like when you created the initial migration, the same commands are used to apply updates. Create a new migration, then update the database:

PM> Add-Migration MyModelChange
PM> Update-Database

Again, Add-Migration scaffolds any changes to the models that need to be applied to the database. Every time you make changes to the models, you must also run the migrations to update the database.

You’ll need to be careful with your database migrations because it’s very easy to lose data from your database. If you delete a property from a model, or a model entirely, it will get deleted from the database including any data. You’ll get a warning if there is a risk of losing data, so you can decide to run the migration, or you could modify the migration file to keep the delete properties in the database.

Where to now?

Now you should have a working Web API with a Swagger json definition and UI. This was achieved by writing only a handful of lines of code, and no SQL at all. This is just a starting point to demonstrate how much can be achieved with very little effort.