SQLite in ASP.NET Core with EntityFrameworkCore

Question

How do you add and use an SQLite database in an ASP.NET Core web application, using EntityFramework 7 ?

I dived into ASP.NET Core the moment I heard about it and created my first web application, I suddenly had a bunch of data that I wanted to store and SQLite seemed like the obvious choice.
Since I wanted it to stay with my application, keep it lightweight, simple and avoid setting up a separate database.

So how would one go about creating an SQLite database in ASP.NET Core?

• ASP.NET Core - now formerly known as ASP.NET MVC 6
• EntityFramework Core - now formerly known as EntityFramework 7

Solution 1

Update: November 4th, 2016.
Reformatting - pictures to code examples.
Info: Keep in mind that in some code examples, code that was generated by the visual studio template have been omitted.

Update: July 11th, 2016.
.NET Core and EntityFrameWork Core version 1.0 is upon us!
So this guide deserves a little update

Step 1:
Create your application.

Step 2:
Get the necessary packages
Microsoft.EntityFrameworkCore 1.0.0
Microsoft.EntityFrameworkCore.SQlite 1.0.0

Step 3:
Create your context:
(The Context will be a class that you create)

public class DatabaseContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Filename=MyDatabase.db");
    }
}

Step 4:
Add your context to your services:
(Located in your Startup class)

public void ConfigureServices(IServiceCollection services)
{
    services.AddEntityFrameworkSqlite().AddDbContext<DatabaseContext>();
}

Step 5:
Create your database on startup, by adding it to the startup method
(Located in the Startup class)

public Startup(IHostingEnvironment env)
{
    using(var client = new DatabaseContext())
    {
        client.Database.EnsureCreated();
    }
}

Et Voíla!
Now you will be able to use SQLite in your ASP.NET Core applications.
The old guide still applies regarding how you create your models as well as using your database context.

---

Update: May 28th, 2016.
.NET Core RC2 and EntityFramework Core RC1 have been released.
They have improved and simplified the steps for setting up SQLite.
But I'm experiencing some trouble with it and can't replicate it, because of an error with the Newtonsoft.Json library and NuGet.

I recommend sticking to the RC1 libraries if you want to do this, for now!

---

Step 1:
Create your ASP.NET web application

Step 2:
Go to Tools -> Nuget Packet Manager -> Manage Nuget Packages for Solution.
Search for EntityFramework.SQLite and check the Include prelease box.
Install the package

Step 3: Creating a context
Create a context class for your database.
Call it whatever you want, but let's go with something that's customiary, like MyDbContext. Make your new class inherit the DbContext class and override the OnConfiguring method and define your connection like so:

public class MyDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var connectionStringBuilder = new SqliteConnectionStringBuilder { DataSource = "MyDb.db" };
        var connectionString = connectionStringBuilder.ToString();
        var connection = new SqliteConnection(connectionString);

        optionsBuilder.UseSqlite(connection);
    }
}

Step 4:
Go to the Startup.cs and make sure your database is created at the start of your web application:

public Startup(IHostingEnvironment env)
    {
        // Set up configuration sources.
        var builder = new ConfigurationBuilder()
            .AddJsonFile("appsettings.json")
            .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true);         


        using (var db = new MyDbContext())
        {
            db.Database.EnsureCreated();
            db.Database.Migrate();
        }

    }

Secondly we need to add the service:

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

        services.AddEntityFramework()
        .AddSqlite()
        .AddDbContext<MyDbContext>();
    }

Step 5: Defining your Models
Create your models and go to MyDbContext.cs and add a new property for each of your new models (given that you want a table for each!)
Here's an example:
My Model:

public class Category
{
    public int Id { get; set; }

    public string Title { get; set; }

    public string Description { get; set; }

    public string UrlSlug { get; set; }
}

Adding it to my context:

public class MyDbContext : DbContext
{
    public DbSet<Category> Categories { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var connectionStringBuilder = new SqliteConnectionStringBuilder { DataSource = "MyDb.db" };
        var connectionString = connectionStringBuilder.ToString();
        var connection = new SqliteConnection(connectionString);

        optionsBuilder.UseSqlite(connection);
    }
}

Step 6: Using the the context
Go to your HomeController and add a new field to your controller.
private readonly MyDbContext _myDbContext = new MyDbContext();
And use it in an ActionResult by passing it to the returned view: (Now lets assume we have a category in our database)

public IActionResult Index()
{
    var category = _myDbContext.Categories.First();
    return View(category);
}

So by going to your Index view, you can use our imaginary data from the database. By defining a model in the top of your view like so:

@model  MyNameSpace.Models.Category
@{
   ViewData["Title"] = "Hey Ho! SO!";
}


