Click here to Skip to main content
15,867,756 members
Articles / Programming Languages / C#

SQLite with C#.Net and Entity Framework

Rate me:
Please Sign up or sign in to vote.
4.71/5 (18 votes)
2 Mar 2017CPOL2 min read 144.4K   48   27
The easiest way to use Entity Framework with SQLite databases in C# Winform and Console Applications

Introduction

This article is to use your SQLite Database with entity framework, I am writing this article because i was unable to use SQLite's Entity framework tools i.e. Entity Framework tool from sqlite.org was not worked in my system may be in yours too. 

As you may be familiar with Entity Framework is a best tool for developers. Thats why i commited to write this article. And Sorry for my bad english :)

So in this article you will be able to use sqlite with entity framework .

Background

You will need System.Data.Sqlite  from nuget package manager. 

Type PM>Install-Package System.Data.SQLite

Then you need any Sqlite Database Management Tool. 

 

Using the code

Create a SQLite database for example 'SQLiteWithEF.db'

Create a table :

CREATE TABLE EmployeeMaster (

ID INTEGER PRIMARY KEY AUTOINCREMENT

UNIQUE,

EmpName VARCHAR NOT NULL,

Salary DOUBLE NOT NULL,

Designation VARCHAR NOT NULL

);

Now create a Console Application and add reference to

  1.  System.Data.SQLite from Nuget package manager. 
  2. System.Data.Linq

Note: It will make some entries in your App.Config file. Just remove all of them, except to default entries.

Now your App.Config file will looks like:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
</configuration>

Now create a Class named SQLiteConfiguration.cs and write some code

C#
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Core.Common;
using System.Data.SQLite;
using System.Data.SQLite.EF6;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SQLiteWithEF
{
    public class SQLiteConfiguration : DbConfiguration
    {
        public SQLiteConfiguration()
        {
            SetProviderFactory("System.Data.SQLite", SQLiteFactory.Instance);
            SetProviderFactory("System.Data.SQLite.EF6", SQLiteProviderFactory.Instance);
            SetProviderServices("System.Data.SQLite", (DbProviderServices)SQLiteProviderFactory.Instance.GetService(typeof(DbProviderServices)));
        }
    }
}

Note: You may be thinking that why i have removed entries from App.Config file and creating this configuration class or whatever... The answer is : It looks very easy to configure Entity framework via code. And App.Config file even don't show any suggestion to what assembly we are using.

Now create your model class for the table EmployeeMaster like this

C#
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Data.Linq.Mapping;
using System.Data.SQLite;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SQLiteWithEF
{
    [Table(Name = "EmployeeMaster")]
    public class EmployeeMaster
    {
        [Column(Name = "ID", IsDbGenerated = true, IsPrimaryKey = true, DbType = "INTEGER")]
        [Key]
        public int ID { get; set; }

        [Column(Name = "EmpName", DbType = "VARCHAR")]
        public string EmpName { get; set; }

        [Column(Name = "Salary", DbType = "DOUBLE")]
        public double Salary { get; set; }

        [Column(Name = "Designation", DbType = "VARCHAR")]
        public string Designation { get; set; }
    }
}

Now create DatabaseContext.cs class and extend it with DbContext class  like this

C#
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SQLiteWithEF
{
    class DatabaseContext : DbContext
    {
        public DatabaseContext() :
            base(new SQLiteConnection()
            {
                ConnectionString = new SQLiteConnectionStringBuilder() { DataSource = "D:\\Databases\\SQLiteWithEF.db", ForeignKeys = true }.ConnectionString
            }, true)
        {
        }
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            base.OnModelCreating(modelBuilder);
        }

        public DbSet<EmployeeMaster> EmployeeMaster { get; set; }
    }
}

Here look at connection string, you have to create the connection string through code. If you are going to create it in App.Config file, it will not work.

Since  we have created only one table in databse thats why you are seeing only one DbSet property, If you have multiple table, then you have to write DbSet for each tables that  exist in your database or as required by your application.

Now we have created our entity framework context class. we can use it now.

So goto Program.cs file and try to insert update delete and query data through Lembda or Linq or both.

See example

C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SQLiteWithEF
{
    class Program
    {
        static void Main(string[] args)
        {
            DatabaseContext context = new DatabaseContext();
            Console.WriteLine("Enter Employee name");
            string name = Console.ReadLine();
            Console.WriteLine("Enter Salary");
            double salary = Convert.ToDouble(Console.ReadLine());
            Console.WriteLine("Enter Designation");
            string designation = Console.ReadLine();
            EmployeeMaster employee = new EmployeeMaster()
            {
                EmpName = name,
                Designation = designation,
                Salary = salary
            };
            context.EmployeeMaster.Add(employee);
            context.SaveChanges();

            var data = context.EmployeeMaster.ToList();
            foreach (var item in data)
            {
                Console.Write(string.Format("ID : {0}  Name : {1}  Salary : {2}   Designation : {3}{4}", item.ID, item.EmpName, item.Salary, item.Designation, Environment.NewLine));
            }

            Console.ReadKey();
        }
    }
}

