DEV Community

loading...

Entity Framework Core

Douglas Starnes
・7 min read

In this post, you'll meet Entity Framework Core, an Object Relational Mapper that will allow Quanda to communicate with a database using C# classes instead of SQL queries.

Object Relational Mapper

If you've worked with relational databases in the past such as Microsoft SQL Server, MySQL or PostgreSQL, you have been exposed to SQL or Structured Query Language. SQL is a common thread among almost all relational databases as the way to get data into and out of tables. But SQL is not the easiest language in the world to work with. In addition, it's another language you have to learn to write and maintain your app.

One solution to this issue is the idea of an Object Relational Mapper, or ORM. An ORM creates associations between objects in the language of the app and SQL tables. Then the ORM provides an API to work with the objects in the app instead of shipping SQL queries to the database. The ORM the Quanda uses is called Entity Framework Core, or EF Core.

Before getting started with EF Core, there are a few NuGet packages and a dotnet CLI tool that need to be installed. To install the NuGet packages, press Ctrl-Shift-P to bring up the Command Palette and then search for NuGet Gallery. In the NuGet Gallery search for and add these packages to Quanda:

  • Microsoft.EntityFrameworkCore
  • Microsoft.EntityFrameworkCore.Design
  • Microsoft.EntityFrameworkCore.Sqlite

Next, in a terminal window, install the EF Core tooling for the dotnet CLI.

$ dotnet tool install -g dotnet-ef

Now you should be able to run

$ dotnet ef

At the command line.

You'll need to create a POCO (Plain Old C# Object) model class to represent the data that would otherwise be stored in tables. The first one will be for questions. Create a new directory in the project called Models to hold the model classes.

$ ni -Name Models -Type Directory

And create a C# file to hold the Question class.

$ ni ./Models -Name Question.cs -Type File

Now press Ctrl-P to search for and open Question.cs.

Your First Model Class

For now, the Question class will be pretty simple. First, add the namespace and class to the file.

namespace Quanda.Models
{
    public class Question
    {

    }
}

The class will be mapped to a table in the database that will be generated later. Columns in the table will be mapped to properties in the class. Add this property to the Question class:

public int QuestionId { get; set; }

This class follows a convention that EF Core has set up. If a property has a name of Id prefixed with the name of the class, it will be mapped to the auto-incrementing primary key of the associated database table. Thus, QuestionId will be the primary key for the to be created Questions table in the database. There are several other conventions you will see later when creating relationships between models in EF Core.

Here's a VS Code tip, you don't have to type the property out. Instead, use a snippet. Type prop and press Tab. This will stub out a public property if type int named MyProperty for you.

Alt Text

Notice that the type is highlighted so you can start typing to replace it. The next property will be a string. Press the Tab key to highlight the property name and change it to QuestionTitle and press Tab once more to more the cursor to the end of the line. Repeat the process to add a another string property named QuestionText.

public int QuestionId { get; set; }
public string QuestionTitle { get; set; }

The next property is of type DateTime and is named Created to store the timestamp of when the question was created. Notice the red line underneath DateTime.

Alt Text

Hover the mouse cursor over the offending text to see the error.

Alt Text

To solve this error, a using statement needs to bring in the System namespace. While this could be done by hand, VS Code can do it for you. Place the cursor on the code throwing the error and press Ctrl-..

Alt Text

VS Code gives you several options to generate the type, use a fully qualified type or to simply add the using statement. The last property is also a DateTime named Edited.

The Database Context

You'll add more model classes later and create relationships but for now, you'll create something called a database context that Quanda will use to communicate with the database. In the Models directory create a new C# file.

$ ni ./Models -Name QuandaDbContext.cs -Type File

Open the file with Ctrl-P and add the class to it.

namespace Quanda.Models
{
    public class QuandaDbContext: DbContext
    {

    }
}

The base class DbContext is in the Microsoft.EntityFrameworkCore namespace which needs to be imported with Ctrl-.. Now use the ctor snippet to create a constructor for QuandaDbContext. Type ctor and press Tab. Press Tab twice more to accept the modifier and name of the constructor and highlight Parameters the constructor will accept a DbContextOptions. To add one, simply type DCO and press Ctrl-Space.