<div class="page-header">
    <h1>@ViewData["Title"]</h1>
</div>

<div class="container">
    @Model.Title
</div>

Now by starting our web application and going to the assigned address we should see a default html page with a fancy bootstrap header, showing this on the page:

The second line is (or would be) the title of our first category in our database.

Entity Framework 7 Docs

This is my first Q&A - if you have any input or something that needs clarifying don't hesitate to comment.
This is a very basic example of how to implement an SQLite database into an ASP.NET Core MVC web application.
Do note that there is several ways to set the connection string for the database, how to use the context and that EntityFramework 7 is still a prerelease

Solution 2

If you want to create an ASP.NET Core web application using SQLite for the database, I highly recommend using Yeoman to scaffold the app for you. You need to first install .NET Core 1.1 SDK (Visual Studio 2015 seems to only include SDK versions 1.0.0 and 1.0.1 at the moment). You then need to install Node.js which comes with npm and then install the following npm packages: yo and generator-aspnet. Then all you have to do is run yo aspnet and answer a few questions.

C:\Development>yo aspnet
? ==========================================================================
We're constantly looking for ways to make yo better!
May we anonymously report usage statistics to improve the tool over time?
More info: https://github.com/yeoman/insight & http://yeoman.io
========================================================================== Yes

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

? What type of application do you want to create? Web Application
? Which UI framework would you like to use? Bootstrap (3.3.6)
? What's the name of your ASP.NET application? WebApplication

Afterwards, you will get the following response:

 Your project is now created, you can use the following commands to get going
    cd "WebApplication"
    dotnet restore
    dotnet build (optional, build will also happen when it's run)
    dotnet ef database update (to create the SQLite database for the project)
    dotnet run

Run dotnet restore, dotnet ef database update, and then dotnet run and go to localhost:5000 to make sure the project is running.

Now you can open the project in Visual Studio 2015 (assuming you're on Windows) or Visual Studio Code.

The great thing about this is that Startup.cs, project.json, and appsettings.json files are setup to use SQLite. Also, a SQLite database is created for you:

Startup.cs:

public void ConfigureServices(IServiceCollection services)
{
    // Add framework services.
    services.AddDbContext<ApplicationDbContext>(options =>
        options.UseSqlite(Configuration.GetConnectionString("DefaultConnection")));
}

project.json:

{
    "Microsoft.EntityFrameworkCore.Sqlite": "1.1.0",
    "Microsoft.EntityFrameworkCore.Sqlite.Design": {
      "version": "1.1.0",
      "type": "build"
    }
}

appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=WebApplication.db"
  }
}

Your SQLite database will be located in bin/Debug/netcoreapp1.0. In my case, it is located in C:\Development\WebApplication\bin\Debug\netcoreapp1.0\WebApplication.db

If you want to rename the SQLite database, modify appsettings.json file and run dotnet ef database update.

To learn more about using SQLite database with .NET Core and EF Core, check out this article: .NET Core - New Database

Solution 3

1. Install Below mentioned packages

    PM> Install-Package Microsoft.EntityFrameworkCore
    PM> Install-Package Microsoft.EntityFrameworkCore.Sqlite
    PM> Install-Package Microsoft.EntityFrameworkCore.Tools
   

2. Create Models

3. Create DBContext class add SQLite connection configuration

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite("Data Source=DBFileName.db");
   

4. Run migration commands to start using it

    PM> add-migration <MigrationName>  //Ex: add-migration IntialMigration
    PM> update-database
   

https://fullstack-lab.co.in/Sqlite-entity-framework-core-quick-start

This article provides simple steps to use SQLite with Asp.net core 3.1

Comments

[hB0]

The specified framework 'Microsoft.NETCore.App', version '1.1.0' was not found. So now it needs 1.1.0 -- the yo aspnet generator breaks backward compatibility with v 1.0.1..

[kimbaudi]

If you're going to use Visual Studio with Yeoman, you have 2 choices: 1) download .NET Core 1.1 SDK & update NuGet to Beta. 2) Manually edit package.json and change "1.1.0-preview4-final" to "1.0.0-preview2-003131" (and also change "1.1.0" to "1.0.1". If you create a global.json file, make sure sdk.version is still "1.0.0-preview2-003131" regardless of your choosing. MS is getting rid of package.json in favor of .csproj so its necessary to recognize package.json

[iJK]

my app is using a Development settings and I am unable to create migrations that use Sqlite. Here is my question if you can help stackoverflow.com/questions/49598638/…

[rizu]

is this till applicable 2020, in core 3.1

[Cicero]

@rizu Yes, it is.

[saravana manikandan]

Sure. I will make sure from next time. Thanks