ASP.NET Core

The release of .Net Core made it really easy to use .Net and C# outside of Windows. However, when it comes to which database to use most Windows users think Sql Server. Unfortunately, Sql Server is not available outside of Windows just yet. While that doesn’t happen there are several options that are cross platform.

This post is a walk-through of how to set up a simple ASP.NET MVC Core project using three available options with Entity Framework Core: Sqlite, MySql and Postgres.

This post starts with an almost empty project that will end in website where you can add, remove, update and list products. All steps assume you are using the command line and Visual Studio Code (feel free to try this on Linux, Mac or Windows).

1. Getting started

The easiest way to get started if you are using the CLI (command line interface) is to use yeoman.

First, you need Nodejs so that you can get npm and install yeoman. You will also need the yeoman generator for aspnet. Finally you need to install bower because the yeoman templates expect you to have it installed.

You will will also need to have .Net Core installed. If you haven’t done it yet, here are the instructions.

To use yoeman’s ASP.NET Core generator type in the command line:

$ yo aspnet

     _-----_     ╭──────────────────────────╮
    |       |    │      Welcome to the       │
    |--(o)--|    │  marvellous ASP.NET Core  │
   `---------´   │        generator!         │
    ( _´U`_ )    ╰──────────────────────────╯
    /___A___\   /
     |  ~  |     
   __'.___.'__   
 ´   `  |° ´ Y ` 

? What type of application do you want to create? (Use arrow keys)
❯ Empty Web Application 
  Console Application 
  Web Application 
  Web Application Basic [without Membership and Authorization] 
  Web API Application 
  Nancy ASP.NET Application 
  Class Library 
  Unit test project (xUnit.net) 

I’m going to select “Empty Web Application” so that there’s the least amount of “noise” regarding what we want to do.

I’m only going to skim over the details that are not related to data access, if you need help with the rest have a look at the Github repo, or send me a message if you are having trouble running any of this.

2. Setting up MVC from scratch

The Empty Web Application template does not come with ASP.NET MVC configured. To add it you first have to edit project.json. Find the “dependencies” section and add

 "Microsoft.AspNetCore.Mvc": "1.0.1"

I’ve chosen version 1.0.1 but there might be a more recent version when you do this (or there might not even be a project.json at all). I’m using Visual Studio Code’s intellisense to find the available versions.

The next thing you need to do is configure the baked in IoC container with ASP.NET MVC’s dependencies in Startup.cs’s ConfigureServices method:

    public void ConfigureServices(IServiceCollection services)
    {
        services.AddMvc();             
    }

If what I just said does not make sense to you, read about Dependency Injection in asp.net core.

Next, also in Startup.cs, update the Configure method so that ASP.NET MVC is added to the pipeline:

    public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
    {
        loggerFactory.AddConsole();

        if (env.IsDevelopment())
        {
            app.UseDeveloperExceptionPage();
        }

        app.UseMvcWithDefaultRoute();

        // app.Run(async (context) =>
        // {
        //     await context.Response.WriteAsync("Hello World!");
        // });
    }

If you are not familiar with Middleware and its pipeline, read the Middleware documentation.

Next we are going to create a Controller. Since the Empty Web Application does not create a Controllers folder you can start by creating one (although you don’t need to, a controller class can be anywhere in the project). After that run

$ yo aspnet:mvccontroller HomeController

This will scaffold a new controller for you, and will even set the correct namespace depending on the folder you create it in. If by any chance this does not work for you, try the following command:

$ yo aspnet --help

This will list all available “subgenerators” (the name of the subgenerator might have changed since I wrote this, it has already changed at least once).

The scaffolded HomeControler has just one method:

public IActionResult Index()
{
    return View();
}

However we don’t have a view for it yet. So let’s create it. First create a Views folder and inside that a Home folder. Then run this command:

yo aspnet:htmlpage Index

The file that is going to be created is Index.html. Rename it to Index.cshtml. The reason we did this is because the MVC view template generates a really empty view (it assumes you have a Layout page).

