Your First EF Core App: DbContext, DbSet, SQLite and the Generated SQL

Version note: The code in this post is written for EF Core 6 and runs unchanged on EF Core 10.

Guess First

By the end of this post you will have a working EF Core application. Its Program.cs is this short:

using BookstoreApp;
using Microsoft.EntityFrameworkCore;

using BookstoreContext db = new();

bool created = db.Database.EnsureCreated();
Console.WriteLine($"Database created: {created}");

db.Books.Add(new Book { Title = "Domain-Driven Design", Price = 54.90m });
db.Books.Add(new Book { Title = "Refactoring", Price = 42.50m });

int saved = db.SaveChanges();
Console.WriteLine($"Saved rows: {saved}");

List<Book> books = db.Books
    .Where(b => b.Title.Contains("Design"))
    .OrderBy(b => b.Title)
    .ToList();

foreach (Book book in books)
{
    Console.WriteLine($"#{book.Id} {book.Title} ({book.Price})");
}

There is no database file in the folder yet. How many SQL commands does EF Core send to the database the first time you run the program? And the second time? Write your guess down; we will see the answer halfway through the post, in EF Core's own log.

The Project and the Packages

We start with an empty console application and add two packages:

dotnet new console -n BookstoreApp
cd BookstoreApp
dotnet add package Microsoft.EntityFrameworkCore.Sqlite --version 10.0.14
dotnet add package Microsoft.EntityFrameworkCore.Design --version 10.0.14

When the commands finish, BookstoreApp.csproj looks like this:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="10.0.14">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="10.0.14" />
  </ItemGroup>

</Project>

Microsoft.EntityFrameworkCore.Sqlite is the provider package. It is enough on its own, because it brings the EF Core core (Microsoft.EntityFrameworkCore), the relational layer (Microsoft.EntityFrameworkCore.Relational) and SQLite's native library along as dependencies. Microsoft.EntityFrameworkCore.Design is not there for the application to run; it is there so that the command-line tool we are about to install can read the project. That is why PrivateAssets is set to all: the package is used during the build and does not flow to other projects that reference yours.

These settings are for the .NET 10 SDK and EF Core 10. If you have an older project targeting .NET 6, you install the 6.0.36 version of the same packages; the only things that change in the csproj are TargetFramework becoming net6.0 and the package versions becoming 6.0.36. The console templates in the current .NET 8 and .NET 10 SDKs don't offer a -f net6.0 option, so if you want an older target you have to change that line by hand. Every example in this series was built and run on both setups.

The dotnet-ef Tool

The command-line tool EF Core uses to create migrations, update the database or report on a DbContext is dotnet-ef. It doesn't ship with the .NET SDK; you install it separately. You have two options: install it globally on the machine or add it to the project as a local tool. Prefer the local tool; its version is written into a manifest file inside the project, and everyone who clones the repository uses the same version:

dotnet new tool-manifest
dotnet tool install dotnet-ef --version 10.0.14
dotnet ef --version

Entity Framework Core .NET Command-line Tools
10.0.14

The .NET 10 SDK writes the manifest into the project folder as dotnet-tools.json. The .NET 8 SDK puts the same file under .config/dotnet-tools.json. When someone clones the repository and runs dotnet tool restore, the tool comes back with the version in the manifest. The tool version should match the EF Core package version: in an EF Core 6 project you use --version 6.0.36.

The quickest way to confirm the tool can see your project is to run this command once the DbContext we are about to write is in place:

dotnet ef dbcontext info

Build started...
Build succeeded.
Type: BookstoreApp.BookstoreContext
Provider name: Microsoft.EntityFrameworkCore.Sqlite
Database name: main
Data source: bookstore.db
Options: None

The tool built the project, found the DbContext in it, created an instance and reported which provider and which database it will connect to. We will use this tool much more heavily when we get to migrations.

The Entity and the DbContext

First the entity. A plain C# class that will map to a table in the database:

namespace BookstoreApp;

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public decimal Price { get; set; }
}

No base class, no attributes. By convention, EF Core will treat the property named Id as the primary key. We will go through the conventions one by one in the next post. Now the DbContext:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace BookstoreApp;

public class BookstoreContext : DbContext
{
    public DbSet<Book> Books => Set<Book>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlite("Data Source=bookstore.db")
            .LogTo(Console.WriteLine, LogLevel.Information);
    }
}

