Entity Framework Core CLI toolkit cheat sheet

Entity framework core (EF Core) is a object relational mapper (ORM) for dotnet core. Allowing developers to interact with databases using .NET objects.

Microsoft documentation can be found at https://docs.microsoft.com/en-us/ef/core/

Installing the EF Core CLI toolkit

For demonstration purposes I'm creating a console application, but the same instructions apply to any other kind of dotnetcore application:

mkdir efcoredemo  
cd efcoredemo  
dotnet new console

Update the generated efcoredemo.csproj file to look like the following, adding the 'DotNetCliToolsReference' segment:

<Project Sdk="Microsoft.NET.Sdk">  
    <PropertyGroup>
        <OutputType>Exe</OutputType>
        <TargetFramework>netcoreapp1.1</TargetFramework>
    </PropertyGroup>
    <ItemGroup>
        <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="1.0.0" />
    </ItemGroup>
</Project>

You will also need to install the shared design-time components. This can be done by running the following command:

dotnet add package Microsoft.EntityFrameworkCore.Design  
dotnet restore

You should now have access to the EF Core tools. To see the help, you can execute:

dotnet ef --help

Scaffolding a database

We're going to be using sqlite for our database server as it works across Windows, Linux and MacOS. So let's first of all install the sqlite packages:

dotnet add package Microsoft.EntityFrameworkCore.Sqlite  
dotnet add package Microsoft.EntityFrameworkCore.Sqlite.Design

We can then scaffold a new database and the necessary csharp classes and configuration strings by running the following command:

dotnet ef dbcontext scaffold "DataSource=/Code/efcoredemo/MyDb.sqlite3" Microsoft.EntityFrameworkCore.Sqlite

dotnet ef dbcontext scaffold - let's scaffold a dbcontext!
"DataSource=/Code/efcoredemo/MyDb.sqlite3" - create the sqlite database in the project folder and call it "MyDb.sqlite3".
Microsoft.EntityFrameworkCore.Sqlite - provider to be user (ie. Sqlite)

You'll notice that after running this command two new files are created. The database, MyDb.sqlite3, and MyDbContext.cs, which contains the information on how to connect to your newly created database.

Creating a table

For our example we'll create a table called "Book". To do this, add a new class to your project named Book.cs with the following contents:

public class Book  
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string ISBN { get; set; }
}

Then edit the previously generated context file MyDbContext.cs, adding the following to the OnModelCreating method.

protected override void OnModelCreating(ModelBuilder modelBuilder)  
{
    modelBuilder.Entity<Book>();
}

This denotes the Book class as a "code first" entity to Entity Framework. When the following command is executed in the terminal your first migration will be generated.

dotnet ef migrations add "AddBookTable"

We can then apply this migration to our database by running the command.

dotnet ef database update

And finally, check out your sweet sweet database skills by viewing the tables you've created, and its schema.

sqlite3 MyDb,sqlite3 ".table"  
sqlite3 MyDb.sqlite3 ".schema Book"

You will notice the table schema automagically has the Id set as the Primary Key with auto increment. This is because EF Core will use conventions to try and generate your primary keys and referential constraints.

CREATE TABLE "Book" (  
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Book" PRIMARY KEY AUTOINCREMENT,
    "ISBN" TEXT,
    "Name" TEXT
);

Managing migrations

So how do we manage our database. What happens if we make a mistake?

List migrations:
This will show a list of available migrations.

dotnet ef migrations list

View migrations:
If you want to view a specific migration, then you can generate the SQL script by running the command:

dotnet ef migrations script <FROM> <TO>

You will need to substitute the <FROM> and <TO> arguments with the appropriate migration name (as shown in the migrations list output).

Note that the <FROM> and <TO> arguments will default to the earliest and most recent migration respectively. If you have a lot of migrations this can generate a lot of SQL code!

To show your latest migration simply execute the command specifying the migration before the one you want to view. For example if you have a list of migrations as follows:

20170419094406_AddBooksTable
20170419094416_AddAuthorsTable
20170419094468_AddCoverImageToBookTable

And you want to see the script generated by the last "AddCoverImageToBookTable" migration, then execute the script command as follows:

dotnet ef migrations script 20170419094416_AddAuthorsTable

Update database:
When you run the following command ef core will apply all migrations up to the <TO> migration specified. If you don't provide a <TO> argument, then all migrations will be applied to the latest.

dotnet ef database update <TO>

Note that this command will use the default connection defined in your solution.

Rollback database:
Accidents happen, and sometimes you apply a migration to the database that you later want to revert. To do this, "update" the database to the migration before the one you want to remove. EF core generates up and down scripts for each migration, and when you update a database to an earlier version than what's been applied, the down script will be executed and the migration removed from the database migrations history table.

Remove migrations:
Once you have rolled back your database to an earlier version, you can then remove the migration. Do so with the following command:

dotnet ef migrations remove

This will only remove the latest migration, and because migrations are linear (ie. applied one on top of another)to revert at migration at position 'N' you will need to rollback each succeeding migration individually.

Menu

Installing the EF Core CLI toolkit

Scaffolding a database

Creating a table

Managing migrations