Because we’ve started with the empty project template we don’t have a Layout page, so our views will need to have all the html required to render them, and the easiest way to do this is to create the Index.html page and rename it to .cshtml.

Adding Entity Framework Core to the project

To add Entity Framework Core to the project edit project.json and in the dependencies section add:

"Microsoft.EntityFrameworkCore": "1.0.1"

If you are using Visual Studio Code you should be prompted to restore the missing dependencies, if not, in the command line you can type

$ dotnet restore

And have the NuGet package for EntityFrameworkCore downloaded.

Next we’ll be creating a Models folder and creating a class inside it named Product. We can use yeoman again to generate the class:

$ yo aspnet:class Product

Then modify it so that Product has three properties, Id, Name and Price:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

Now we are going to create EntityFramework’s DbContext class that will contain the Product’s DbSet. We can start again with yeoman’s class subgenerator:

$ yo aspnet:class ProductsDbContext

And then modify the file so that it looks like this:

using Microsoft.EntityFrameworkCore;

namespace DataAccessTutorial.Web.Models
{
    public class ProductsDbContext : DbContext
    {
        public DbSet<Product> Products { get; set; }

        public ProductsDbContext(DbContextOptions<ProductsDbContext> options) : base(options)
        {
        }        
    }
}    

The important thing to note here is the constructor with a DbContextOptions<TypeOfYourDbContext> parameter. This is only a requirement if you are using the IoC container for ASP.NET Core MVC. The actual IoC setup depends on which database you are using (next steps).

If you were not using ASP.NET Core (only .Net Core, for example a console app) and you did not rely on IoC, you would have to provide a parameterless constructor and override a method called OnConfiguring. For example, if we were using Sqlite it would look like this:

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Data source=productsdb.sqlite");
    }

Sqlite

Sqlite is the simplest database option because you don’t need to install a database engine, and that’s because a Sqlite database is just a standalone file.

To enable using Sqlite in your project you’ll have to add the following NuGet package in your project.json dependencies section:

"Microsoft.EntityFrameworkCore.Sqlite": "1.0.1"

Because we want to take advantage of Entity Framework’s migrations you also have to add the following NuGet package:

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

And we also have to add it to the tools section in project.json’s (you have to add it to both places):

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

After restoring these dependencies we can now go back to our Startup.cs’s ConfigureServices method and configure the baked in IoC so that our ProductsDbContext is resolved with the configuration required for Sqlite:

    public void ConfigureServices(IServiceCollection services)
    {
        services.AddMvc();   
        services.AddDbContext<ProductsDbContext>(options =>
            options.UseSqlite("Data Source=productsDb.sqlite"));
    }

Also, because we’ve added Microsoft.EntityFrameworkCore.Tools to the tools section in project.json we can now write, in the command line:

$ dotnet ef

And get something similar to this:

                     _/\__       
               ---==/    \\      
         ___  ___   |.    \|\    
        | __|| __|  |  )   \\\   
        | _| | _|   \_/ |  //|\\ 
        |___||_|       /   \\\/\\
Entity Framework .NET Core CLI Commands 1.0.0-preview2-21431
Usage: dotnet ef [options] [command]
Options:
...

We can now add migrations using the command line. A migration is a set of instructions that is able to take the database from its current state to a state that matches the entities in your DbContext. A migration also contains the instructions to go back to the previous state. You can read more about them in the Migrations document in docs.asp.net.

Lets create a migration and name it Initial:

$ dotnet ef migrations add Initial

If you need help with any of these commands you can just type dotnet ef [commandName], e.g. dotnet ef migrations, and you’ll be given a list of possible options.

To apply the migration we can run the following command:

$ dotnet ef database update Initial

This will update the database (which is non-existent) and take it to a state where it matches what is defined in the DbContext. In our case, a database with a table named Product with three columns, Id, Name and Price.

If we were to make changes to Product.cs, for example add a property named Description, we could create a new migration. That new migration would just have the instructions to update the Product table by adding the new Description column.

