SQLite Entity Framework Database First Tutorial

This tutorial describes how to use SQLite database-first method with Visual Studio 2013 and Entity Framework 6. This article is based on Tomasz Maciejewski topic SQLite and Entity Framework with Visual Studio Express 2013, which shows how to generate EDMX. That method works but Visual Studio says that EDMX contains errors (though it compiles). This article's method doesn't produce errors.

I will use Visual Studio 2013, SQLite 1.0.94.0, Entity Framework 6.1.1, .NET Framework 4.5, and targeting both 32 and 64 bit platforms.

Let's start from scratch and create database for a simplest blog. There will be only posts and comments.

I used SQLiteStudio to create database. These are the tables saved in "Blog.sqlite" database:

CREATE TABLE Posts
(
    Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    Created DATETIME NOT NULL,
    Text NTEXT NOT NULL
);

CREATE TABLE Comments
(
    Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    Created DATETIME NOT NULL,
    PostId INTEGER NOT NULL REFERENCES Posts (Id),
    Text NTEXT NOT NULL
);

I'll create a console application for simplicity.

Next step is to create Entity Framework schema files. They are used to represent conceptual and store schemas and mapping between them. You'll need three things:

1. Database file.
2. EdmGen utility.
3. SQLite.

They should be copied into the same directory.

Database is "Blog.sqlite" we've just created.

EdmGen is .NET utility located in "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\EdmGen.exe". Copy it to "the same" directory and create "EdmGen.exe.config" file with the following content:

<configuration>
    <system.data>
        <DbProviderFactories>
            <remove invariant="System.Data.SQLite" />
            <add name="SQLite Data Provider"
                invariant="System.Data.SQLite"
                description=".NET Framework Data Provider for SQLite"
                type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
        </DbProviderFactories>
    </system.data>
</configuration>

Last thing is SQLite. Download precompiled binaries the same as your system (32 or 64 bit), for example Precompiled Binaries for 64-bit Windows (.NET Framework 4.5). If you need both 32 and 64 bit support you can do as the following. Download both 32 and 64 bit versions, extract one of them from ZIP, then delete "SQLite.Interop.dll", then create "x86" and "x64" directories and put 32 bit "SQLite.Interop.dll" version into "x86" directory and 64 bit version in "x64".

Now create "gen.bat" file with the following command:

EdmGen.exe /mode:fullgeneration /c:"Data Source=Blog.sqlite" /provider:System.Data.SQLite /entitycontainer:Blog /project:Blog /language:CSharp

Run "gen.bat" and the following files will be created: "Blog.csdl", "Blog.msl", "Blog.ssdl", "Blog.ObjectLayer.cs", "Blog.Views.cs":

EdmGen for Microsoft (R) .NET Framework version 4.5
Copyright (C) Microsoft Corporation. All rights reserved.

Loading database information...
Writing ssdl file...
Creating conceptual layer from storage layer...
Writing msl file...
Writing csdl file...
Writing object layer file...
Writing views file...

Generation Complete -- 0 errors, 0 warnings

Copy these 5 generated files into project directory into "DAL" folder. Create "Data" folder in the project and copy "Blog.sqlite" inside. Add these files into project. And add reference to "System.Data.Entity" assembly.

For the test, in Visual Studio go to properties of "Blog.sqlite" and set "Copy to Output Directory" parameter value to "Copy always". This option will copy empty database from project into compilation directory and you always will have empty database for every application run from VS.

The go to properties of schema files "Blog.csdl", "Blog.msl", "Blog.ssdl" and set "Build action" to "Embedded Resource" for each file. This puts them into application resources so that Entity Framework could load them.

The last thing is left. Right click on project in Solution Explorer and choose "Manage NuGet Packages...". Click "Online" in left panel and type "sqlite" in top right search field. Install "System.Data.SQLite (x86/x64)" package. This will change "app.config" file. And it will change it wrong! You have to reorder some string to get them in the following way (note the order of "remove" and "add" tags):

