Code snippets for the blog post /2017/02/09/webapi-tutorial-library-check-getting-data-from-the-database/)

_InitialMigration.cs

using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore.Migrations;

namespace webApiTutorial.Migrations
{
  public partial class InitialMigration : Migration
  {
    protected override void Up(MigrationBuilder migrationBuilder)
    {
      migrationBuilder.CreateTable(
        name: "Books",
        columns: table => new
        {
          BookId = table.Column<int>(nullable: false)
            .Annotation("Sqlite:Autoincrement", true),
          BookDescription = table.Column<string>(nullable: true),
          BookIsbn10 = table.Column<string>(nullable: true),
          BookIsbn13 = table.Column<string>(nullable: true),
          BookName = table.Column<string>(nullable: true),
          BookOrdinal = table.Column<int>(nullable: false)
        },
        constraints: table =>
        {
          table.PrimaryKey("PK_Books", x => x.BookId);
        });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
      migrationBuilder.DropTable(name: "Books");
    }
  }
}

BooksController.cs

using webApiTutorial.Services;
using Microsoft.AspNetCore.Mvc;

namespace webApiTutorial.Controllers
{
  [Route("/[controller]")]
  public class BooksController : Controller
  {
    private IBookService _bookService;

    public BooksController(IBookService bookService)
    {
      _bookService = bookService;
    }

    // Get/5
    [HttpGet("Get/{id}")]
    public JsonResult GetByOrdinal(int id)
    {
      var book = _bookService.FindByOrdinal(id);
      if (book == null)
      {
        return ErrorResponse("Not found");
      }

      return Json(new {
        Success = false,
        Result = new {
          id = book.BookId,
          ordinal = book.BookOrdinal,
          name = book.BookName,
          isbn10 = book.BookIsbn10,
          isbn13 = book.BookIsbn13,
          description = book.BookDescription
        }
      });
    }

    protected JsonResult ErrorResponse(string message = "Not Found")
    {
        return Json (new {
            Success = false,
            Result = message
        });
    }
  }
}

dwExtension.cs

using webApiTutorial.Models;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;

namespace webApiTutorial.DatabaseContexts
{
  public static class DatabaseContextExtentsions
  {
    public static bool AllMigrationsApplied(this DwContext context)
    {
      var applied = context.GetService<IHistoryRepository>()
            .GetAppliedMigrations()
            .Select(m => m.MigrationId);

      var total = context.GetService<IMigrationsAssembly>()
            .Migrations
            .Select(m => m.Key);

      return !total.Except(applied).Any();
    }

    public static void EnsureSeedData(this DwContext context)
    {
      if (context.AllMigrationsApplied())
      {
        if(!context.Books.Any())
        {
          context.Books.AddRange(GenerateAllBookEntiies());
          context.SaveChanges();
        }
      }
    }

    private static List<Book> GenerateAllBookEntiies()
    {
      return new List<Book>(){
        new Book {
          BookName = "The Colour of Magic",
          BookOrdinal = 1,
          BookIsbn10 = "086140324X",
          BookIsbn13 = "9780552138932",
          BookDescription = "On a world supported on the back of a giant turtle (sex unknown), a gleeful, explosive, wickedly eccentric expedition sets out. There's an avaricious but inept wizard, a naive tourist whose luggage moves on hundreds of dear little legs, dragons who only exist if you believe in them, and of course THE EDGE of the planet ...",
        }
      };
    }
  }
}

powershell.sh

// for VS Powershell commands

shell.sh

dotnet run
dotnet ef migrations add InitialMigration
Project src (.NETCoreApp,Version=v1.1) was previously compiled. Skipping compilation.
Done. To undo this action, use 'dotnet ef migrations remove'
dotnet ef database update
Project src (.NETCoreApp,Version=v1.1) was previously compiled. Skipping compilation.
Done.
info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      PRAGMA foreign_keys=ON;
info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT CASE
          WHEN EXISTS (
              SELECT 1
              FROM "Books" AS "b")
          THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
      END
info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      PRAGMA foreign_keys=ON;
info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
      Executed DbCommand (10ms) [Parameters=[@p0='?', @p1='?', @p2='?', @p3='?', @p4='?'], CommandType='Text', CommandTimeout='30']
      INSERT INTO "Books" ("BookDescription", "BookIsbn10", "BookIsbn13", "BookName", "BookOrdinal")
      VALUES (@p0, @p1, @p2, @p3, @p4);
      SELECT "BookId"
      FROM "Books"
      WHERE changes() = 1 AND "BookId" = last_insert_rowid();

startup.cs

public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
{
  loggerFactory.AddConsole(Configuration.GetSection("Logging"));
  loggerFactory.AddDebug();

  app.UseMvc();

  // seed the database using an extension method
  using (var serviceScope = app.ApplicationServices.GetRequiredService<IServiceScopeFactory>()
            .CreateScope())
  {
    var context = serviceScope.ServiceProvider.GetService<DwContext>();       
    context.Database.Migrate();
    context.EnsureSeedData();
  }
}