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

2 comments:

  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?

    Eg;

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

    }
    public DbSet Users { get; set; }
    }

    Nice blog! :)

    ReplyDelete
    Replies
    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"

      Delete

Real Time Web Analytics