<DbProviderFactories>
    <remove invariant="System.Data.SQLite" />
    <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
    <remove invariant="System.Data.SQLite.EF6" />
    <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
</DbProviderFactories>

One thing more went wrong after adding SQLite package. "System.Data.Entity" reference disappeared. Add this reference again :). And also add reference to "System.Runtime.Serialization". Now project can be compiled without errors (but it still does nothing). After compilation you'll notice that you haven't "SQLite.Interop.dll" file in destination directory, but you have two directories "x86" and "x64" containing this file for 32 and 64 bit systems. It's because "System.Data.SQLite (x86/x64)" for both 32 and 64 bit systems was installed.

Remember CSDL, SSDL and MSL schemas was added into resources. It's time to use them. EF needs a connection string that specifies all the things such as schemas, database source and data provider. Schemas are being added in resources with full name, for example "EF_SQLite_Example.DAL.Blog.csdl" for CSDL in this project. Add the following connection string in "app.config":

<connectionStrings>
    <add name="Blog"
        connectionString="metadata=res://*/EF_SQLite_Example.DAL.Blog.csdl|res://*/EF_SQLite_Example.DAL.Blog.ssdl|res://*/EF_SQLite_Example.DAL.Blog.msl;provider=System.Data.SQLite;provider connection string=&quot;data source=./Data/Blog.sqlite&quot;"
        providerName="System.Data.EntityClient" />
</connectionStrings>

Where "res://" means that schema will be taken from resources, and "*" means "current application". It's also can be written this way:

<connectionStrings>
    <add name="Blog"
        connectionString="metadata=res://*/;provider=System.Data.SQLite;provider connection string=&quot;data source=./Data/Blog.sqlite&quot;"
        providerName="System.Data.EntityClient" />
</connectionStrings>

This means that schemas will be searched in all resource files. This is shorter but a bit slower and inaccurate (you can have multiple schemas).

Add reference to "System.Configuration" to load connection string from config.

Now SQLite with EF6 can be used. Let's add some records into DB and read them.

using System;
using System.Configuration;
using System.Linq;
using Blog;

namespace EF_SQLite_Example
{
    class Program
    {
        static void Main(string[] args)
        {
            var connectionString = ConfigurationManager.ConnectionStrings["Blog"].ConnectionString;

            Console.WriteLine("Writing into database.");
            using (var ctx = new Blog.Blog(connectionString))
            {
                var post = new Posts
                {
                    Created = DateTime.UtcNow,
                    Text = "Example of post in this blog.",
                };

                var comment1 = new Comments
                {
                    Created = DateTime.UtcNow,
                    Text = "Comment 1 for the post.",
                };
                var comment2 = new Comments
                {
                    Created = DateTime.UtcNow,
                    Text = "Comment 2 for the post.",
                };
                
                post.Comments.Add(comment1);
                post.Comments.Add(comment2);

                ctx.Posts.AddObject(post);

                ctx.SaveChanges();

                Console.WriteLine("Post: Id = {0}", post.Id);
                Console.WriteLine("Comment 1: Id = {0}, PostId = {1}", comment1.Id, comment1.PostId);
                Console.WriteLine("Comment 2: Id = {0}, PostId = {1}", comment2.Id, comment2.PostId);
            }

            Console.WriteLine("\r\nReading from database.");
            using (var ctx = new Blog.Blog(connectionString))
            {
                var posts = ctx.Posts.Where(r => r.Comments.Any(r2 => r2.Text.Contains("2"))).ToList();

                foreach (var postItem in posts)
                {
                    Console.WriteLine("Post: Id = {0}", postItem.Id);
                }
            }

            Console.WriteLine("\r\nEND");
            Console.ReadKey();
        }
    }
}

Produces output:

Writing into database.
Post: Id = 1
Comment 1: Id = 1, PostId = 1
Comment 2: Id = 2, PostId = 1

Reading from database.
Post: Id = 1

Don't forget to regenerate EF schemas and models after changing database scheme.

View project source code at Bitbucket.