Code Clue

This walkthrough will get you started with an application that uses the Entity Framework (EF) to read and write data from a SQLite database. It is intended to be similar to the Code First to a New Database walkthrough.

There are currently two SQLite providers for EF that I know of: System.Data.SQLite andDevart's dotConnect for SQLite. Devart's provider has a much richer set of features, but it is also a commercial product. In the spirit of FOSS, we will be using the System.Data.SQLite provider for this walkthrough. I encourage you to keep the Devart provider in mind, however, if your project requires that extra level of support.

Create the Application

For simplicity, we will be using a Console Application, but the basic steps are the same regardless of project type.

1. Open Visual Studio
2. Select File -> New -> Project...
3. Select Console Application
4. Name the project
5. Click OK

Create the Model

For our model, we'll be borrowing pieces from the Chinook Database (a cross-platform, sample database). Specifically, we will be using Artists and Albums.

Add the following two classes to your project.

public class Artist
{
    public Artist()
    {
        Albums = new List();
    }
 
    public long ArtistId { get; set; }
    public string Name { get; set; }
 
    public virtual ICollection Albums { get; set; }
}
 
public class Album
{
    public long AlbumId { get; set; }
    public string Title { get; set; }
 
    public long ArtistId { get; set; }
    public virtual Artist Artist { get; set; }
}

Create a Context

1. Select Tools -> Library Package Manager -> Package Manager Console
2. Inside the Package Manager Console (PMC) run Install-Package EntityFramework

class ChinookContext : DbContext
{
    public DbSet Artists { get; set; }
    public DbSet Albums { get; set; }
 
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // Chinook Database does not pluralize table names
        modelBuilder.Conventions
            .Remove();
    }
}

Install the Provider

1. Inside PMC, run Install-Package System.Data.SQLite.x86

Add the Database

1. Download and extract the SQLite version of the Chinook Database
2. Select Project -> Add Existing Item...
3. Browse to the folder where you extracted the database
4. Select All Files from the dropdown next to File name
5. Select the Chinook_Sqlite_AutoIncrementPKs.sqlite file
6. Click Add
7. Select the file in Solution Explorer
8. In Properties, set Copy to Output Directory to Copy if newer

Start Coding

using (var context = new ChinookContext())
{
    var artists = from a in context.Artists
                  where a.Name.StartsWith("A")
                  orderby a.Name
                  select a;
 
    foreach (var artist in artists)
    {
        Console.WriteLine(artist.Name);
    }
}

        using (var context = new ChinookContext())
{
    context.Artists.Add(
        new Artist
        {
            Name = "Anberlin",
            Albums =
            {
                new Album { Title = "Cities" },
                new Album { Title = "New Surrender" }
            }
        });
    context.SaveChanges();
}

Conclusion