Entity Framework and SQLite, the ultimate how-to

Question

I'm trying to get Entity Framework (6.4.4. the newest version in summer 2020) working together with SQLite (1.0.113.1, also latest in summer 2020).

I found a lot of information about how to do this, but this information was not always helpful, quite often they contradicted each other.

Now that I found out how to do it, I decided to jot down how I did it.

The question describes the classes and the tables, the answer will describe how to do it.

I describe a database for Schools, where every School has zero or more Students and Teachers (one-to-many), every Student and every Teacher has exactly one Address (one-to-one), Teachers teach zero or more Students, while Students are taught by zero or more teachers (many-to-many)

So I have several tables:

• A simple one: Addresses
• A simple one: Schools
• Students with a foreign key to the School they attend
• Teachers with a foreign key to the School they teach on.
• TeachersStudents: the junction table to implement the many-to-many relation between Students and Teachers

The classes:

Address and School:

public class Address
{
    public long Id { get; set; }
    public string Street { get; set; }
    public int Number { get; set; }
    public string Ext { get; set; }
    public string ExtraLine { get; set; }
    public string PostalCode { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
}

public class School
{
    public long Id { get; set; }
    public string Name { get; set; }

    // Every School has zero or more Students (one-to-many)
    public virtual ICollection<Student> Students { get; set; }

    // Every School has zero or more Teachers (one-to-many)
    public virtual ICollection<Teacher> Teachers { get; set; }
}

Teachers and Students:

public class Teacher
{
    public long Id { get; set; }
    public string Name { get; set; }

    // Every Teacher lives at exactly one Address
    public long AddressId { get; set; }
    public virtual Address Address { get; set; }

    // Every Teacher teaches at exactly one School, using foreign key
    public long SchoolId { get; set; }
    public virtual School School { get; set; }

    // Every Teacher Teaches zero or more Students (many-to-many)
    public virtual ICollection<Student> Students { get; set; }
}

public class Student
{
    public long Id { get; set; }
    public string Name { get; set; }

    // Every Student lives at exactly one Address
    public long AddressId { get; set; }
    public virtual Address Address { get; set; }

    // Every Student attends exactly one School, using foreign key
    public long SchoolId { get; set; }
    public virtual School School { get; set; }

