[WebApi Tutorial] Library Check – Getting Data From the Database

Today’s header image was created by Freddie Marriage, the original source for the image is available here

Stop!

Collaborate and listen?

This is the second in a series of blog posts related to building a WebApi application.

If you haven’t read the first part, I’d recommend starting there.

Continue

If you remember, last time we built a basic WebApi application, added Entity Framework Core, Added a DbContext, and a service.

What We’ll Be Doing

In this post we’ll:

• Add an Initial Database Migration (to set up the Book table)
• Add some Seed data
• Build a Service Method that will use our database context to return Book records as entities
• Call the Service Method from a controller that we’ll build
• Send the Book JSON data as a response to a GET request

As with the previous part in this series, I’ve already uploaded all of the code that we’re about to write to a GitHub repository. You can head over there if you want to take a sneak peak at what we’re going to create in this tutorial.

Before We Start

I’ve had to think long and hard about this. In the previous part of this series, I’d shown you how to perform certain steps with Visual Studio 2015. Since I wrote that part (and at the time of writing), Visual Studio’s support for .NET Core has entered a state of flux. Visual Studio 2015 (with Update 3 installed) only partially supports the version of the SDK we’re going to use (project.json), and Visual Studio 2017 supports the next version (csproj/msbuild).

This has left our project in a bit of a strange place, it’s supported by VS 2015 (with update 3), but after VS 2017 is released (which Damien Edwards has said is “some time in 2017”), our project won’t be supported by VS 2015 or 2017 (without having to change some dependencies and the SDK version).

This was announced by Damien Edwards during the APT.NET Community Standup on January 24th. If you want to watch that particular stand up video, you can do so here.

 

The above YouTube video should start at 44:24 (which is when Damien mentions the upcoming change in tooling support). If it doesn’t you’ll have to scrub to that time stamp to hear what Damien says about it.

As Visual Studio 2015 will soon no longer be supported (even though a date has not been given), this tutorial (and any posts that I create for a short while after it) will not have Visual Studio specific steps in them. This is specifically because I could end up sharing code and commands or steps which will only work for the short term, and may leave some readers short when Visual Studio 2017 is finally released.

I’d rather not send readers in the direction of VS 2017 because (at the time of writing this post), it is still in RC (Release Candidate) and has more than a few breaking changes and bugs which need to be fixed before it can be Released to Manufacturers (RTM – which means “put out as a proper release for devs”).

Running the Service

The first thing I would recommend that you do is make sure that the code from last time still runs. So cd into the src directory and issue the run command:

Then head over to ‘http://localhost:5000/api/values’ in your browser:

If something doesn’t go quite right, then take a quick look at the the first part of this series, or clone the code from the GitHub repository for this part of the series.

Creating the Database

We’ll create the database (and apply any changes that we make to data model) using Entity Framework Migrations. EF Migrations are C# files which describe any changes that we have made to the domain model. These are interpreted by EF, converted into SQL and sent, as a query to the database, to update the schema.

Migrations have both and UP and DOWN methods, which contain the steps required to upgrade the database to the new model and the steps required to downgrade the database from the new model (respectively). It does this by keeping a snapshot of the model for the database and running a comparison against the changes that you have created to the local model – each time you make a substantial change you’ll need to create a migration.

In EF 6, this was done by taking a snapshot of the database when you ran the upgrade command, in EF Core a snapshot of your database is maintained to make this operation quicker.

Creating the Initial Migration

In the terminal, issue the following command:

This will build the application, create a sub-directory (within the src directory) called ‘Migrations’ and add a group of files with names ending in ‘InitialMigration.cs’.

If the build action fails, the migrations directory (and the files within it) will not be created.

The response that you get (in the terminal) should be similar to this:

Once that has completed, you should see the new directory and files appear.

Initial Migration Added

The filenames for Migrations take the format of {DateTime of when it was created}_{MigrationName}.cs, so they should be easy to spot. the Designer file is a copy of the migration file, but in FluentApi

If you take a look at the migration file (in my screenshot it’s called ‘20170201222814_InitialMigration.cs’), it should look like this:

You don’t need to be able to read these files, but it will definitely help to familiarise yourself with what’s going on.

You want to know what EF Core is doing to your database, right?

Let’s take a look at the migration file in greater detail, first the Up method (for updating the database schema):

This Up method looks complex, but it really isn’t. It’s one call to CreateTable, which will create our Books table. The column names are passed in, with their properties (their types and whether they can be nullable or not), and a constraint is placed on the Id property (making it the primary key).

And now the Down method (for reverting the upgrade, or downgrading the database schema):

This is one of the simplest down methods that EF Core can create on a new migration: the drop table migration. EF Core has done this because our Up migration created the table, so to reverse that (and take the database schema back to how it was before we ran the initial migration), it has to drop the table.

A Note on IDs

