ASP.NET Core WebAPI app Using SQLite & EF7

This tutorial uses Visual Studio 2015 and ASP.NET 5 RC. This was recently renamed by Microsoft to ASP.NET Core.

My intension is to give you a practical introduction into developing ASP.NET Core 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 app that uses the following Student entity:

WebAPI project

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

• File >> New >> Project
• Templates >> Visual C# >> Web
• Select “ASP.NET Web Application” and name the project SQLiteWebAPI.
• Under ASP.NET 5 Templates select “Web API”

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

{
   "projects": [ "src", "test" ],
   "sdk": {
      "version": "1.0.0-rc1-update1"
   }
}

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

"dependencies": {
    "Microsoft.ApplicationInsights.AspNet": "1.0.0-rc1",
    "Microsoft.AspNet.IISPlatformHandler": "1.0.0-rc1-final",
    "Microsoft.AspNet.Mvc": "6.0.0-rc1-final",
    "Microsoft.AspNet.Server.Kestrel": "1.0.0-rc1-final",
    "Microsoft.AspNet.StaticFiles": "1.0.0-rc1-final",
    "Microsoft.Extensions.Configuration.FileProviderExtensions" : "1.0.0-rc1-final",
    "Microsoft.Extensions.Configuration.Json": "1.0.0-rc1-final",
    "Microsoft.Extensions.Logging": "1.0.0-rc1-final",
    "Microsoft.Extensions.Logging.Console": "1.0.0-rc1-final",
    "Microsoft.Extensions.Logging.Debug": "1.0.0-rc1-final"
  },

We will need to add some more dependencies in order to use SQLite with Entity Framework 7. Add these dependencies:

"EntityFramework.Commands": "7.0.0-rc1-final",
"EntityFramework.SQLite": "7.0.0-rc1-final",

As soon as you save the project.json file you will notice that the references will be installed by the Nuget package manager.
Since we will be doing EF code migrations, add the following EF command to the commands section:

"ef": "EntityFramework.Commands",

Class Library Project

It is good practice to place all your data models inside a class library. Therefore, we will add to our solution a class library:

• Right-click on the src node in Solution Explorer
• Add >> New Project…
• Select Class Library (Package) >> Name it DataModel then click on OK

• Delete Class1.cs
• Open project.json in the DataModel project. Make sure that the runtime versions match those in the project.json file of the SQLiteWebAPI project. I found that the SQLiteWebAPI project used dnx451 & dnxcore50, whereas the DataModel project used net451 & dotnet5.4. Therefore, I changed dotnet5.4 in the DataModel project to dnxcore50.
• In the DataModel project’s project.json file, add the following section right before frameworks:

"dependencies": {
    "EntityFramework.Commands": "7.0.0-rc1-final",
    "EntityFramework.SQLite": "7.0.0-rc1-final",
  },

Let’s create our Student class in the DataModel project. 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 in the DataModel project, add another class file named SchoolContext.cs with the following code:

public class SchoolContext : DbContext {
    public DbSet<Student> Students { get; set; }

    protected override void OnModelCreating(ModelBuilder builder) {
        builder.Entity<Student>().HasKey(m => m.Id);
        base.OnModelCreating(builder);
    }
}

In the SQLiteWebAPI project, make a reference to the DataModel project so that the entity classes are available to the web project.

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.sqlite"
  }
},

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

• Open the Startup.cs file in the SQLiteWebAPI project
• Add the following instance variable to the Startup class:

private IApplicationEnvironment _appEnv;

• Inject the argument “IApplicationEnvironment appEnv” into the Startup constructor so that it looks like this:

public Startup(IHostingEnvironment env, IApplicationEnvironment appEnv)

• Add the following assignment inside the constructor:

_appEnv = appEnv;

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

var connection = Configuration["Data:DefaultConnection:ConnectionString"];
connection = connection.Replace("=", "=" + _appEnv.ApplicationBasePath + "/");
services.AddEntityFramework()    
  .AddSqlite()
  .AddDbContext<SchoolContext>(options => options.UseSqlite(connection));

Seed Data

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

