Using the Entity Framework Core with SQLite

CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

On occasion, your regular author, Peter Shaw, requires rest from the daily strife of the I.T. world. As much as a ghost in the machine as he is, even he needs such rest. Therefore, from time to time, I shall pop my head up and fill in with a few articles, making up for any gaps. Given that Shawty’s articles are .NET/C# focused, I will be following his lead.

For my first article, I’d like to share with you a very nice combination of technologies, which is: Entity Framework Core 1.0, SQLite, and—for the sake of this article—something on the desktop, such as a console application.

Entity Framework Core

Before we start, where does EF Core come from? As its name suggests, it spun out from the .NET Core camp of development. EF Core is an extensible version of Entity Framework, which may be complied against the full .NET Framework, or .NET Core for cross platform development. It’s worth keeping in mind that the original Entity Framework is still in development, but can only be complied against the full .NET Framework. You can find the GitHub repositories for both .NET Core and EF Core below…

• https://github.com/dotnet/core
• https://github.com/aspnet/EntityFramework

The next question one might ask is: If I’m building a desktop application with the full framework .NET, will pieces of code from .NET Core world work with something such as WPF or Win Forms? The answer to that is simply yes, although the more recent versions of .NET are recommended and needed in some cases. And, this is one of the great things about .NET Core. You may have heard the words light-weight being throw around a lot when .NET Core is in conversation. Well, that light-weightiness refers not immediately to size, but to modularity.

One of the guiding principles of its development was strong modularity. And this principle, echoed throughout all the projects spun from .NET Core, is something we can take great advantage of. And in this article, this is exactly what we’ll be doing.

Building the Application

To give you a basic overview, this application will be a .NET console application, for which we’ll bring in the needed components for EF Core for SQLite.

Once you have your full framework .NET Console application created, you’ll need to open your NuGet package manager, tick the ‘include prerelease’ option on—which is required at the time of this writing—and grab the following packages:

• EntityFramework.SQLite: This will install everything you need if you don’t want to create migrations.
• EntityFramework.Commands: This will give you the commands you’ll need to create a migration from the package manager console.

When you install the EF SQLite package, you will see quite a few libraries of code being installed. Don’t worry about this. Remember, as we’ve already talked about, .NET Core has a strong emphasis on modularity. There are a few libraries that come down with EF Core you may find are worthy of further investigation. I’ve used a number of them on recent projects for various needs.

And that’s it. That’s everything you need to get started with some coding. If you’ve used Entity Framework before, you’ll have probably jumped ahead and created your db context class already; but if you haven’t, mine looks something like this…

public class DataContext : DbContext
{
   public DataContext()
   {
      Database.Migrate();
   }

   public DbSet<Beer> Beers { get; set; }

   protected override void OnConfiguring(DbContextOptionsBuilder
      optionsBuilder)
   {
      string connectionStringBuilder = new
         SqliteConnectionStringBuilder()
      {
         DataSource = "beer.db"
      }
      .ToString();

      var connection = new SqliteConnection(connectionStringBuilder);
      optionsBuilder.UseSqlite(connection);
   }
}

If we examine the preceding code a little, we first need to look at the override we have in place for the ‘OnConfuguring’ method. In our override, we are creating a connection string using the SqliteConnectionStringBuilder class. We can give a name to our db by passing a string to the ‘DataSource’ property. I’ve named my database ‘beer.db,’ but you can give this database whatever name suits you best. Keep in mind the name of my database reflects that I’ll be storing information about my favourite beers in this db.

If you are familiar with EntityFramework already, you’ll recognise the property of type DbSet<T>, where T in my case is ‘Beer.’ If you are not familiar with EntityFramework, this property represents a table in your database. It might be recommended you dive a little more into Entity Framework for more details, but you can probably get to the end of this article without that knowledge and just play a little.

You’ll also see a line of code in the constructor for our db context; but we’ll cover this in a moment.

If you haven’t created a class to quell the voices of Intellisense—which at this point will be given you and an error saying it can’t find Beer. This is what I have in my Beer class.

public class Beer
{
   public int Id { get; set; }

   public string Name { get; set; }
}

Migrations

We’ll be going into the code to add an item to our database at runtime in a moment. Before we do, we need to talk about creating our SQLite database file. For this writing, I’m using migrations because of how easy it is to use in EF Core and I want to show you this. This is apparent when you look at the ‘DataContext’ class above, which includes the line…

Database.Migrate();

There is another option you can use here, which is…

Database.EnsureCreated();

I’ll leave it to your good self to explore the various options, but EnsureCreated pretty much does what it says on the box. It ensures that when you new the context up, the database has been created; and if it hasn’t, it will create it. However, because we are using Migrate(), we won’t need to use EnsureCreated() for this application.

And, as we are going down this path. we need to create our migration. Being sure we installed the package ‘EntityFramework.Commands,’ open your Package Manager Console and use this command…

Add-Migration

It will ask you for your migration name. Put what you like here; I used ‘Initial-Migration’ myself. Press Enter, and away it will go, creating your migration. And that is it. You do not need to update your database through the package manager console—if you are used to doing this—because the line of code ‘Database.Migrate()‘ will do this for you at runtime. And, at the same time, it will ensure the database has been created, and create it if it does not exist.

The Program

And the final piece of this application… The program.cs.

Mine is quite simple. It asks for a beer name, creates a beer object, and enters this into the database. My code looks something like this…

class Program
{
   static void Main(string[] args)
   {
      Console.WriteLine("Enter a beer name");
      string beerName = Console.ReadLine();

      Beer beer = new Beer()
      {
         Name = beerName,
      };

      DataContext db = new DataContext();

      db.Beers.Add(beer);
      db.SaveChanges();
   }
}

And that is it. The complete application, which if you run it, you can enter a beer name and store that in your SQLite db. Further expansion on the application might include the strength of the beer, and possibly a personal rating for said beer. Possibly even some notes, allowing you to expand a little on taste and quality.

When you have entered a few beers, you might want to have a peek into the db file. You will find your db file in the root of your application. If the application was run from VS in DEBUG, this would be -> bin -> Debug -> beer.db. Unless, of course, you named your db something else. To look at the Sqlite db file, I like to use sqlitebrowser, which can be found here.

One you’ve downloaded the required exe for your platform, run the browser and open the db file. For me, I can now see something like this…

Figure 1: The program in action

If you feel like exploring a little, you can see various pieces of information through each of the tabs; such as your ‘Migrations History’ by selecting the table under Browse Data, and ‘Database Structure.’

If you want to see the application, as I have put it together, running, you can find the code in GitHub at https://github.com/GLanata/EFCoreSQLiteConsole. Also, if you have any questions, you can find me on Twitter as @GLanata.