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.- Open Visual Studio
- Select File -> New -> Project...
- Select Console Application
- Name the project
- 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.- Select Tools -> Library Package Manager -> Package Manager Console
- Inside the Package Manager Console (PMC) run Install-Package EntityFramework
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.- Inside PMC, run Install-Package System.Data.SQLite.x86
<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!- Download and extract the SQLite version of the Chinook Database
- Select Project -> Add Existing Item...
- Browse to the folder where you extracted the database
- Select All Files from the dropdown next to File name
- Select the Chinook_Sqlite_AutoIncrementPKs.sqlite file
- Click Add
- Select the file in Solution Explorer
- In Properties, set Copy to Output Directory to Copy if newer
<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(); }
53 comments :
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
Does this sqlite version work with entity framework 6 alpha ? I cannot get it to work.
@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.
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?
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!
@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.
@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.
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.
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 :(
@Никита, 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();
}
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!
@Anonymous, check out Databinding with WinForms.
Very nice Brice,
I dont know how can I thank enough.
This page is my must go to page now onwards!
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?
@Paul, Unfortunately, System.Data.SQLite doesn't yet support DDL generation (see Ticket c8083867b9). I would consider using Devart's provider instead.
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
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
@myCollections myCollections,
Ensure you've removed all references to System.Data.Entity.dll from the provider and your project.
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 :(
What if my code is in a dll (not exe) and I can't modify the exe's app.config?
Really great article :)
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?
@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.
I missed it.
Now it works, thanks!
Thanks a lot for this post man!!!
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?
@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.
Thank you Brice, that was very helpful to me.
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!
@Anonymous, here is an article on Entity Framework and Databinding with WPF.
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
@Sylvia, I hit the same issue when writing this post; that's why I suggest using the System.Data.SQLite.x86 package.
Hi i keep getting "context has changed since the database was created" error. Whats the equivalent of text field for ef
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?
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
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?
@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.
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?
@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.
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.
Great thanks for the great start up example! Now I'm learning EF from Julie Lerman's "Learning Entity Framework"
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
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?
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
Foriegn Key relation doesn't work. When I do Artist.Albums, it throws ObjectDisposedException
Hi Brice,
Thanks for the article.
But I am getting below exception.
The type initializer for 'System.Data.Entity.Internal.AppConfig' threw an exception.
all the async extensions seem to throw "InvalidCastException".
E.g.:
await Context.Customers.LoadAsync();
is there a workaround? thanks.
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.
Post a Comment