Now try to run the application by pressing F5 or Control+F5 and see the result :)

Points of Interest

  1. Entity Framework always treates ID column as primary key, If you have declared any other column as primary key then you have to annotate it with [Key] attribute of
<code>System.ComponentModel.DataAnnotations</code>

       2. Keep your SQLiteConfiguration.cs file in same folder where your context class is.

       3. You may notice that i have removed a convention to Pluralize the table names from DatabaseContext class

C#
protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            base.OnModelCreating(modelBuilder);
        }

If you will remove this line you will get  

C#
System.Data.Entity.Infrastructure.DbUpdateException

      4. While using the libraries read the terms and conditions of their licenses.

History

There is no updates yet.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
India India
/*life runs on code*/
So keep coding...

Comments and Discussions

 
Question.NET 7 Pin
roberto bandiera16-Mar-23 21:02
roberto bandiera16-Mar-23 21:02 
GeneralMy vote of 5 Pin
roberto bandiera16-Mar-23 14:17
roberto bandiera16-Mar-23 14:17 
QuestionDo we have to manually create the classes? Pin
Interrobang29-May-21 5:45
Interrobang29-May-21 5:45 
AnswerRe: Do we have to manually create the classes? Pin
Claire Streb13-Nov-21 16:19
Claire Streb13-Nov-21 16:19 
QuestionMessage Closed Pin
21-May-21 0:43
Günter Althoff21-May-21 0:43 
QuestionException unhandled at VS 2019 Pin
Member 150446772-Feb-21 19:49
Member 150446772-Feb-21 19:49 
AnswerRe: Exception unhandled at VS 2019 Pin
Awesh Vishwakarma15-Feb-22 21:23
professionalAwesh Vishwakarma15-Feb-22 21:23 
QuestionThank you! The magic base class call fixed it. Pin
Poikilos19-Dec-20 19:43
Poikilos19-Dec-20 19:43 
QuestionGreat example! Pin
alejandro207124-Sep-20 10:10
alejandro207124-Sep-20 10:10 
PraiseMy Vote Of 5 Pin
Szymon Roslowski3-Sep-20 22:29
professionalSzymon Roslowski3-Sep-20 22:29 
QuestionHow to design models for relational tables Pin
Khandagale Prashant9-Apr-19 18:41
Khandagale Prashant9-Apr-19 18:41 
QuestionBlog translation request Pin
Member 1382941115-May-18 21:26
Member 1382941115-May-18 21:26 
QuestionException Pin
Asım Gündüz6-Apr-18 2:36
Asım Gündüz6-Apr-18 2:36 
PraiseAwesome code! Pin
Daniel Kamisnki25-Feb-18 13:56
Daniel Kamisnki25-Feb-18 13:56 
QuestionI got an exception Pin
AlekhyaDevisetty19-Jan-18 21:59
AlekhyaDevisetty19-Jan-18 21:59 
AnswerRe: I got an exception Pin
Awesh Vishwakarma7-Feb-18 7:04
professionalAwesh Vishwakarma7-Feb-18 7:04 
QuestionI get a null error Pin
dommy1A20-Oct-17 5:28
dommy1A20-Oct-17 5:28 
AnswerRe: I get a null error Pin
Awesh Vishwakarma6-Nov-17 7:45
professionalAwesh Vishwakarma6-Nov-17 7:45 
QuestionCreating tables without raw sql Pin
Member 132953641-Aug-17 20:01
Member 132953641-Aug-17 20:01 
Questionthat's great!!! Pin
ginocic14-Jun-17 22:23
ginocic14-Jun-17 22:23 
AnswerRe: that's great!!! Pin
Awesh Vishwakarma23-Jun-17 3:44
professionalAwesh Vishwakarma23-Jun-17 3:44 
GeneralRe: that's great!!! Pin
ginocic23-Jun-17 5:55
ginocic23-Jun-17 5:55 
GeneralRe: that's great!!! Pin
Awesh Vishwakarma23-Jun-17 7:29
professionalAwesh Vishwakarma23-Jun-17 7:29 
QuestionEF7 with improved SqLite support Pin
stefaneidelloth22-Mar-17 7:14
stefaneidelloth22-Mar-17 7:14 
AnswerRe: EF7 with improved SqLite support Pin
Awesh Vishwakarma23-Mar-17 17:36
professionalAwesh Vishwakarma23-Mar-17 17:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.