Getting Started with Entity Framework Core and SQLite

Earlier I created a Reminder Service using ASP.NET Core Web API. For simplicity I used a Hashset in C# as the repository for the reminders. It might be more useful to use a database, and therefore in this example I will create a .NET Core Console Application that uses Entity Framework Core to create a SQLite Database and perform various CRUD operations. Later I will add EF Core and SQLite to the Reminder Service.

Entity Framework Core and SQLite

I open a bash terminal on macOS and create a new .NET Core Application, restore the Nuget Packages, and open everything in Visual Studio Code.

mkdir efcore
cd efcore

dotnet new
dotnew restore

code .

I just need to add 1 dependency for this application and that is Microsoft.EntityFrameworkCore.Sqlite. I'm not going to work with the Entity Framework Tools and Database Migrations in this example. I will write about those later.

  "dependencies": {
      "Microsoft.EntityFrameworkCore.Sqlite": "1.0.0"
  },

Once I have added the new dependency, I re-run dotnet restore from the Visual Studio Code Command Palette (CMD-SHIFT-P).

Next, I create a class, called Reminder, that will serve as the entity for a reminder. I'll give it a primary key of Id and the Title Property will be the actual reminder.

using System.ComponentModel.DataAnnotations;

namespace DatabaseApplication {
    public class Reminder {
        [Key]
        public int Id { get; set;}
        [Required]
        public string Title { get; set;}
    }
}

Now I need to create a class, called SqliteDbContext, that represents the SQLite Database. The database file will be called Reminders.sqlite.

using Microsoft.EntityFrameworkCore;

namespace DatabaseApplication {
    public class SqliteDbContext :  DbContext {
        public DbSet<Reminder> Reminders { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
            optionsBuilder.UseSqlite("Filename=./Reminders.sqlite");
        }
    }
}

That's crazy simple, right? The last thing I need to do is just perform my CRUD operations.

using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;

namespace DatabaseApplication {
    public class Program {
        public static void Main(string[] args) {
            using (var context = new SqliteDbContext()) {

                // Start with a clean database
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();

                // Add reminders.
                context.Reminders.Add(new Reminder {
                    Title = "Meditate"
                });
                context.Reminders.Add(new Reminder {
                    Title = "Eat a nutritious breakfast"
                });
                context.SaveChanges();

                // Fetch Reminders
                var reminders = context.Reminders.ToArray();
                foreach(var reminder in reminders) {
                    Console.WriteLine($"{reminder.Title}");
                }

                // Remove a reminder
                context.Database.ExecuteSqlCommand(
                    "DELETE FROM Reminders WHERE Title = {0}", "Meditate");

                // Fetch Reminders
                var reminderz = context.Reminders.ToArray();
                foreach(var reminder in reminderz) {
                    Console.WriteLine($"{reminder.Title}");
                }
            }
        }
    }
}

I'm having Entity Framework Core create the database. Like I mentioned earlier, I could be a bit more proactive and use the EF Core Command Line Tools and Data Migrations. I'll do that next time. Once the database is created, I just add a few reminders, delete a reminder, and then re-fetch reminders. The output looks like this. I added some spacing so you can see that in the first list there are 2 reminders and in the next list there is only 1 reminder.

Meditate
Eat a nutritious breakfast
Eat a nutritious breakfast

Entity Framework Core and SQLite

Next time I will add this functionality to the ASP.NET Core Web API Reminder Service. Things will be a little different, because I'll be using ASP.NET Core and its depdendency injection framework.

You can find me on twitter as @KoderDojo! I hope this was useful!