By default EF Core will look at an entity class and find the first property that is either named after the class plus the word Id (BookId for example) or is called Id.

This is a case insensitive search, and will prefer the class name plus Id format

If EF Core can find a property which matches, then it will assume that the property it has found is the primary key. If it can’t, then it will raise an error telling you that it doesn’t know what the primary key should be.

Seeding The Database Part 1

Seeding a database is when we add an initial set of data to it, so that it isn’t created empty. There are times when you might want to create the database with some seed data (for instance to ensure that it has some test data, or when you have a read-only view of the data – like our application) and sometimes you don’t what this to happen (perhaps in a production environment where the user might be supplying the valid data via the application or interacting directly with the database).

Let’s take care of seeding the database before we create it, that way it’ll be seeded as soon as it’s created.

A Note on Licensing

Before we continue, all of the code in this tutorial is MIT licensed. This means that you can, basically, do what you want with it but you cannot hold me liable for any issues that running the code on your machine could cause.

You can read an excellent summary of the MIT license at TL;DR Legal

However, I’m going to source some of the seed data from the L-Space wiki. L-Space’s data is protected by a Creative Commons Share alike 3.0 license. This means that I’m free to use the data in this tutorial, but that I must give attribution and share the data in kind.

You can read an excellent summary of this license at TL;DR Legal

Here’s where it gets a little wobbly because I’ve taken the data from one format (HTML encoded) and transposed it into another (JSON). I’m going to link directly to the page which contains the original data, and you will be able to see that I have not altered the data in any form (other than the formatting).

Creating The Seeder

The first thing we need to do is to create a helper class for our dbContext. In the DatabaseContexts directory, create a file called dwExtension.cs and paste the following content into it:

There’s a lot going on here, so let’s take the file in chunks.

Here we’re creating a method to check whether all of the migrations have been applied. This will be useful in a moment (when we need to decide whether add the seed data or not).

Then we’re creating a method to ensure that we have our seed data inserted into the DbSet. The first thing we do is check whether all of the migrations have been applied, then we check whether there are entries in our DbSet and add them if not (via the GenerateAllBookEntities method).

Here we’re creating a single Book entity and returning it in a list. We can extend this later, by adding more entries. Each of these entires will then be added to the DbSet (in the EnsureSeedData method, which calls this method).

Source of the Data

Here is where I need to take a break and point out where the data has come from.

The data in the following code block:

is sourced directly from the following page on the L-Space wiki: https://wiki.lspace.org/mediawiki/Book:The_Colour_of_Magic

Calling the Seeder

We want the seeder to be called as soon as possible during startup, so we’ll add a call to it in the startup.cs class. Find the Configure method in the startup.cs class and paste the following into it:

The highlighted lines are the ones that you want to paste into the Configure method.

Here we’re getting a handle to the ServiceScope (which will give us access to our services), then getting a handle on our DbContext. Once we have a handle to our DbContext, we’re automatically applying migrations

Which is something that you might not want to do in production, but it’s fine for us here

then we’re calling the seed method.

Updating (Creating) the Database

Head back to the terminal and issue the following command:

EF Core will build the application, then apply the Up migration to the database. In order to do this, it will have to create the database, then apply it.

You should get output similar to this:

And if you take a look in the bin directory you should see the database has been written to disk:

Seeding the Database Part 2

If you open the database and view it’s contents

I use DB Browser for SQLite, because it’s free

you’ll see that our Books table has been created.

However if you browse the data, you’ll see that we have no Book records yet.

This is because we haven’t run the seed method yet. To run the seed method, we need to run the application, so let’s do that now. Issue the run command:

In the terminal window, you’ll see the SQL commands that EF Core is running to seed your database.

Here’s a reduced version of the output that I received (I’ve taken a lot out of here, because the output is massive):

Heading back to our DB Browser for SQLite, I can see our first Book entry

Adding a Controller

Having a service that we can talk to is great, but it’s pretty useless unless we actually talk to it. So the next thing we’re going to do is create a controller which will deal with our HTTP GET requests. We’ll only need the one method (for now), it will search the database for a given record and return it to us in JSON.

In the Controllers directory add a file called “BooksController.cs” and paste the following code into it:

We’ve seen most of this before (in our ValuesController from last time), so we’ll just dissect the newer stuff:

The first thing we’re doing here is to search for a book with the supplied ordinal. If one can’t be found we return an error response, otherwise we return a JSON version of the Book record.

If you navigate to “/Books/Get/1” in your browser, you should receive a response like this:

Having the Controller do the conversion to a View Model isn’t best practise here, but we’ll cover a way to convert them next time.

Conclusion

We’ve created a migration, a Seeder method, created the database and a controller method which interacts with our service.

Next time, we’ll cover converting database models into View Models (POCOs that represent a database model, but can be used in any views or responses), we’ll add the Character entity, we’ll add a Character service and a controller, and we’ll look into many-to-many relationships in EF Core.