A first look at Entity Framework Core with SQLite database on Ubuntu Linux

Posted on 12 September, 2016

In this blog post I will go through step by step how to create a simple .NET Core console application that can insert and select a SQLite database table using Entity Framework Core as well as run on Ubuntu Linux. In order to follow these steps below you need to install .NET Core on your Ubuntu box which has step by step instruction here.

Let's start create the application.

Step 1: Create new .NET Core project by terminal command

In your terminal, execute these commands to create a new directory named FirstLookEFCore and a new empty .NET Core console project.

mkdir FirstLookEFCore
cd FirstLookEFCore/
dotnet new

The command above will create a .NET Core console project with two file Program.cs and project.json as below.

Step 2: Setup Entity Framework Core SQLite dependencies

Open project.json file with any text editor you like. Then add these json settings for Entity Framework Core SQLite to dependencies and tool sections.

"Microsoft.EntityFrameworkCore.Sqlite": "1.0.0",
"Microsoft.EntityFrameworkCore.Design": {
    "version": "1.0.0-preview2-final",
    "type": "build"
}
"tools": {
    "Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final"
}

The final project.json file will look like below:

{
  "version": "1.0.0-*",
  "buildOptions": {
    "debugType": "portable",
    "emitEntryPoint": true
  },
  "dependencies": {
    "Microsoft.EntityFrameworkCore.Sqlite": "1.0.0",
    "Microsoft.EntityFrameworkCore.Design": {
      "version": "1.0.0-preview2-final",
      "type": "build"
    }
  },
  "frameworks": {
    "netcoreapp1.0": {
      "dependencies": {
        "Microsoft.NETCore.App": {
          "type": "platform",
          "version": "1.0.0"
        }
      },
      "imports": "dnxcore50"
    }
  },
  "tools": {
    "Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final"
  }
}

Step 3: Create Model and DbContext classes

In the same directory, create a new file named Contact.cs and define Contact class as below:

using System;
namespace FirstLookEFCore
{
    public class Contact
    {
        public int ContactId { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public string Phone { get; set; }
        public string Address { get; set; }
        public DateTime Birthday { get; set; }
        public string Website { get; set; }
        public string Notes { get; set; }
    }
}

Create a new file named ContactContext.cs and define ContactContext class as below:

using Microsoft.EntityFrameworkCore;
namespace FirstLookEFCore
{
    public class ContactContext : DbContext
    {
        public DbSet Contacts { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite("Filename=./contact.db");
        }
    }
}

Step 4: Create database migrations

Execute the command below to let .NET Core download and install dependencies and tools configured at Step 2.

dotnet restore

Then execute this command to let Entity Framework generate migration classes for existing model and DBContext classes defined at Step 3.

dotnet ef migrations add InitialDatabase


The command above will generate following classes under /Migrations directory.

Step 5: Create the SQLite database file

Execute the command below to let Entity Framework create a SQLite database file

dotnet ef database update


The command above will based on the connection string configured in ContactContext class at Step 3 and migration classes to create a SQLite database file named contact.db under /bin/Debug/netcoreapp1.0/ directory

Step 6: Access SQLite database in .NET Core application

Open the Program.cs file and write some code that do simple operations as below
Insert one row into database.
Select all rows from database and print it to console.

using System;
namespace FirstLookEFCore
{
    public class Program
    {
        public static void Main(string[] args)
        {
            using (var db = new ContactContext())
            {
                db.Contacts.Add(new Contact
                {
                    Name = "Contact Name",
                    Email = "sample@testing.com",
                    Website = "http://google.com"
                });
                var numberOfRecordSaved = db.SaveChanges();
                Console.WriteLine($"{numberOfRecordSaved} records saved.");
                Console.WriteLine("Select records from database:");
                foreach (var contact in db.Contacts)
                {
                    Console.WriteLine($"ContactId: {contact.ContactId}");
                    Console.WriteLine($"Name: {contact.Name}");
                    Console.WriteLine($"Email: {contact.Email}");
                    Console.WriteLine($"Website: {contact.Website}");
                }
            }
        }
    }
}

Notice that the default console application that .NET Core created with namespace is "ConsoleApplication". In the class above I have changed the namespace to FirstLookEFCore.
Now execute the command below in you terminal to run the application and observe the result

dotnet run


I also use the tool called SQLite Database Browser to open the contact.db file and check the database table:

Summary

I just finished 6 simple steps to create a .NET Core application that can insert and selct records from SQLite database and run it on Ubuntu Linux.

Happy Coding!