SQLite and Entity Framework using Visual Studio 2015 ( .NET 4.6) : Model first approach

This tutorial demonstrates how to use Entity Framework 6.0 to Insert, Update and Delete from SQLite database with Model first approach.

Let’s begin.

Create a new console project

Start by creating a console application. Let’s call it SqliteEf6
consoleapp

 

Add reference to SQLite libraries

Go to Nuget Package manager for the newly created project and search for “sqlite”. (Make sure you are in “Browse” tab. by default it opens in Installed tab.) Install “System.Data.SQLite” package. This package contains the Core libraries as well as Linq and EF support for Sqlite.

nugetinstall

Update Entity Framework to 6.0 ( Optional )

This step is optional. It will work without upgrading EF to 6.0.
You can go to “Updates” tab of nuget manager and upgrade EF.

Installing SQLite design-time components for Visual Studio 2015

This step is essential if you are opting for Model first approach. Without it you won’t be able to see an option for “Sqlite database” when you try to add a new Model from database in your project.

You won’t be able to see below option ( as shown in Screenshot ) without design time component for Visual studio.

modelWizard

To get the above option you will need to install “Setups for 32-bit Windows (.NET Framework 4.6)” from Sqlite Site

Search for this text on the page “This is the only setup package that is capable of installing the design-time components for Visual Studio 2015. ” and install “sqlite-netFx46-setup-bundle-x86-2015-1.0.102.0.exe“. You will notice that this is a 32bit version of the library. It seems that you will need this version even though you have 64bit VS.

Note: if you are using older .Net version  and VS version, there are setup for them as well on the same page. Choose appropriately.

Now install the setup. You will see one option show below. Don’t forget to check it.

2015DesignerComponents

And wait for the setup to finish. It might take a while. And that is all there is to the configuration/installation part.

Adding ADO.NET entity Data Model

Right click on your project, go to “Add” -> “New Item” and Choose “ADO.NET Entity Data Model” and add a Model named “SqliteEF6Model”. Follow the steps below.

SqliteEF6Model

 

Choose the first option “EF Designer from database“:

SqliteEF6Model2

 

Click on “New Connection…“:

SqliteEF6Model3

 

Choose “System.Data.SQLite Database File” as Data Source (you won’t see  this option if you haven’t installed the design time components as mentioned in previously):

SqliteEF6Model4

 

Browse for the “Sqlite” database file you want to use. Here is the demo database file that I am using in this tutorial.  Demo Sqlite file: SqliteEF.sqlite

It already contains some data to play with.

SqliteEF6Model5

 

Choose the objects you want to be included in your model:

SqliteEF6Model6

 

Click finish  and you will see the designer and edmx file in solution explorer as below.

SqliteEF6Model7

 

Fetch , Insert and Delete data:

The code snippet below is self explanatory.

using (var entities = new SqliteEFEntities())
 {
 // get employees whole salary is more than 9000
 var emps = entities.Employees.Where(e => e.Salary > 9000);
 foreach (var employee in emps)
 {
 Console.WriteLine(employee.FirstName + " " + employee.LastName);
 }

// Insert new Employee
 var johnDoe = new Employee
 {
 FirstName = "John",
 LastName = "Doe",
 Salary = 8000
 };
 entities.Employees.Add(johnDoe);
 if (entities.SaveChanges() > 0)
 {
 Console.WriteLine("John doe added");
 }

// Delete john doe
 entities.Employees.Remove(johnDoe);
 entities.SaveChanges();
 Console.WriteLine("John doe removed");
 }

Output:

output

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Bitnami