ASP.NET Core MVC + Web API app with EF Core 1.0 & SQLite

This tutorial uses Visual Studio 2015 and ASP.NET Core 1.0.

My intention is to give you a practical introduction into developing ASP.NET Core 1.0 MVC and Web API apps with the SQLite database as an alternative to traditional SQL Server.

A useful utility that comes in handy when working with the SQLite database is SQLiteStudio. Download SQLiteStudio from: http://sqlitestudio.pl/?act=download. Extract the ZIP file and place contents in a separate folder. Run SQLiteStudio.exe.

We will build an ASP.NET Core 1.0 app that uses the following Student entity:

ASP.NET Core 1.0 MVC project

Create a new ASP.NET Core app in Visual Studio 2015:

• File >> New >> Project
• Templates >> Visual C# >> Web

• Select “ASP.NET Web Application (.NET Core)”, name the project SQLiteWeb then click on OK.

• Select “Web Application”, uncheck “Host in the cloud”, then click on OK.

Open the global.json and note the runtime version that will be used by the application:

{
  "projects": [ "src", "test" ],
  "sdk": {
    "version": "1.0.0-preview2-003131"
  }
}

The above indicates that the version of the runtime that is being used is 1.0.0-preview2-003131.
Dependencies are added to the project.json file. Open this file and have a peek at the dependencies section:

"dependencies": {
  "Microsoft.NETCore.App": {
    "version": "1.0.1",
    "type": "platform"
  },
  "Microsoft.AspNetCore.Diagnostics": "1.0.0",
  "Microsoft.AspNetCore.Mvc": "1.0.1",
  "Microsoft.AspNetCore.Razor.Tools": {
    "version": "1.0.0-preview2-final",
    "type": "build"
  },
  "Microsoft.AspNetCore.Routing": "1.0.1",
  "Microsoft.AspNetCore.Server.IISIntegration": "1.0.0",
  "Microsoft.AspNetCore.Server.Kestrel": "1.0.1",
  "Microsoft.AspNetCore.StaticFiles": "1.0.0",
  "Microsoft.Extensions.Configuration.EnvironmentVariables": "1.0.0",
  "Microsoft.Extensions.Configuration.Json": "1.0.0",
  "Microsoft.Extensions.Logging": "1.0.0",
  "Microsoft.Extensions.Logging.Console": "1.0.0",
  "Microsoft.Extensions.Logging.Debug": "1.0.0",
  "Microsoft.Extensions.Options.ConfigurationExtensions": "1.0.0",
  "Microsoft.VisualStudio.Web.BrowserLink.Loader": "14.0.0"
},

Build the application then run it by hitting CTRL F5 to see what it looks like in your browser.



This runs your application in IIS Express. Close your browser.

When working with ASP.NET Core 1.0, you will need to go to the command-line interface frequently. Add a command-prompt extension to make it easier. Click on Tools >> Extensions and Updates…



Find an extension named “Open Command Line” as shown below.



If you have not installed it already, install the above extension.

In solution explorer, right-click on the SQLiteWeb node the choose “Open Command Line” >> “Default (cmd)”



This opens a regular operating system terminal window. Try out some important .NET Core 1.0 commands.

1. dotnet --help – this gives you a list of common commands
2. dotnet restore – restore dependencies specified in the .NET project
3. dotnet build - Builds a .NET project
4. dotnet run --help – provides help information about the run command
5. dotnet new --type --help – shows the types of templates that can be scaffolded. At the time of writing these are: Console, Web, Lib, xunittest.
6. dotnet new --type web – scaffolds a web template in the current directory

Execute the following command in order to run your web application using another cross-platform web server called Kestrel on port 5000, instead of IIS Express.

dotnet run

Note the following message:

Project SQLiteWeb (.NETCoreApp,Version=v1.0) was previously compiled. Skipping compilation.
Hosting environment: Production
Content root path: D:\scrap\_4976\SQLiteWeb\src\SQLiteWeb
Now listening on: http://localhost:5000
Application started. Press Ctrl+C to shut down.

Point your browser to http://localhost:5000. You will see the exact same page as before.
Close your browser, close the command-line terminal window and return to Visual Studio 2015.
We will need to add some dependencies in order to use SQLite with Entity Framework Core . Add these dependencies to the project.json file:

"Microsoft.EntityFrameworkCore.Sqlite": "1.0.1",
"Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final"
Add the following to the tools section in the project.json file:

"Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final"

Class Library Project

It is good practice to place all your data models inside a Models folder. Therefore, add a folder called Models. Inside of the Models folder, add a class file named Student.cs. Use the following code for the class file:

