October 12, 2012

Entity Framework 5 on SQLite

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 and Devart'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<Album>();
    }

    public long ArtistId { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Album> 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

In EF, the context becomes your main entry point into the database. Before we define our context though, we will need to install Entity Framework.
  1. Select Tools -> Library Package Manager -> Package Manager Console
  2. Inside the Package Manager Console (PMC) run Install-Package EntityFramework
Now, add the context class to your project.
class ChinookContext : DbContext
{
    public DbSet<Artist> Artists { get; set; }
    public DbSet<Album> Albums { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // Chinook Database does not pluralize table names
        modelBuilder.Conventions
            .Remove<PluralizingTableNameConvention>();
    }
}

Install the Provider

In order to connect to SQLite databases, we will need to install an appropriate ADO.NET and Entity Framework provider. Luckily, the provider we're using is available via NuGet.
  1. Inside PMC, run Install-Package System.Data.SQLite.x86
We also need to register the provider. Open App.config, and anywhere inside the configuration element, add the following fragment.
<system.data>
  <DbProviderFactories>
    <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>

Add the Database

Unfortunately, System.Data.SQLite does not support creating databases. So instead of letting Code First create our database, we will need to manually add a database to our project. It's a good thing we're using a sample database that's available for SQLite!
  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
Also, add a connection string to the App.Config that points to the database file. Anywhere inside the configuration element, add the following fragment.
<connectionStrings>
  <add name="ChinookContext"
        connectionString=
"Data Source=|DataDirectory|Chinook_Sqlite_AutoIncrementPKs.sqlite"
        providerName="System.Data.SQLite" />
</connectionStrings>

Start Coding

Ok, we should be ready to start coding our application. Let's see what artists exist in the database. Inside Program.cs, add the following to Main.
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);
    }
}
Hmm, it looks like one of my favorite bands is missing. Let's add it.
using (var context = new ChinookContext())
{
    context.Artists.Add(
        new Artist
        {
            Name = "Anberlin",
            Albums =
            {
                new Album { Title = "Cities" },
                new Album { Title = "New Surrender" }
            }
        });
    context.SaveChanges();
}
We can also update and delete existing data like this.
using (var context = new ChinookContext())
{
    var police = context.Artists.Single(a => a.Name == "The Police");
    police.Name = "Police, The";

    var avril = context.Artists.Single(a => a.Name == "Avril Lavigne");
    context.Artists.Remove(avril);

    context.SaveChanges();
}

Conclusion

Hopefully by now, you have enough information to get started using the Entity Framework with a SQLite database. For many, many more articles on how to use EF, check out our team's official Getting Started page on MSDN.

53 comments :

Anonymous said...

Very good sample :-)

Initially had a problem getting this sample to work. A quick Google lead me to the below stackoverflow.


http://stackoverflow.com/questions/4225908/error-when-adding-a-configuration-to-app-config-file

Adwen said...

Does this sqlite version work with entity framework 6 alpha ? I cannot get it to work.

Brice Lambson said...

@Adwen, Not yet, but the System.Data.SQLite provider is open source and updating the provider to work with EF6 is as trivial as following instructions for Rebuilding EF providers for EF6.

Jason and Kris Carter said...

Hey @Brice Lambson, I followed that tutorial, but I get the following exception when trying to use EF6.

The 'Instance' member of the Entity Framework provider type 'System.Data.SQLite.SQLiteFactory, System.Data.SQLite, Version=1.0.83.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139' did not return an object that inherits from 'System.Data.Entity.Core.Common.DbProviderServices'. Entity Framework providers must extend from this class and the 'Instance' member must return the Singleton instance of the provider.

Is there some quick wrapper I can write for this, or is it a completely different provider architecture that SQLite will have to provide?

Anonymous said...

I generated a POCO classes from SQL Server using DB-first (contained in their own project library). My SQLite DB has the exact same schema, so I was hoping to reuse those POCO classes. I would think this could work, but EF keeps trying to create the SQLite database and it is throwing a "UnintentionalCodeFirstException" error from OnModelCreating.

I cannot figure out how to stop EF from trying to create the database and have tried "faking" metadata in the connection string as well as setting the context element attribute disableDatabaseInitialization="true".

The SQLite database is set to copy to the output directory (if newer) and I have confirmed it exists under bin\Debug.

Not a big deal, but it is kind of frustrating that I seem unable to keep EF's magic from spreading its pixie dust when I don't want to fly!

Brice Lambson said...

@Anonymous, it sounds like you're mixing the database-first and Code First workflows. You should be able to reuse the POCO classes, but you will need to create a new DbContext from scratch -- not try and reuse the one generated using database-first.

Brice Lambson said...