Alt Text

VS Code will attempt to match symbols based on those letters. Usually a good place to start are the capitalized letters in the name. Select DbContextOptions and make it generic on the context, QuandaDbContext. Name this options and then pass it to the base class. This is all that EF Core requires of the constructor.

To tell the database context about the model classes, you'll add a property for each one. The type of the property will be DbSet and is generic on the model class. The entire class should look like this

using Microsoft.EntityFrameworkCore;

namespace Quanda.Models
{
    public class QuandaDbContext: DbContext
    {
        public QuandaDbContext(DbContextOptions<QuandaDbContext> options)
            : base(options)
        {

        }

        public DbSet<Question> Questions { get; set; }
    }
}

The last step in the code is to tell the app about the database context. This is done in the Startup.cs file. This file configures the app when it is launched. Find the ConfigureServices method and add the following line before the call to AddRazorPages.

services.AddDbContext<QuandaDbContext>(
    options => options.UseSqlite(Configuration.GetConnectionString("Quanda")));

You'll need to add using statements for QuandaDbContext and UseSqlite with Ctrl-.. This line registers the database context class with Quanda tells it how to connect to the database. For development purposes, the file-based local SQLite database will suffice. There are a few hoops you'll have to jump through because of the limitations of SQLite, but it's a tradeoff that is warranted compared to the complexities of managing a database server during development.

One last thing is to add the connection string for the database to the appsettings.json file. This file contains values and metadata that you don't want to place in the code, such as connection strings. Open the file and add a new key called ConnectionStrings (make sure it is plural). The value will be a JSON object and a key / value pair for each string.

{
  // omitted ...
  "ConnectionStrings": {
    "Quanda": "Data Source = quanda.db"
  }
}

For SQLite, the connection string is just a path to the database file, relative to the appsettings.json file.

Migrations

To manage the actual database (the SQLite file) you'll use migrations. A migration is essentially the instructions for restoring the the state of a database at specified points. Every time you make a change to the structure of the data model, you create a migration to generate C# code that will modify the database to bring it in sync with the model. So you will have a series of migrations and each assumes the previous has been applied. The initial migration will also create the database as it does not yet exist. To create a migration use the dotnet ef command.

$ dotnet ef migrations add Initial

The migration will be stored in a new Migrations directory in the project. Each migration generates two C# files. The name of the file is the same as the name of the migration preceded by a timestamp.

Alt Text

Rarely, if ever, will you need to edit these files, but it's not a bad idea to crack open the migration file (not the designer file) and scan it for any obvious errors.

Each migration is a class inheriting the base Migration class. There are two methods Up and Down. The Up method applies the changes to bring the database in sync with the model. The Down method undoes the changes made by the Up method. This gives you the ability to move back and forth in time.

The Up method for this migration creates a table called Questions that corresponds to the Questions DbSet in the QuandaDbContext class. The columns of the table correspond to the properties of the Question data model class. Notice that the convention of the QuestionId property generated code to make a required auto incrementing column. The method added a constraint to the column to make it a primary key. The Down method simply drops the table.

To apply the most recent migration, run this command:

$ dotnet ef database update

Notice that the database file quanda.db is created in the project.

Alt Text

Inside the Database

To wrap up, you'll install an extension for VS Code that will let you explore a SQLite database, from inside of the editor. Press Ctrl-Shift-X to open the Extensions pane and search for the SQLite extension by alexcvzz.

Alt Text

Now in the Command Palette (Ctrl-Shift-P) search for the command SQLite: Open Database. A list of databases detected will appear, select quanda.db.

Alt Text

The SQLite Explorer will appear in the Explorer pane, expand the database and table to see the columns added by EF Core.

Alt Text

Right click on a table to generate SQL scripts to query the database.

Alt Text

Press Ctrl-Shift-Q to run the query. Of course there are no rows in the database right now. You'll start to work with the database in the next post. Thanks for reading!

Discussion (1)

Collapse
saint4eva profile image
saint4eva

Nice one.