public class Student {
  public int Id { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
  public string School { get; set; }
  public DateTime StartDate { get; set; }
}

Also, to the Models folder, add another class file named SchoolContext.cs with the following class definition code:

public class SchoolContext : DbContext {
  public SchoolContext(DbContextOptions<SchoolContext> options) :base(options)
        { }
  public DbSet<Student> Students { get; set; }
}

We will need to add a connection string for the SQLite database in the SQLiteWebAPI project:

• Open the appsettings.json file in the SQLiteWebAPI project
• Add the following section after the Logging block:

"Data": {
  "DefaultConnection": {
    "ConnectionString": "Data Source=school-db.sqlite"
  }
}

We will need to add both Entity Framework and SQLite to the project’s Startup.cs class:

• Open the Startup.cs file.

• Add the following code to the ConfigureServices() method just before services.AddMvc();

var connection = Configuration["Data:DefaultConnection:ConnectionString"];
services.AddDbContext<SchoolContext>(options => options.UseSqlite(connection));

The above reads the connection string from the appsettings.json file and passes it on to the SchoolContext class.

Seed Data

Before we carry out code first migrations, let us first create some seed data:

• In the Models folder, create a class named DummyData.cs.
• Add the following Initialize() method code inside the DummyData class:

public static void Initialize(SchoolContext db) {
  if (!db.Students.Any()) {
    db.Students.Add(new Student {
      FirstName = "Bob",
      LastName = "Doe",
      School = "Engineering",
      StartDate = Convert.ToDateTime("2015/09/09")
    });
    db.Students.Add(new Student {
      FirstName = "Ann",
      LastName = "Lee",
      School = "Medicine",
      StartDate = Convert.ToDateTime("2014/09/09")
    });
    db.Students.Add(new Student {
      FirstName = "Sue",
      LastName = "Douglas",
      School = "Pharmacy",
      StartDate = Convert.ToDateTime("2016/01/01")
    });
    db.Students.Add(new Student {
      FirstName = "Tom",
      LastName = "Brown",
      School = "Business",
      StartDate = Convert.ToDateTime("2015/09/09")
    });
    db.Students.Add(new Student {
      FirstName = "Joe",
      LastName = "Mason",
      School = "Health",
      StartDate = Convert.ToDateTime("2015/01/01")
    });
    db.SaveChanges();
  }
}

To generate seed data, we will first inject the dependency “SchoolContext context” into the arguments of the Configure() method in Startup.cs. Next, we can make a call to seed the data at the bottom of the Configure() method with the following statement:

DummyData.Initialize(context);

Migrations

We are now ready to do some migrations:

• Compile your application
• Open a command terminal inside the src\SQLiteWeb folder
• Add a migration to the project with the following ef command:

dotnet ef migrations add FirstMigration

Notice that class files are created in the Migrations folder.

• We will then update the database with the following terminal command:

dotnet ef database update

At this point, there will be a file named school-db.sqlite in the src\SQLiteWeb\bin\Debug\netcoreapp1.0 folder. The data will not have been seeded yet because this happens when the application is actually run. 

Creating an MVC UI

Let us seed the data by running your web application in a browser. You should see the same page as we saw earlier. Let us create a UI so that we can see the seeded data.

• Right-click on the Controllers folder and choose Add >> New Item… >> MVC Controller Class

• Name the class file StudentController.cs.

• Replace the class definition with the following code:

public class StudentController : Controller {
  private SchoolContext _context;
  public StudentController(SchoolContext context) {
      _context = context;
  }
  public IActionResult Index() {
      return View(_context.Students.ToList());
  }
  public IActionResult Create() {
      return View();
  }
  [HttpPost]
  [ValidateAntiForgeryToken]
  public IActionResult Create(Student student) {
      if (ModelState.IsValid) {
          _context.Students.Add(student);
          _context.SaveChanges();
          return RedirectToAction("Index");
      }
      return View(student);
  }
}

You’ll notice that the controller takes a SchoolContext as a constructor parameter. ASP.NET dependency injection will take care of passing an instance of SchoolContext into your controller.

The controller contains an Index action, which displays all blogs in the database, and a Create action, which inserts a new student into the database.

Let us add a view for the UI.

• Create a new folder under Views named Student.
• Right-click on the newly created Student folder and and select Add >> New Item…
• Add an “MVC View Page” item and accept the default name index.cshtml.
• replace the contents of the index.cshtml with the following code:

@model IEnumerable<SQLiteWeb.Models.Student>
@{
  ViewBag.Title = "Students";
}
<h2>@ViewBag.Title</h2>
<p>
  <a asp-controller="Student" asp-action="Create">Create New</a>
</p>
<table class="table">
  <tr>
    <th>Id</th>
    <th>First Name</th>
    <th>Last Name</th>
    <th>School</th>
    <th>Start Date</th>
  </tr>
  @foreach (var item in Model) {
  <tr>
      <td>
        @Html.DisplayFor(modelItem => item.Id)
      </td>
      <td>
        @Html.DisplayFor(modelItem => item.FirstName)
      </td>
      <td>
        @Html.DisplayFor(modelItem => item.LastName)
      </td>
      <td>
        @Html.DisplayFor(modelItem => item.School)
      </td>
      <td>
        @Html.DisplayFor(modelItem => item.StartDate)
      </td>
  </tr>
  }
</table>

• Let us add a link to the Student controller on the main page of our application. Open _Layout.cshtml under Views/Shared.
• Paste the following markup in the navigation section around line 36:

<li><a asp-area="" asp-controller="Student" asp-action="Index">Student</a></li>

• Run the application then click on the Student link. You should see the dummy data that we created.

The “Create New” link is currently broken. We need to develop the UI for adding new students

• Create a “MVC View Page” item create.cshtml under Views/Student.
• Replace the contents of create.cshtml with the following markup:

@model SQLiteWeb.Models.Student
@{
  ViewBag.Title = "New Blog";
}
<h2>@ViewData["Title"]</h2>
<form asp-controller="Student" asp-action="Create" method="post" class="form-horizontal" role="form">
  <div class="form-horizontal">
    <div asp-validation-summary="All" class="text-danger"></div>
    <div class="form-group">
      <label asp-for="FirstName" class="col-md-2 control-label"></label>
      <div class="col-md-10">
        <input asp-for="FirstName" class="form-control" />
        <span asp-validation-for="FirstName" class="text-danger"></span>
      </div>
    </div>
    <div class="form-group">
      <label asp-for="LastName" class="col-md-2 control-label"></label>
      <div class="col-md-10">
        <input asp-for="LastName" class="form-control" />
        <span asp-validation-for="LastName" class="text-danger"></span>
      </div>
    </div>
    <div class="form-group">
      <label asp-for="School" class="col-md-2 control-label"></label>
      <div class="col-md-10">
        <input asp-for="School" class="form-control" />
        <span asp-validation-for="School" class="text-danger"></span>
      </div>
    </div>
    <div class="form-group">
      <label asp-for="StartDate" class="col-md-2 control-label"></label>
      <div class="col-md-10">
        <input asp-for="StartDate" class="form-control" />
        <span asp-validation-for="StartDate" class="text-danger"></span>
      </div>
    </div>
    <div class="form-group">
      <div class="col-md-offset-2 col-md-10">
        <input type="submit" value="Create" class="btn btn-default" />
      </div>
    </div>
  </div>
</form>

• Add a new student to the database by running your application and clicking on Student >> Create New.

The WebAPI Controller

Let us add a Web API Studentsapi controller to our projects.

• In the Controllers folder, add a new “Web API Controller Class” named StudentapiController
• Replace StudentapiController class definition with the following code:

[Route("api/[controller]")]
public class StudentapiController : Controller {
  private SchoolContext _context { get; set; }
  public StudentapiController(SchoolContext context) {
    _context = context;
  }
  // GET: api/student
  [HttpGet]
  public IEnumerable<Student> Get() {
    return _context.Students.ToList();
  }
  // GET api/studentapi/5
  [HttpGet("{id}")]
  public Student Get(int id) {
    return _context.Students.FirstOrDefault(s => s.Id == id);
  }
  // POST api/studentapi
  [HttpPost]
  public void Post([FromBody]Student student) {
    _context.Students.Add(student);
    _context.SaveChanges();
  }
  // PUT api/studentapi/5
  [HttpPut("{id}")]
  public void Put(int id, [FromBody]Student student) {
    _context.Students.Update(student);
    _context.SaveChanges();
  }
  // DELETE api/studentapi/5
  [HttpDelete("{id}")]
  public void Delete(int id) {
    var student = _context.Students.FirstOrDefault(t => t.Id == id);
    if (student != null) {
        _context.Students.Remove(student);
        _context.SaveChanges();
    }
  }
}

• Hit CTRL-F5 on your keyboard and point your browser to /api/studentapi. You will see the seed data appearing as JSON in the browser:

In subsequent blog posts I shall discuss

• how you can add swagger to the Web API controller so that it is self-documenting

• how to interact with the Web API controller to retrieve, add, update, data with Google's postman

• how to publish an ASP.NET Core app that uses SQLite database to Azure