You don’t need to apply migrations one by one, you can just run:

$ dotnet ef database update

And that will add all pending migrations.

And that’s it for Sqlite, if you inspect the output folder you should see a productsDb.sqlite file. That’s our Sqlite database file.

If you are not interested in how this procedure works for MySql and Postgres go directly to the “Using the DbContext” section.

MySql

MySql is one of the most popular database engines and it’s free. If you don’t have it already installed and if you are using Ubuntu, you can follow the installation instructions for Ubuntu. Here are the instructions for Windows and OS X.

We are not going to use the dotnet ef command with MySql because at this time (27/10/2016) the official MySql provider for .Net Core does not support migrations. There’s no way to generate the database from the command line. Alternatively, it is always possible to create the database first, or we could generate it from code, which is what we’ll do.

The required packages for MySql are (add them to projects.json dependencies):

"MySql.Data.Core": "7.0.4-ir-191",
"MySql.Data.EntityFrameworkCore": "7.0.4-ir-191"

These were the latest working versions at the time I wrote this. You can use Visual Studio Code’s auto completion to check which other ones are available.

The next step is to setup IoC for our DbContext in Startup.cs’s ConfigureServices:

    public void ConfigureServices(IServiceCollection services)
    {
        services.AddMvc();
        services.AddDbContext<ProductsDbContext>(options => 
            options.UseMySQL("server=localhost;userid=root;pwd=thePassword;port=3306;database=ProductsDb;sslmode=none"));                                     
    }