    // Every Student is taught by zero or more Teachers (many-to-many)
    public virtual ICollection<Teacher> Teachers { get; set; }
}

And finally the DbContext:

public class SchoolDbContext : DbContext
{
    public DbSet<Address> Addresses { get; set; }
    public DbSet<School> Schools { get; set; }
    public DbSet<Student> Students { get; set; }
    public DbSet<Teacher> Teachers { get; set; }
}

When using entity framework you don't need to define the Junction table TeachersStudents in your DbContext. Of course this doesn't mean that you won't need it.

If you use Microsoft SQL server this would have been enough to let entity framework identify the tables and the relations between the tables.

Alas, with SQLite this is not enough.

So: how to get this working. On to the answer!

Answer 1

So I used Visual Studio to create an empty solution and added a DLL project: SchoolSQLite. To see if this works I also added a console application that would access the database using entity framework.

To be complete I added some unit tests. This is out-of-scope of this answer.

In the DLL project I used References-Manage NUGET Packages to search for System.Data.SQLite. This is the version that adds both the code needed for Entity Framework and SQLite. If needed: update to the newest version.

Add the classes described in the question: Address, School, Teacher, Student, SchoolDbContext.

Now comes the part that I found most difficult: the connection string in file App.Config of your console App.

To get it working I needed the following parts in App.Config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <configSections>
        <!-- For more information on Entity Framework configuration, visit ... -->
        <section name="entityFramework"
        type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, 
        Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
        requirePermission="false"/>
    </configSections>

Later in App.Config the section EntityFramework:

<entityFramework>
  <providers>
    <provider invariantName="System.Data.SqlClient" 
      type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer"/>
    <provider invariantName="System.Data.SQLite.EF6" 
      type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6"/>
  </providers>
</entityFramework>

<system.data>
  <DbProviderFactories>
    <remove invariant="System.Data.SQLite.EF6" />
    <add name="SQLite Data Provider"
       invariant="System.Data.SQLite.EF6"
       description=".NET Framework Data Provider for SQLite"
       type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
  </DbProviderFactories>
</system.data>

And finally the connection string. The file where my database is located is C:\Users\Harald\Documents\DbSchools.sqlite. Of course you can choose your own location.

<connectionStrings>
  <add name="SchoolDbContext"
     connectionString="data source=C:\Users\Haral\Documents\DbSchools.sqlite"
     providerName="System.Data.SQLite.EF6" />

(there may be more connection strings to other databases)

This should compile, but you can't access the database yet. Summer 2020 Entity Framework does not create the tables, so you'll have to do this yourself.

As I thought this was part of the SchoolDbContext I added a method. For this you need a little knowledge of SQL, but I think you get the gist:

protected void CreateTables()
{
    const string sqlTextCreateTables = @"
        CREATE TABLE IF NOT EXISTS Addresses
        (
            Id INTEGER PRIMARY KEY NOT NULL,
            Street TEXT NOT NULL,
            Number INTEGER NOT NULL,
            Ext TEXT,
            ExtraLine TEXT,
            PostalCode TEXT NOT NULL,
            City TEXT NOT NULL,
            Country TEXT NOT NULL
        );
        CREATE INDEX IF NOT EXISTS indexAddresses ON Addresses (PostalCode, Number, Ext);

        CREATE TABLE IF NOT EXISTS Schools
        (
           Id INTEGER PRIMARY KEY NOT NULL,
           Name TEXT NOT NULL
        );

        CREATE TABLE IF NOT EXISTS Students
        (
            Id INTEGER PRIMARY KEY NOT NULL,
            Name TEXT NOT NULL,
            AddressId INTEGER NOT NULL,
            SchoolId INTEGER NOT NULL,

            FOREIGN KEY(AddressId) REFERENCES Addresses(Id)  ON DELETE NO ACTION,
            FOREIGN KEY(SchoolId) REFERENCES Schools(Id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS Teachers
        (
            Id INTEGER PRIMARY KEY NOT NULL,
            Name TEXT NOT NULL,

            AddressId INTEGER NOT NULL,
            SchoolId INTEGER NOT NULL,

            FOREIGN KEY(AddressId) REFERENCES Addresses(Id)  ON DELETE NO ACTION,
            FOREIGN KEY(SchoolId) REFERENCES Schools(Id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS TeachersStudents
        (
            TeacherId INTEGER NOT NULL,
            StudentId INTEGER NOT NULL,

            PRIMARY KEY (TeacherId, StudentId)
            FOREIGN KEY(TeacherId) REFERENCES Teachers(Id) ON DELETE NO ACTION,
            FOREIGN KEY(StudentId) REFERENCES Students(Id) ON DELETE NO ACTION
        )";

    var connectionString = this.Database.Connection.ConnectionString;
    using (var dbConnection = new System.Data.SQLite.SQLiteConnection(connectionString))
    {
        dbConnection.Open();
        using (var dbCommand = dbConnection.CreateCommand())
        {
            dbCommand.CommandText = sqlTextCreateTables;
            dbCommand.ExecuteNonQuery();
        }
    }
}

Some things are worth mentioning:

• Table Address has an extra index, so the search for an address using PostalCode + House Number (+ extension) will be faster. "What is your PostCode?" "Well it is 5473TB, house number 6". The index will immediately show the complete address.
• Although the SchoolDbcontext doesn't mention the junction table TeachersStudents, I still need to create it. The combination [TeacherId, StudentId] will be unique, so this can be used as primary key
• If a School is removed, all its Teachers and Students need to be removed: ON DELETE CASCADE
• If a Teacher leaves the School, the Students should not be harmed. If a Student leaves the School, the Teachers keep on teaching: ON DELETE NO ACTION

When your application executes an entity framework query for the first time after it has been started, method OnModelCreating is called. So that is a good moment to check if the tables exist, and if not, create them.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    this.CreateTables();

Of course you should use OnModelCreating to inform entity framework about your tables and the relations between the tables. This can be done after the tables are created.

Continuing OnModelCreating:

    this.OnModelCreatingTable(modelBuilder.Entity<Address>());
    this.OnModelCreatingTable(modelBuilder.Entity<School>());
    this.OnModelCreatingTable(modelBuilder.Entity<Teacher>());
    this.OnModelCreatingTable(modelBuilder.Entity<Student>());

    this.OnModelCreatingTableRelations(modelBuilder);

    base.OnModelCreating(modelBuilder);
}

For those who know entity framework, modelling these tables is fairly straightforward.

Address; example of a simple table

private void OnModelCreatingTable(EntityTypeConfiguration<Address> addresses)
{
    addresses.ToTable(nameof(SchoolDbContext.Addresses)).HasKey(address => address.Id);
    addresses.Property(address => address.Street).IsRequired();
    addresses.Property(address => address.Number).IsRequired();
    addresses.Property(address => address.Ext).IsOptional();
    addresses.Property(address => address.ExtraLine).IsOptional();
    addresses.Property(address => address.PostAlCode).IsRequired();
    addresses.Property(address => address.City).IsRequired();
    addresses.Property(address => address.Country).IsRequired();

    // The extra index, for fast search on [PostalCode, Number, Ext]
    addresses.HasIndex(address => new {address.PostAlCode, address.Number, address.Ext})
        .HasName("indexAddresses")
        .IsUnique();
    }

Schools is also simple:

    private void OnModelCreatingTable(EntityTypeConfiguration<School> schools)
    {
        schools.ToTable(nameof(this.Schools))
            .HasKey(school => school.Id);
        schools.Property(school => school.Name)
            .IsRequired();
    }

Teachers and Students: they have required foreign key to the Schools, every School has zero or more Students / Teachers:

private void OnModelCreatingTable(EntityTypeConfiguration<Teacher> teachers)
{
    teachers.ToTable(nameof(SchoolDbContext.Teachers))
            .HasKey(teacher => teacher.Id);
    teachers.Property(teacher => teacher.Name)
            .IsRequired();

    // Specify one-to-many to Schools using foreign key SchoolId
    teachers.HasRequired(teacher => teacher.School)
            .WithMany(school => school.Teachers)
            .HasForeignKey(teacher => teacher.SchoolId);
}

private void OnModelCreatingTable(EntityTypeConfiguration<Student> students)
{
    students.ToTable(nameof(SchoolDbContext.Students))
            .HasKey(student => student.Id);
    students.Property(student => student.Name)
            .IsRequired();

    // Specify one-to-many to Schools using foreign key SchoolId        
    students.HasRequired(student => student.School)
            .WithMany(school => school.Students)
            .HasForeignKey(student => student.SchoolId);
}

Note: by default: if a School is removed, this will cascade down: all its Teachers and Students will be removed.

Only one table relation is left: the junction table. If I wanted I could have defined the one-to-many relations between Schools and Teachers and Schools and Students also here. I already did this when defining the Teachers and the Students. So they are not needed here. I left the code, as example if you want to put them here.

private void OnModelCreatingTableRelations(DbModelBuilder modelBuilder)
{
    //// School <--> Teacher: One-to-Many
    //modelBuilder.Entity<School>()
    //    .HasMany(school => school.Teachers)
    //    .WithRequired(teacher => teacher.School)
    //    .HasForeignKey(teacher => teacher.SchoolId)
    //    .WillCascadeOnDelete(true);

    //// School <--> Student: One-To-Many
    //modelBuilder.Entity<School>()
    //    .HasMany(school => school.Students)
    //    .WithRequired(student => student.School)
    //    .HasForeignKey(student => student.SchoolId)
    //    .WillCascadeOnDelete(true);

    // Teacher <--> Student: Many-to-many
    modelBuilder.Entity<Teacher>()
       .HasMany(teacher => teacher.Students)
       .WithMany(student => student.Teachers)
       .Map(manyToMany =>
       {
           manyToMany.ToTable("TeachersStudents");
           manyToMany.MapLeftKey("TeacherId");
           manyToMany.MapRightKey("StudentId");
       });
}

The many-to-many mapping is explained here

Now we are almost done. All we have to do is make sure that the database will not be dropped and recreated. This is usually done in:

Database.SetInitializer<SchoolDbContext>(null);

Because I wanted to hide that we use SQLite, so I added this as a method to SchoolDbContext:

public class SchoolDbContext : DbContext
{
    public static void SetInitializeNoCreate()
    {
        Database.SetInitializer<SchoolDbContext>(null);
    }

    public SchoolDbContext() : base() { }
    public SchoolDbContext(string nameOrConnectionString) : base(nameOrConnectionString) { }

    // etc: add the DbSets, OnModelCreating and CreateTables as described earlier
}

I sometimes see that people set the initializer in the constructor:

public SchoolDbContext() : base()
{
    Database.SetInitializer<SchoolDbContext>(null);
}

However, this constructor will be called very often. I thought it a bit of a waste to do this every time.

Of course there are patterns to automatically set the initializer once when the SchoolDbContext is constructed for the first time. For simplicity I didn't use them here.

The console app

static void Main(string[] args)
{
    Console.SetBufferSize(120, 1000);
    Console.SetWindowSize(120, 40);

    Program p = new Program();
    p.Run();

    // just for some neat ending:
    if (System.Diagnostics.Debugger.IsAttached)
    {
        Console.WriteLine();
        Console.WriteLine("Fin");
        Console.ReadKey();
    }
}

Program()
{
    // Set the database initializer:
    SchoolDbContext.SetInitializeNoCreate();
}

And now the fun part: Add a School, add a Teacher and a Student and let the Teacher teach this Student.

void Run()
{
    // Add a School:
    School schoolToAdd = this.CreateRandomSchool();
    long addedSchoolId;
    using (var dbContext = new SchoolDbContext())
    {
        var addedSchool = dbContext.Schools.Add(schoolToAdd);
        dbContext.SaveChanges();
        addedSchoolId = addedSchool.Id;
    }

Add a Teacher:

    Teacher teacherToAdd = this.CreateRandomTeacher();
    teacherToAdd.SchoolId = addedSchoolId;

    long addedTeacherId;
    using (var dbContext = new SchoolDbContext())
    {
        var addedTeacher = dbContext.Teachers.Add(teacherToAdd);
        dbContext.SaveChanges();
        addedTeacherId = addedTeacher.Id;
    }

Add a Student.

Student studentToAdd = this.CreateRandomStudent();
studentToAdd.SchoolId = addedSchoolId;

long addedStudentId;
using (var dbContext = new SchoolDbContext())
{
    var addedStudent = dbContext.Students.Add(studentToAdd);
    dbContext.SaveChanges();
    addedStudentId = addedStudent.Id;
}

Almost done: only the many-to-many relation between teachers and students:

The student decides to be taught by the Teacher:

using (var dbContext = new SchoolDbContext())
{
    var fetchedStudent = dbContext.Find(addedStudentId);
    var fetchedTeacher = dbContext.Find(addedTeacherId);

    // either Add the Student to the Teacher:
    fetchedTeacher.Students.Add(fetchedStudent);

    // or Add the Teacher to the Student:
    fetchedStudents.Teachers.Add(fetchedTeacher);
    dbContext.SaveChanges();
}

I also tried to Remove Teachers from the Schools, and saw that this didn't harm the Students. Also if a Student leaves School, the Teachers keep on teaching. Finally: if I delete a School, all Students and Teachers are deleted.

So now I've shown you:

• simple tables, like Addresses and Schools;
• tables with one-to-many relationships: Teachers and Students;
• a many-to-many relationship: StudentsTeachers.

There is one relation that I didn't show: self-referencing: a foreign key to another object in the same table. I couldn't come up with a good example for this in the School database. If anyone has a good idea, please edit this answer and add the self-referencing table.

Hope this has been useful for you.