@Jason/Kris Carter, This sounds a bit like you're trying to use an EF5 provider with EF6. In order to use providers with EF6, they need to be updated first by the provider writers.

Anonymous said...

I would like to use a database in a specific directory, i.e. D:\Data\MyDb.sqlite.
If I put that pathname in the connectionStrings in App.config, I'm able to read data from the database table, but new entities are not saved into its tables after context.SaveChanges().
How can I override this problem?
Thanks.

Unknown said...

My sample:
var post = new Post {Content = "test"};
var post2 = new Post { Content = "test2"};

try
{
using (var context = new TestContext())
{
var sql = "create table Posts(Id int identity(1,1) primary key not null,Content varchar(1200))";
context.Database.ExecuteSqlCommand(sql);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}

try
{
using (var context = new TestContext())
{
context.Posts.Add(post);
context.Posts.Add(post2);

context.SaveChanges();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}

and i receive an error:
System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SQLite.SQLiteException: SQL logic error or missing database
no such table: Posts
at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
at System.Data.SQLite.SQLiteCommand.BuildNextCommand()

Post is:

public class Post
{
[Key]
public int Id { get; set; }
public string Content { get; set; }
}

what i doing wrong? sorry from my english :(

Brice Lambson said...

@Никита, You need to create the database before using the context.

Create the table outside of the context like this:

using (var connection = new SQLiteConnection(myConnectionString))
using (var command = connection.CreateCommand())
{
command.CommandText = "create table Posts(Id int identity(1,1) primary key not null,Content varchar(1200))";

connection.Open();
command.ExecuteNonQuery();
}

Anonymous said...

Thanks for this post!

This was my favorite - as I need to use EF wherever possible on free dbs and on free providers - like SQLite or Postgres with dotConnect Express.

Can you please direct me how can I use this further to set the data in objects as a datasource for Grid control? I want to be able to modify grid data and save it with context.SaveChanges(). I used so far the data adapter but it's really messy code!

Hopefully its not too much to ask!






Brice Lambson said...

@Anonymous, check out Databinding with WinForms.

Anonymous said...

Very nice Brice,

I dont know how can I thank enough.

This page is my must go to page now onwards!

Paul Ruiz Pauker said...

Hi @Brice, nice post, it helped me a lot, but now I'm stuck on Model First aproach with SQLite, could you give me some lights on how to generate database model?

Brice Lambson said...

@Paul, Unfortunately, System.Data.SQLite doesn't yet support DDL generation (see Ticket c8083867b9). I would consider using Devart's provider instead.

Paul Ruiz Pauker said...

Thanks for the reply!.

Finally I've found another way of solving this, and, in case anyone else is looking for the same, I mean: "EF To Sqlite, model first", it's in:

http://code.msdn.microsoft.com/Demo-of-ADONET-POCO-with-140ad3ad

myCollections said...
This comment has been removed by the author.
myCollections said...

Hello Brice,

I just update sqlite provider as you described here :

http://stackoverflow.com/questions/14510096/entity-framework-6-sqlite/15926681#15926681

But i get the same error message as Jason :

"Entity Framework providers must extend from this class and the 'Instance' member must return the Singleton instance of the provider."

Could you help us :)

Thanks a lot for your time.

Regards

Brice Lambson said...

@myCollections myCollections,

Ensure you've removed all references to System.Data.Entity.dll from the provider and your project.

myCollections said...

Hello,

Thanks for your answer.
I have already removed System.Data.Entity.dll from System.Data.SQLite.Linq, System.Data.SQLite and my project.

Added EF6 nightly Build, rebuild both project using .net 4.5.

But without any success :(

HR said...
This comment has been removed by the author.
HR said...

What if my code is in a dll (not exe) and I can't modify the exe's app.config?

Ahmed said...

Really great article :)

Anonymous said...

Hi,
I copied exacly what you wrote in the article.

SQL logic error or missing database no such table: Artist

What am I doing wrong?

Brice Lambson said...

@Anonymous, make sure that the database file is being copied to the bin folder, and that the connection string filename matches the one on disk.

Anonymous said...

I missed it.

Now it works, thanks!

Anonymous said...

Thanks a lot for this post man!!!

Ryan Stowasser said...

Thank you for this article, this has worked great for me until now.

I'm creating some reports from a sqlite database that doesn't use the naming scheme of AlbumId for and Album object. How do I specify a different field to load an object? An example would be Tickets.AssignedTo is an id that needs to load a User object?

Brice Lambson said...

@Ryan Stowasser, I'm not entirely sure how you want your classes to look, but I'll do my best to reply.

You'll need to configure the relationship by overriding OnModelCreating in your DbContext and adding code similar to the following.

modelBuilder.Entity<Ticket>().HasOptional(t => t.AssignedToUser).WithMany(u => u.Tickets).HasForeignKey(t => t.AssignedTo);

For additional information, see Configuring Relationships with the Fluent API.

Ryan Stowasser said...

Thank you Brice, that was very helpful to me.

Anonymous said...

Hi Brice, this is a very interesting topic!

I'm not that familiar with Sqlite and entity framework, so I was wondering how it is done within a WPF application (data binding..)

Thanks in advance!

Brice Lambson said...

@Anonymous, here is an article on Entity Framework and Databinding with WPF.

Sylvia said...

Hi,I have a question,I do the same thing with this article,but it show error "could not load sqlite.interop.dll",I installed the system.data.sqlite(x86 and x64) using PMD in vistual studio 2012,could you help me

Brice Lambson said...

@Sylvia, I hit the same issue when writing this post; that's why I suggest using the System.Data.SQLite.x86 package.

Alex said...

Hi i keep getting "context has changed since the database was created" error. Whats the equivalent of text field for ef

Alex said...
This comment has been removed by the author.
Alex said...

Oh forget that last comment i was still connecting to my SqlDB and I had turned of the database initializer. I still cant figure why the connection isn't working though. I made all the connection strings and i can even access it via the Server Explorer.

is there anyway to post my app.config to see what the problem is?

Bill said...

I have a working set up for NHibernate 3 and SQLite and everything is working exactly the way I want: create an empty database, create the tables, populate the database, etc.

Now I want to do the same thing with EF5. Using your tutorial as a starting point, I can get a new database created, but I cannot figure out how to get EF5 to create the tables for me. This should be just as transparent as NHIbernate, as I am using the same SQLite driver.

What do you suggest?

Thanks,
Bill

butteredtoast said...

I am having troubles referencing the sample database using a Web API interface (verses the console application). I am using the select method in my GET function and I am getting a "SQL logic error or missing database no such table: Artist" error. Any ideas?

Brice Lambson said...

@Bill, as I mentoned to Paul above, "Unfortunately, System.Data.SQLite doesn't yet support DDL generation (see Ticket c8083867b9). I would consider using Devart's provider instead."

@butteredtoast, Make sure your connection string is what you expect it to be at runtime (use db.Database.Connection.ConnectionString). If it uses "|DataDirectory|" make sure the database file is inside the App_Data directory.

Anonymous said...

I'm currently using SQL Express and EF "database first", but am considering switching to SQLite.

Does SQLite support database first? And do you know of any tools that would let me export my SQL database (schema and data) into a SQLite database?

Brice Lambson said...

@Anonymous As far as I know, it supports database first. You'll need to download the SQLite design-time components for Visual Studio. I'm not sure about any migration tools, but you might try asking on StackOverflow.com or ServerFault.com.

Unknown said...

I spent a LOT of time getting this working in a class library as a plug-in to a third-party product. All kinds of issues with not being able to set connect strings or DbProviders due to the dll not referencing the config. There are work-arounds but they're messy and annoying - eg as having to deploy the SQLite dlls to the parant application's folder and manipulating the machine.config programmatically. Blech. (Why Microsoft won't allow class libraries to (optionally) refer to their own config at run time escapes me and I think quite a few other people as there are definitely times when it would be preferable.) These problems are not specific to SQLite/Data Entity/System.Data.SQLite, btw.

Unknown said...

Great thanks for the great start up example! Now I'm learning EF from Julie Lerman's "Learning Entity Framework"

Unknown said...
This comment has been removed by the author.
Unknown said...

I have created a new database file. Using that I'am getting
System.Data.Entity.ModelConfiguration.ModelValidationException

Is it because if the wrong use of the types in my model classes. Is there any way to see the .sqlite file. I have an sqlite editor but it is not opening the database file

Unknown said...

Hey Brice.

Say i want to use another database.
The classes i create need to be identical to the database structure?

EF will connect each property to the table field by the name?

Unknown said...
This comment has been removed by the author.
Anonymous said...

I use with sqlite a free tool Valentina Studio at the moment. Does everything you need, and does it very well. http://www.valentina-db.com/en/valentina-studio-overview

Unknown said...

Foriegn Key relation doesn't work. When I do Artist.Albums, it throws ObjectDisposedException

Anonymous said...

Hi Brice,
Thanks for the article.

But I am getting below exception.

The type initializer for 'System.Data.Entity.Internal.AppConfig' threw an exception.

Anonymous said...

all the async extensions seem to throw "InvalidCastException".
E.g.:
await Context.Customers.LoadAsync();

is there a workaround? thanks.

Anonymous said...

Could you please add a new example of how to accomplish this with the updated versions? I have spent hours trying and keep getting various errors. Or would you atleast be able to post the full working App.Config file? As I believe that is where all the errors are coming from.