SQLite with C#.Net and Entity Framework

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

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

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

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

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

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

If you will remove this line you will get  

System.Data.Entity.Infrastructure.DbUpdateException

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

History

There is no updates yet.