Because migrations are not supported, a possible workaround is to have the database generated, if it not exists, when the application starts. To do that edit your Startup.cs’s Configure method:

    public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory, ProductsDbContext productsDbContext)
    {
        loggerFactory.AddConsole();

        if (env.IsDevelopment())
        {
            app.UseDeveloperExceptionPage();
            productsDbContext.Database.EnsureCreated();
        }

The EnsureCreated method will create the database if it does not exist.

And that’s it for MySql, if you are not interested in Postgres just go to section “Using the DbContext”.

Postgres

Postgres, like MySql, is a very popular database that we can also use with Entity Framework Core. It has the advantage of already supporting migrations.

Here are the installation instructions for Ubuntu 16.04, Windows and OS X.

To enable using Postgres in your project add the following dependencies:

"Npgsql.EntityFrameworkCore.PostgreSQL": "1.0.2"
"Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final"

And also update the tools section of project.json with the Tools package:

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

Don’t forget you can check the latest version of the NuGet packages when typing the version number in Visual Studio Code.

Next update Startup.cs so that when ProductsDbContext is requested, it’s initialized for Postgres with the right connection string:

    public void ConfigureServices(IServiceCollection services)
    {
        services.AddMvc();
        services.AddDbContext<ProductsDbContext>(options =>
            options.UseNpgsql("User ID=postgres;Password=YourPassword;Host=localhost;Port=5432;Database=ProductsDb;Pooling=true;"));             
    }

Don’t forget to update the connection string with the correct user and password that you are using.

Let’s generate a migration, call it Initial

$ dotnet ef migrations add Initial

And use apply it to the database (in this case, because it does not exist yet it will create the db):

$ dotnet ef database update

You now have the database created.

Using the DbContext

What we’ll do for the rest of this walk-through is use the ProductsDbContext to create, modify, list and delete products. Note that our choice of a particular database has no influence on how we use a DbContext, the only difference is in how you set it up using IoC (in the Startup.cs’s ConfigureServices method) where we supply the different connection strings. Or if you are not using IoC, in the overridable OnConfiguring method in the derived DbContext class, where you also supply the connection string.

Because of the IoC capabilities that come out of the box with ASP.NET MVC Core, and for which we’ve already configured our ProductsDbContext, we can now add ProductsDbContext as a constructor parameter in a Controller and it will be ready for use at runtime.

Open HomeController.cs and add ProductsDbContext to its constructor and store it in a private field:

public class HomeController : Controller
{
    private readonly ProductsDbContext _productsDb;
    public HomeController(ProductsDbContext productsDb)
    {
        _productsDb = productsDb;
    }
...

And that’s it, our ProductsDbContext is ready to be used. Note that it is configured with a scoped lifetime. What that means is that during the duration of a request, every time that ProductsDbContext is requested as a dependency, the same instance of it will be served.

This is important because of how O/RMs in general and Entity Framework in particular works. All changes that you make using a DbContext will run in the same transaction when you save them (i.e. when you call .SaveChanges()). So that this becomes clearer imagine that our HomeController had a dependency on IProductsRepository and IOrdersRepository instead. Both of which with implementations that have a dependency on ProductsDbContext. That means that two different classes will request the IoC container for an instance of ProductsDbContext.

As IProductsRepository and IOrdersRepository are used, they make use of their instance of ProductsDbContext. At some point either IProductsRepository or IOrdersRepository will call .SaveChanges on their ProductsDbContext, and as they will be using the same instance both sets of changes (from ProductsRepository and OrdersRepository) will be persisted or both will be rolled back in case of an error.

If you find it weird to use the repository pattern in this scenario, you are right, it is weird, but it was just to explain that the same ProductsDbContext is used throughout a request.

Next we can start adding the controller actions:

    public IActionResult Index()
    {
        return View(_productsDb.Products.ToList());
    }

    public IActionResult Create()
    {
        return View();
    }

    [HttpPost]
    public IActionResult Create(Product product)
    {
        if (!ModelState.IsValid)
            return View(product);

        _productsDb.Products.Add(product);
        _productsDb.SaveChanges();
        return RedirectToAction(nameof(Index));
    }

    public IActionResult Update(int id)
    {               
        return View(_productsDb.Products.Single(p => p.Id == id));
    }

    [HttpPost]
    public IActionResult Update(Product product)
    {
        if (!ModelState.IsValid)
            return View(product);

        _productsDb.Entry(product).State = EntityState.Modified;
        _productsDb.SaveChanges();
        return RedirectToAction(nameof(Index));
    }

    [HttpPost]
    public IActionResult Delete(int id)
    {            
        _productsDb.Entry(new Product { Id = id}).State = EntityState.Deleted;
        _productsDb.SaveChanges();
        return RedirectToAction(nameof(Index));
    }

There’s not much to say about these if you are already familiar with Entity Framework. There’s a trick you can do to save a database call when performing a delete operation (this is not specific to Entity Framework Core). Creating a product with the same Id as the one we want to delete, and by modifying its state to EntityState.Deleted is enough to have that product deleted from the database.

The non-efficient way of doing this is:

var product = _productsDb.Products.Single(p => p.Id == id); //First database call (note that there's no Find method in EF Core) 
_productsDb.Products.Remove(product);
_productsDb.SaveChanges(); //Second db call

The database command that will be issued will be equivalent to this:

DELETE FROM Products
WHERE Id = @Id

That’s why it is sufficient to create a new Product, set it’s Id and change it’s state in the DbContext to Deleted. The same SQL will be generated.

If you are interested in the Razor views you can find them in this GitHub repository (Index, Create and Update).

You can find all the steps in this walk-through in the repo’s main branch

$ git clone https://github.com/ruidfigueiredo/DataAccessTutorial.Web

There are three branches in the repository, one with the example using MySql (without using migrations), another with Postgres (using migrations), and another with Sqlite (with migrations). The branch names are MySql, Postgres and Sqlite. The master branch uses Sqlite without migrations so that you can just run the project without having to provide any configuration or install any database.

After cloning the main repo you can get to the branches by doing, for example for MySql:

$ git checkout -b MySql origin/MySql

To get the full list of branch names

$ git branch -a

Also, if you just run the project from the command line, you should specify the “Development” environment. The master branch, for example, will only create the Db if running as “Development”:

$ dotnet run --environment "Development"

If this walk-through has helped you, or if you had any problems, don’t forget to drop a line in the comments.