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:

• SQLite Downloads (Precompiled 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.

• SQLite Manager (Firefox Addon)

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"?>

<configuration>

  <configSections>

    <section name="entityFramework" 

             type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" 

             requirePermission="false" />

  </configSections>

  <connectionStrings>

    <add name="TestContext" 

         connectionString="Data Source=C:\Code\DB\Test.sqlite" 

         providerName="System.Data.SQLite" />

  </connectionStrings>

  <entityFramework>

    <defaultConnectionFactory 

      type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />

    <providers>

      <provider 

        invariantName="System.Data.SQLite" 

        type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />

    </providers>

  </entityFramework>

  <system.data>

    <DbProviderFactories>

      <remove invariant="System.Data.SQLite" />

      <add name="SQLite Data Provider"

          invariant="System.Data.SQLite"

          description="Data Provider for SQLite"

          type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />

    </DbProviderFactories>

  </system.data>

</configuration>

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

{

    [Fact]

    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);

            transaction.Rollback();

        }

    }

}

Enjoy
Tom