Using SQLite database in .NET with LINQ to SQL and Entity Framework 6

SQLite is an open-source SQL database engine. It is widely used in iOS apps. The database is available in the form of a file.

Create Database

To create a database with name accounting:

sqlite3 accounting.sqlite

.tables is a command which lists all tables. Add a new table, Company. And insert a few rows.

CREATE TABLE company 
(id int, seats int);
INSERT INTO company (id, seats)
VALUES (1, 20);
INSERT INTO company (id, seats)
VALUES (2, 30);
INSERT INTO company (id, seats)
VALUES (3, 50);

ALTER TABLE has restricted syntax. It does not have an option to add a primary key. So, if you want to add a primary key, do it at the time of table creation.

LINQ to SQL

SQLite has a NuGet package for .NET. There are two ways to connect to the database, using LINQ to SQL or Entity Framework.

LINQ to SQL has a convenient mapping notation using attributes. Attributes are a convenient way to map the tables and columns in the database to .NET classes.

  1. TableAttribute – maps the table name to a .NET class.
  2. ColumnAttribute – maps a column name to a .NET property.

Create a entity class named Company as follows:

[Table(Name="company")]
class Company
{
    [Column(Name="id")]
    public int Id { get; set; }

    [Column(Name = "seats")]
    public int Seats { get; set; }
}

LINQ to SQL by default works with SQL Server. To help LINQ to SQL work with SQLite, pass the connection object to create the DataContext object.

static void Main(string[] args)
{
    var connection = new SQLiteConnection(
        @"Data Source=C:\iOS\Accounting101\Accounting.sqlite");
    var context = new DataContext(connection);

    var companies = context.GetTable<Company>();
    foreach(var company in companies)
    {
        Console.WriteLine("Company: {0} {1}", 
            company.Id, company.Seats);
    }
    Console.ReadKey();
}

The connection string for SQLite database is simple. It has the format of Data Source=<Path of the file>. The connection string is the only difference from any other LINQ to SQL implementation.

Entity Framework

Other than LINQ to SQL, Entity Framework is available for use. In web.config, the following configuration sections should be available.

<entityFramework>
	<providers>
		<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
		<provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
	</providers>
</entityFramework>
<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" />
		<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>
</system.data>

The connection string name should be passed to the context class.

public class MyDbContext : DbContext
{
    public MyDbContext() : base("name=MyDbCS") { }
    public DbSet<Employee> Employees { get; set; }
}

The connection string is specified in the config file. It is very similar to the connection string used in LINQ to SQL.

<connectionStrings>
	<add name="MyDbCS" connectionString="Data Source=|DataDirectory|\Accounting.sqlite" providerName="System.Data.SQLite"/>
</connectionStrings>

Table and Column attributes are used to map the database table and columns to a .NET class. The additional attribute, DatabaseGenerated, indicates if the primary key is auto-generated or not. By default, the key is auto-generated.

[Table(Name="company")]
class Company
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [Column(Name="id")]
    public int Id { get; set; }

    [Column(Name = "seats")]
    public int Seats { get; set; }
}

 

Related Posts

One thought on “Using SQLite database in .NET with LINQ to SQL and Entity Framework 6

  1. I tried using linq to sql as you show but I can’t compile it because DataContext is missing.
    How does this work using current versions of System.Data.Sqlite?

Leave a Reply

Your email address will not be published.