Wednesday, December 24, 2014

How to use Entity Framework and SQLite

SQLite is the definition of a lightweight database. Using SQLite you can run an entire database with only a 304 KB executable and a database file. It's fast, stable, and very easy to use. Entity Framework is Microsoft's official ORM, and it has support for SQLite!

SQLite Tools

To run SQLite you need only to download the precompiled SQLite binaries for windows:

You can easily manipulate the database via command line. However, if you would prefer to use a GUI, there is a wonderful Firefox plugin for managing your SQLite databases.

Entity Framework Setup

To get stated using Entity Framework you will need to add two NuGet packages to your solution:

  1. EntityFramework
  2. System.Data.SQLite (x86/x64)

After that you will need to make sure that your app.config file has properly registered the both a System.Data.SQLite provider and provider factory.

Demo App.config

<?xml version="1.0" encoding="utf-8"?>
    <section name="entityFramework" 
             type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089" 
             requirePermission="false" />
    <add name="TestContext" 
         connectionString="Data Source=C:\Code\DB\Test.sqlite" 
         providerName="System.Data.SQLite" />
      type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
        type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
      <remove invariant="System.Data.SQLite" />
      <add name="SQLite Data Provider"
          description="Data Provider for SQLite"
          type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />

Demo Code

public class User
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int Age { get; set; }
public class TestContext : DbContext
    public DbSet<User> Users { get; set; }
public class TestContextTests
    public async Task AddUser()
        using (var db = new TestContext())
        using (var transaction = db.Database.BeginTransaction())
            var user = db.Users.Add(new User
                FirstName = "Taboo",
                LastName = "DuPont",
                Age = 3
            await db.SaveChangesAsync();
            Assert.NotEqual(0, user.Id);



  1. Nice, thanks for posting! I'm not in a position to test this currently. Out of curiosity, do you not have to have a default constructor on your TestContext to initialise the connection string? I know I have to do this using Express so wondered if it was the same with Lite?


    public class TestContext : DbContext
    public TestContext() : base("TestContext") //the name of your connection string

    public DbSet Users { get; set; }

    Nice blog! :)

    1. Great question! The default constructor for a DataContext will look in the app.config for a connection string with the same name as the type. Thus in the sample config I have a connection string named "TestContext"


Real Time Web Analytics