• Back in the DataModel project, in the Models folder, create a class named SeedData.cs.
• Add the following Initialize() method code inside the SeedData 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 belonging to the SQLiteWebAPI project. Next, we can make a call to seed the data at the bottom of the Configure() method with the following statement:

      SeedData.Initialize(context);

Migrations

We will execute EF migrations commands from the SQLiteWebAPI project. It is necessary to let the main app know that migrations files exist in the DataModel project. In the SQLiteWebAPI project Startup class ConfigureServices() method, chain .MigrationsAssembly("DataModel") to options.UseSqlite(connection).
The ConfigureServices class will look like this:

public void ConfigureServices(IServiceCollection services) {
    // Add framework services.
    services.AddApplicationInsightsTelemetry(Configuration);

    var connection = Configuration["Data:DefaultConnection:ConnectionString"];
    connection = connection.Replace("=", "=" + _appEnv.ApplicationBasePath + "/");
    services.AddEntityFramework()
      .AddSqlite()
      .AddDbContext<SchoolContext>(options => options.UseSqlite(connection)
      .MigrationsAssembly("DataModel"));

    services.AddMvc();
}

We are now ready to do some migrations:

• Compile your application
• Open a command terminal inside the src\SQLiteWebAPI folder
• To ensure that we will use the same version of the runtime as the application, execute the following command inside the command terminal:

dnvm use 1.0.0-rc1-update1 -r coreclr -arch x64

• Next, we will add a migration in the DataModel project with the following ef command:

dnx ef migrations add InitialCreate --context SchoolContext --targetProject DataModel

This causes the migrations files to be created in the DataModel project.

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

dnx ef database update

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

The WebAPI Controller

Now that we are done setting up database artifacts, let us create a WebAPI Students controller.

• In the Controllers folder, delete the ValuesController.cs file
• In the Controllers folder, add a new “Web API Controller Class” named StudentsController
• Make sure the StudentsController class is annotated with:

[Route("api/[controller]")]

• Replace contents of the StudentsController class with the following code:

private SchoolContext _context { get; set; }

public StudentsController(SchoolContext context) {
    _context = context;
}

// GET: api/student
[HttpGet]
public IEnumerable<Student> Get() {
    return _context.Students.ToList();
}

// GET api/student/5
[HttpGet("{id}")]
public Student Get(int id) {
    return _context.Students.FirstOrDefault(s => s.Id == id);
}

// POST api/student
[HttpPost]
public void Post([FromBody]Student student) {
    _context.Students.Add(student);
    _context.SaveChanges();
}

// PUT api/student/5
[HttpPut("{id}")]
public void Put(int id, [FromBody]Student student) {
    _context.Students.Update(student);
    _context.SaveChanges();
}

// DELETE api/student/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();
    }
}

• Open the Properties\launchSettings.json and change the launchUrl value to api/students

It is now time to run the application. Hit CTRL-F5 on your keyboard. You will see the seed data appearing as JSON in the browser:

Testing with PostMan

If you do not already have the Chrome extension named “PostMan – REST Client”then take a moment and install it. Start PostMan in Chrome.

Retrieving data with GET method

1. Enter URL like http://localhost:50932/api/Students
2. Select GET
3. Click Send button

Adding data with POST method

1. Change from GET to POST
2. Click Headers button
3. Enter Header=Content-Type & Value=application/json
4. Click Raw button
5. Enter a JSON object like this: {"FirstName":"Jane","LastName":"Roberts","School":"Music","StartDate":"2014-10-09T00:00:00"}
6. Click on Send.

Updating data with PUT method

Let us update the last record we added with Id=7. We will change the school to Tourism.

1. Add /7 to the URL
2. Change POST to PUT
3. The JSON object will be complete with all data items, including Id. The only changed data is School=Tourism as follows:{"Id":7,"FirstName":"Jane","LastName":"Roberts","School":"Tourism","StartDate":"2014-10-09T00:00:00"}
4. Click Send

 Deleting data with DELETE method

1. Add /7 to the URL
2. Change PUT to DELETE
3. Click Send