How to use SQLite with Entity Framework 6 in WPF Application

When you are looking for a free and light weight tool to store your data locally SQLite is the best alternative to go for, SQLite is a small, fast and reliable database which can be used by end user without any installation requirement.

Getting Started:

Here are the steps you need to follow:

1> Create File->New Project -> WPF Application.

2> Right click on your project -> Properties -> Build Tab -> Change Platform Target to x86 and build solution.

3> Now  you first need to install Entity Framework via nuget.

Go to Tools -> Library Package Manager - > Package Manager Console, 

Your Package Mangager Console will open, paste the below line and hit enter.

Install-Package EntityFramework

It will install latest version of Entity Framework from nuget in your application.

Also you need to have SQLite to be install in your application, so paste the below line in your PMC and hit enter.

Install-Package System.Data.SQLite

4> Now as SQLite and EF is reference in our application, we need to create SQLite  database to use with our application.

There is a good Firefox plugin "SQLite Manger" for creating and managing SQLite database.

I have created a database name "MyDB", with one table "UserInfo", below is the structure of this table:


CREATE TABLE "UserInfo" ("ID" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "FirstName" TEXT, "LastName" TEXT)

Also add few records in the table, so that you can view that in your application.


5> Once your database is created, In your application add new folder give it any name say "DataFile" and put your SQLite database here in this folder.

Now right click your database file -> Properties -> Select "Copy if newer" in Copy to Output Directory field and build project.


6> Open MainWindow.xaml and insert below code in your grid.


<DataGrid x:Name="dataGrid" HorizontalAlignment="Stretch" VerticalAlignment="Stretch" />

7> Go to MainWindow.cs file and add following code(This is code base configuration instead of configuring in app.config):

        public class SQLiteConnectionFactory : IDbConnectionFactory
        {
            public DbConnection CreateConnection(string nameOrConnectionString)
            {
                return new SQLiteConnection(nameOrConnectionString);
            }
        }

        public class SQLiteConfiguration : DbConfiguration
        {
            public SQLiteConfiguration()
            {
                SetDefaultConnectionFactory(new SQLiteConnectionFactory());
                SetProviderFactory(&quot;System.Data.SQLite&quot;, SQLiteFactory.Instance);
                SetProviderFactory(&quot;System.Data.SQLite.EF6&quot;, SQLiteProviderFactory.Instance);
                Type t = Type.GetType(&quot;System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6&quot;);
                FieldInfo fi = t.GetField(&quot;Instance&quot;, BindingFlags.NonPublic | BindingFlags.Static);
                SetProviderServices(&quot;System.Data.SQLite&quot;, (DbProviderServices)fi.GetValue(null));
            }
        }


8> We need to create a class that represent our UserInfo table, so here is the class for that:

        [Table("UserInfo")]
        public class User
        {
            public int ID { get; set; }
            public string FirstName { get; set; }
            public string LastName { get; set; }
        }


9> No we need to create DataContext of use Entity Framework with Sqlite:

         public class MyDBContext : DbContext
        {
            public MyDBContext()
                : base(new SQLiteConnection()
                {
                    ConnectionString =
                        new SQLiteConnectionStringBuilder() { DataSource = "I:\\WPF\\MyDB.sqlite"}
                        .ConnectionString
                }, true)
            {

            }
            public DbSet<User> Users { get; set; }
        }


You can see that I have give static path of my sqlite database in DataSource field, but you can give relative path using Assembly.GetExecutingAssembly() method.


10> As our DataContext is created, we need to fetch record from the database to display in our datagrid, so write this below code in your MainWindow_Loaded:

                MyDBContext context = new MyDBContext();
                context.Users.OrderBy(c => c.FirstName).Load();
                this.dataGrid.ItemsSource = context.Users.Local;


11> Now run the application and you will see that all your records in database file will be display in datagrid.


Happy Coding :)