There are three parts. The class deriving from DbContext is the application's session with the database. DbSet<Book> tells EF Core "Book is an entity and gets a table", and it is where queries start. The classic { get; set; } form produces warning CS8618 ("Non-nullable property 'Books' must contain a non-null value when exiting constructor") in an EF Core 6 project when nullable reference types are on. In EF Core 10, EF's own analyzer suppresses that warning for DbSet properties. The Books => Set<Book>() form compiles cleanly on both versions.

OnConfiguring is where you tell the context which provider and which connection to use. UseSqlite picks the provider, and "Data Source=bookstore.db" is the connection string: the bookstore.db file in the folder the application runs in. LogTo prints EF Core's logs at Information level and above to the console. In an ASP.NET Core application we configure this through dependency injection with AddDbContext instead of OnConfiguring; we will see that in the ASP.NET Core Web API series and in this series' integration post.

Creating the Database with EnsureCreated

The program's first job is db.Database.EnsureCreated(). This method looks at the database. If the database doesn't exist, or has no tables in it, it creates every table in the model and returns true. If the tables already exist, it does nothing and returns false. Let's run the program for the first time (EF Core 6 output):

info: 11/4/2026 00:48:15.679 CoreEventId.ContextInitialized[10403] (Microsoft.EntityFrameworkCore.Infrastructure)
      Entity Framework Core 6.0.36 initialized 'BookstoreContext' using provider 'Microsoft.EntityFrameworkCore.Sqlite:6.0.36' with options: None
info: 11/4/2026 00:48:15.805 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      PRAGMA journal_mode = 'wal';
info: 11/4/2026 00:48:15.827 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "Books" (
          "Id" INTEGER NOT NULL CONSTRAINT "PK_Books" PRIMARY KEY AUTOINCREMENT,
          "Title" TEXT NOT NULL,
          "Price" TEXT NOT NULL
      );
Database created: True

The first line says which EF Core and provider version the context was initialized with; EF Core 10's Information-level output doesn't include this line. PRAGMA journal_mode = 'wal' is a setting sent when the SQLite file is newly created. Then comes the CREATE TABLE generated from the model: Id became an auto-increment primary key, and Price is stored as TEXT because SQLite has no decimal type.

When you run the program a second time, this part changes:

info: 11/4/2026 00:48:30.797 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT COUNT(*) FROM "sqlite_master" WHERE "type" = 'table' AND "rootpage" IS NOT NULL;
Database created: False

EF Core checked whether tables exist, found them and skipped the creation step. The critical point here: EnsureCreated doesn't update an existing schema. If you add a new property to Book, the table stays as it was and the query fails because of the missing column. It is very handy for experiments and tests, but in a real application whose schema will change over time it gives way to migrations. If you want a clean start during development, you can put db.Database.EnsureDeleted() in front of it; as the name says, it deletes the whole database.

The First Insert and the First Query

db.Books.Add(...) sends nothing to the database; it only registers the entity with the change tracker in the Added state. The real work happens in SaveChanges: EF Core gathers all pending changes, generates the necessary commands, runs them and returns the number of affected rows. The rest of the first run looks like this:

info: 11/4/2026 00:48:15.859 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (1ms) [Parameters=[@p0='?' (DbType = Decimal), @p1='?' (Size = 20)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "Books" ("Price", "Title")
      VALUES (@p0, @p1);
      SELECT "Id"
      FROM "Books"
      WHERE changes() = 1 AND "rowid" = last_insert_rowid();
info: 11/4/2026 00:48:15.863 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[@p0='?' (DbType = Decimal), @p1='?' (Size = 11)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "Books" ("Price", "Title")
      VALUES (@p0, @p1);
      SELECT "Id"
      FROM "Books"
      WHERE changes() = 1 AND "rowid" = last_insert_rowid();
Saved rows: 2
info: 11/4/2026 00:48:15.914 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "b"."Id", "b"."Price", "b"."Title"
      FROM "Books" AS "b"
      WHERE ('Design' = '') OR (instr("b"."Title", 'Design') > 0)
      ORDER BY "b"."Title"
#1 Domain-Driven Design (54.90)

The answer to the guessing question: five commands on the first run (PRAGMA, CREATE TABLE, two INSERTs, one SELECT) and four on the second (the table check, two INSERTs, one SELECT). Each INSERT has another SELECT behind it, because the database generates the Id and EF Core reads that value back and writes it into the entity. That is why book.Id is filled in after saving.

On the query side nothing ran until ToList() was called. Where and OrderBy only built a query recipe; it was ToList(), asking for the result, that translated the recipe to SQL and sent it to the database. Contains was translated to SQLite's instr function.

In real applications, especially on the web, we use the async counterparts of these calls: SaveChangesAsync, ToListAsync, SingleAsync. The behavior is the same; the thread is simply freed while waiting for the database.

Reading the Generated SQL

LogTo(Console.WriteLine, LogLevel.Information) prints everything and can get noisy. If you only want to see the SQL commands being executed, you can filter by log category. If you also want to see parameter values, add EnableSensitiveDataLogging:

optionsBuilder
    .UseSqlite("Data Source=bookstore.db")
    .LogTo(Console.WriteLine, new[] { DbLoggerCategory.Database.Command.Name }, LogLevel.Information)
    .EnableSensitiveDataLogging();

Let's also move the search term into a variable and look at the SQL with ToQueryString before running the query:

string term = "Design";
IQueryable<Book> query = db.Books
    .Where(b => b.Title.Contains(term))
    .OrderBy(b => b.Title);

Console.WriteLine(query.ToQueryString());
List<Book> books = await query.ToListAsync();

EF Core 6 output:

.param set @__term_0 'Design'

SELECT "b"."Id", "b"."Price", "b"."Title"
FROM "Books" AS "b"
WHERE (@__term_0 = '') OR (instr("b"."Title", @__term_0) > 0)
ORDER BY "b"."Title"
info: 11/4/2026 00:49:21.143 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (1ms) [Parameters=[@__term_0='Design' (Size = 6)], CommandType='Text', CommandTimeout='30']
      SELECT "b"."Id", "b"."Price", "b"."Title"
      FROM "Books" AS "b"
      WHERE (@__term_0 = '') OR (instr("b"."Title", @__term_0) > 0)
      ORDER BY "b"."Title"

The value you passed as a variable is no longer embedded in the SQL; it travels as a parameter named @__term_0. That protects against SQL injection and lets the database reuse the same query plan. The .param set line at the top of the ToQueryString output is there so you can copy the SQL as is and run it in the SQLite command-line shell.

The same code produces simpler SQL on EF Core 10:

.param set @term 'Design'

SELECT "b"."Id", "b"."Price", "b"."Title"
FROM "Books" AS "b"
WHERE instr("b"."Title", @term) > 0
ORDER BY "b"."Title"

The parameter name was simplified to the variable's name, and the empty-string check is gone. On EF Core 10 the INSERTs also end with RETURNING "Id" instead of SELECT ... last_insert_rowid(). Not a character of the code changed; the provider generates the SQL, and it generates it a little better with each version.

Common Pitfalls

Forgetting the Design package. Without the package, the dotnet ef command gets through the build but stops with this message:

Your startup project 'BookstoreApp' doesn't reference Microsoft.EntityFrameworkCore.Design. This package is required for the Entity Framework Core Tools to work. Ensure your startup project is correct, install the package, and try again.

Letting the tool and the package versions drift apart. If you run the 6.0.36 tool in an EF Core 10 project, the command works but first prints this warning:

The Entity Framework tools version '6.0.36' is older than that of the runtime '10.0.14'. Update the tools for the latest features and bug fixes. See https://aka.ms/AAc1fbw for more information.

Looking for the database file in the wrong folder. Data Source=bookstore.db is a relative path, resolved against the application's working directory. When you run with dotnet run, the file is created in the project folder. Depending on your IDE settings, the working directory may be bin/Debug/...; then you end up with two separate database files and wonder where the data you saved went.

Starting with EnsureCreated and switching to migrations later. A database created by EnsureCreated has no table recording migration history, so the first migration tries to create the existing tables again and dotnet ef database update stops with this error: SQLite Error 1: 'table "Books" already exists'. If you are going to use migrations, start with migrations; we will cover this in detail in the migrations post.

Leaving EnableSensitiveDataLogging on in production. The name is not an accident: parameter values, meaning customer names, email addresses and other personal data, end up in the log. Turn it on only in development.

A Small Experiment

★☆☆ Set up the project from scratch, run the program twice and find the SQL difference between the two runs in the log yourself. Are the books inserted twice on every run? If so, how would you prevent it?

★★☆ Add public int PageCount { get; set; } to the Book class and run the program without deleting the database. Read the error you get, then add EnsureDeleted before EnsureCreated and try again.

★★★ Make the program async: use EnsureCreatedAsync, SaveChangesAsync and ToListAsync. Then add .Where(b => b.Price > 50m) to the query and look at how the decimal comparison is translated in the ToQueryString output.

In this post we gave EF Core a single class and it took care of the rest: the table name, the primary key, the NOT NULL columns, the auto-increment Id. All of these decisions come from conventions. In the next post we will look at how EF Core builds the model: the key convention, required and optional properties with nullable reference types, string lengths, generated values, table and column names, and shadow properties.