EF Core / LINQ ↔ PostgreSQL Cheat Sheet
If you can read SQL but have trouble remembering LINQ method names, use this page as a reference. The examples reuse the Todo items and categories from Chapters 15–17. This is a reference page; you do not need to read it all before continuing along the main path.
Where, Select, and OrderBy are part of LINQ (Language Integrated Query). The EF Core database provider translates queries into SQL; SQLite and PostgreSQL may produce different translations.
Technical detail
The main tutorial uses SQLite. This page also runs on SQLite by default and includes an equivalent PostgreSQL script, as well as a query generated by Npgsql. The equivalent script is for reading and comparison; it does not mean EF Core will generate exactly the same SQL.
Complete example and how to run it
This is a console program that observes database operations without starting an HTTP service. Each run creates a new in-memory database that disappears when the program exits, so you can run it repeatedly.
Expand the complete project files
using System.Text.Json;
using Microsoft.EntityFrameworkCore;
var postgresSql = args.Contains("--postgres-sql");
var options = new DbContextOptionsBuilder<TodoDbContext>();
if (postgresSql)
options.UseNpgsql("Host=localhost;Database=translation_only");
else
options.UseSqlite("Data Source=:memory:");
await using var db = new TodoDbContext(options.Options);
var categoryId = 1;
var query = db.Todos.AsNoTracking()
.Where(t => t.CategoryId == categoryId && !t.Done)
.OrderBy(t => t.Title).ThenBy(t => t.Id)
.Skip(0).Take(2)
.Select(t => new { t.Id, t.Title, Category = t.Category.Name });
// Translate queries only; do not connect to PostgreSQL or create tables.
if (postgresSql)
{
Console.WriteLine(query.ToQueryString());
return;
}
// The in-memory database disappears when the connection closes; each run starts with the same data.
await db.Database.OpenConnectionAsync();
await db.Database.EnsureCreatedAsync();
db.Categories.AddRange(new Category { Id = 1, Name = "Work" }, new Category { Id = 2, Name = "Life" });
db.Todos.AddRange(
new Todo { Id = 1, Title = "Write report", CategoryId = 1 },
new Todo { Id = 2, Title = "Review PR", Done = true, CategoryId = 1 },
new Todo { Id = 3, Title = "Buy milk", CategoryId = 2 });
await db.SaveChangesAsync();
db.ChangeTracker.Clear();
Print("Filter and projection", await query.ToListAsync());
Print("Second page", await db.Todos.AsNoTracking().OrderBy(t => t.Id)
.Skip(2).Take(2).Select(t => new { t.Id, t.Title }).ToListAsync());
Print("Incomplete count", await db.Todos.CountAsync(t => !t.Done));
Print("Any incomplete tasks?", await db.Todos.AnyAsync(t => !t.Done));
Print("First item", (await db.Todos.AsNoTracking().OrderBy(t => t.Id).FirstOrDefaultAsync())?.Id);
Print("Missing ID", (await db.Todos.AsNoTracking().SingleOrDefaultAsync(t => t.Id == 99))?.Id);
using Microsoft.EntityFrameworkCore;
public class Todo
{
public int Id { get; set; }
public string Title { get; set; } = "";
public bool Done { get; set; }
public int CategoryId { get; set; }
public Category Category { get; set; } = null!;
}
public class Category
{
public int Id { get; set; }
public string Name { get; set; } = "";
public List<Todo> Todos { get; set; } = [];
}
public class TodoDbContext(DbContextOptions<TodoDbContext> options) : DbContext(options)
{
public DbSet<Todo> Todos => Set<Todo>();
public DbSet<Category> Categories => Set<Category>();
}<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.Sqlite" Version="10.0.12" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="10.0.3" />
</ItemGroup>
</Project>Run from the repository root. You do not need to install a database service:
cd samples/efcore-sql-cheatsheet
dotnet runExpected output:
Filter and projection: [{"id":1,"title":"Write report","category":"Work"}]
Second page: [{"id":3,"title":"Buy milk"}]
Incomplete count: 2
Any incomplete tasks?: true
First item: 1
Missing ID: null
Category and tasks: {"name":"Work","titles":["Write report","Review PR"]}
Database Done before save: false
Database Done after save: true
Count after Add: 3
New ID: 4
Count after save: 4
Count after delete: 3The initial data matches Chapter 16: the Work category has Write report (incomplete) and Review PR (complete); the Life category has Buy milk (incomplete). Their IDs are 1, 2, and 3, respectively.
Common query patterns
The table below covers database queries in EF Core. After ToListAsync(), the collection is in memory; a subsequent call to Where filters it in C# and does not add a condition to the earlier SQL query.
| Need | LINQ / EF Core | PostgreSQL equivalent |
|---|---|---|
| Incomplete tasks | Where(t => !t.Done) | WHERE NOT "Done" |
| Incomplete tasks in a category | Where(t => t.CategoryId == categoryId && !t.Done) | WHERE "CategoryId" = 1 AND NOT "Done" |
| Return titles only | Select(t => t.Title) | SELECT "Title" |
| Sort by title, then by ID for ties | OrderBy(t => t.Title).ThenBy(t => t.Id) | ORDER BY "Title", "Id" |
| Sort by ID descending | OrderByDescending(t => t.Id) | ORDER BY "Id" DESC |
| Two items per page; return the second page | OrderBy(t => t.Id).Skip(2).Take(2) | ORDER BY "Id" LIMIT 2 OFFSET 2 |
| Count incomplete tasks | CountAsync(t => !t.Done) | SELECT COUNT(*) FROM "Todos" WHERE NOT "Done" |
| Check whether any incomplete task exists | AnyAsync(t => !t.Done) | SELECT EXISTS (SELECT 1 FROM "Todos" WHERE NOT "Done") |
The example combines filtering, sorting, pagination, and projection to return the IDs, titles, and category names of incomplete tasks in Work. In C#, Skip comes before Take; the corresponding SQL usually puts LIMIT … OFFSET …. Focus on what the operations mean rather than copying their order literally.
Use ThenBy to add a sort condition. Calling OrderBy twice replaces the primary sort, so it does not mean “sort by title, then by ID.” Pagination uses the unique Id to break ties and avoid an indeterminate order. Data changes during pagination can still cause items to be skipped or repeated. See PostgreSQL pagination.
Tip
If you only need to know whether data exists, use AnyAsync() instead of fetching every row with ToListAsync(). Use CountAsync() when you need the number of rows.
Fetching one item: First, Single, and Find
“Returns null when not found” applies to the entity objects in this example. For a value type such as an integer, OrDefault returns that type's default value.
| Method | When nothing matches | When multiple items match | Use case |
|---|---|---|---|
FirstOrDefaultAsync() | null | Returns the first item | Select one item in a defined order without requiring a unique match |
SingleOrDefaultAsync() | null | Throws an exception | The data should match at most one item |
SingleAsync() | Throws an exception | Throws an exception | Exactly one match is required, such as a fixed category in the example |
FindAsync(id) | null | Looks up by primary key, so it cannot match multiple items | The primary key is known; the current context's tracked object can be reused |
FirstOrDefaultAsync() commonly translates to LIMIT 1. SingleOrDefaultAsync() must detect whether more than one item matched, so providers typically fetch up to two and check the count. Do not treat it as another form of LIMIT 1.
FindAsync first checks whether the current context is tracking an entity with that primary key. If so, it returns that object directly; otherwise it queries the database. This also means that it does not force a fresh read from the database. See Find and FindAsync.
Relationships: Select versus Include
The example has two different needs:
- Category name only: Read
t.Category.NameinSelectso the database returns only the needed column. You do not have toIncludethe category object first. - Category and its Todo objects: Load the collection with
Include(c => c.Todos), then iterate overcategory.Todos.
The second case can use a LEFT JOIN to fetch the category and tasks in one query. SQL returns flat rows, which EF Core then assembles into a Category and its Todos collection. Include describes loading related objects; it is not a fixed SQL keyword.
With AsSplitQuery(), collection loading can be split across multiple SQL statements, so do not memorize “one Include means one JOIN.” That is an optimization topic for later; for now, focus on understanding the single query in this example. See Single versus split queries.
When does SQL actually run?
| Operation | Does it access the database immediately? |
|---|---|
Where, Select, OrderBy, Skip, Take, Include | No; these compose the query |
ToListAsync, FirstOrDefaultAsync, SingleOrDefaultAsync, AnyAsync, CountAsync | Yes; these execute the query |
FindAsync | No query is needed if the current context already tracks this primary key |
AsNoTracking | No; it controls whether query results join change tracking and has no corresponding SQL clause |
ToQueryString | Generates SQL for inspection only; does not execute it |
Change an entity property, call Add, or call Remove in this example | No write yet; changes wait for SaveChangesAsync |
The example changes Todo 1's Done property to true, then reads it again with Select(t => t.Done). Before saving, the value in the database is still false; after saving, it is true. Selecting a single Boolean reads the database value; directly querying the tracked entity again may return the same object from memory. See Tracking queries.
AsNoTracking() is useful for read-only queries, but it is not a database permission and does not prevent other writes. If you modify an untracked object and call SaveChangesAsync(), EF Core does not automatically know what you changed.
Adding, changing, and deleting with SaveChanges
| C# operation | Equivalent SQL operation when saved |
|---|---|
Add(created), then save | INSERT INTO … RETURNING "Id" to get the ID generated by the database |
Change Done on a tracked entity, then save | UPDATE "Todos" SET "Done" = TRUE WHERE "Id" = 1 |
Remove(created), then save | DELETE FROM "Todos" WHERE "Id" = 4 |
The actual SQL may also include parameters, return values, or concurrency checks. The table shows only the effect of each operation in this example.
SaveChangesAsync() saves all pending changes in the context, not just the most recently modified object. In the example, the query still returns 3 rows after Add and before saving; it returns 4 only after the save.
Technical detail
EF Core also provides ExecuteUpdateAsync() and ExecuteDeleteAsync() for bulk updates and deletes. They do not need to load entities first and do not wait for SaveChangesAsync(). They also do not synchronize objects already tracked by the context. This page uses the tracking and save approach from Chapter 17.
Inspect SQL generated by Npgsql
The project also references the PostgreSQL provider Npgsql. Run this from the same directory:
dotnet run -- --postgres-sqlThis time, UseNpgsql translates the query at the start of the program, and ToQueryString() prints it. The program does not connect to PostgreSQL or create tables. With the dependency versions pinned in the project, the output is:
-- @categoryId='1'
-- @p2='2'
-- @p='0'
SELECT t0."Id", t0."Title", c."Name" AS "Category"
FROM (
SELECT t."Id", t."CategoryId", t."Title"
FROM "Todos" AS t
WHERE t."CategoryId" = @categoryId AND NOT (t."Done")
ORDER BY t."Title", t."Id"
LIMIT @p2 OFFSET @p
) AS t0
INNER JOIN "Categories" AS c ON t0."CategoryId" = c."Id"
ORDER BY t0."Title", t0."Id"You can see that the provider uses a subquery and turns the category ID and pagination values into parameters instead of mechanically joining the SQL shown in the table above.
Note
ToQueryString() is a debugging preview. The parameter comments at the start are not PostgreSQL variable declarations, so you cannot paste the full text containing @categoryId directly into psql. To observe the command that actually runs, use logging as shown in Chapter 16. See ToQueryString.
The same C# method does not guarantee the same translation across databases. For example, PostgreSQL's ILIKE can be used through Npgsql's EF.Functions.ILike; it is not a feature available from the SQLite provider used throughout this tutorial. See Npgsql translations.
Run an equivalent script in PostgreSQL
The standalone SQL comparison file below creates the same initial data, then queries, updates, inserts, and deletes it. It uses temporary tables in the current session and rolls back at the end, leaving existing business tables unchanged.
Expand the complete PostgreSQL script
-- PostgreSQL comparison: run this entire file in one connection.
-- Temporary tables are visible only in this session; the final rollback leaves existing business tables unchanged.
BEGIN;
CREATE TEMP TABLE "Categories" (
"Id" integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
"Name" text NOT NULL
) ON COMMIT DROP;
CREATE TEMP TABLE "Todos" (
"Id" integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
"Title" text NOT NULL,
"Done" boolean NOT NULL DEFAULT FALSE,
"CategoryId" integer NOT NULL REFERENCES "Categories" ("Id")
) ON COMMIT DROP;
INSERT INTO "Categories" ("Name") VALUES ('Work'), ('Life');
INSERT INTO "Todos" ("Title", "Done", "CategoryId") VALUES
('Write report', FALSE, 1),
('Review PR', TRUE, 1),
('Buy milk', FALSE, 2);
-- Where + OrderBy + ThenBy + Skip + Take + Select
SELECT t."Id", t."Title", c."Name" AS "Category"
FROM "Todos" AS t
JOIN "Categories" AS c ON t."CategoryId" = c."Id"
WHERE t."CategoryId" = 1 AND NOT t."Done"
ORDER BY t."Title", t."Id"
LIMIT 2 OFFSET 0;
-- Second page: two rows per page, with a stable order first
SELECT "Id", "Title" FROM "Todos" ORDER BY "Id" LIMIT 2 OFFSET 2;
-- CountAsync / AnyAsync
SELECT COUNT(*) FROM "Todos" WHERE NOT "Done";
SELECT EXISTS (SELECT 1 FROM "Todos" WHERE NOT "Done");
-- FirstOrDefaultAsync: SQL returns zero rows when no record matches; EF Core returns null.
SELECT * FROM "Todos" ORDER BY "Id" LIMIT 1;
-- SingleOrDefaultAsync: fetch at most two rows to check whether multiple records matched.
SELECT * FROM "Todos" WHERE "Id" = 99 LIMIT 2;
-- Single-query Include example: SQL returns flat rows; EF Core assembles the category and its task collection.
SELECT c."Id", c."Name", t."Id" AS "TodoId", t."Title"
FROM "Categories" AS c
LEFT JOIN "Todos" AS t ON c."Id" = t."CategoryId"
WHERE c."Id" = 1
ORDER BY c."Id", t."Id";
-- FindAsync: query the database only when the current context is not already tracking this primary key.
SELECT * FROM "Todos" WHERE "Id" = 1 LIMIT 1;
-- Equivalent operation for changing a tracked property and calling SaveChangesAsync
UPDATE "Todos" SET "Done" = TRUE WHERE "Id" = 1;
SELECT "Done" FROM "Todos" WHERE "Id" = 1;
If PostgreSQL is installed, run this from the example directory. Replace the host, account, and database name for your environment; psql will prompt for the password:
psql -X -h localhost -U postgres -d postgres -v ON_ERROR_STOP=1 -f PostgreSql.sqlYou can also run the whole file in a database tool using the same connection. The main results should be:
| Operation | Result |
|---|---|
| Incomplete tasks in Work | 1 / Write report / Work |
| Second page | 3 / Buy milk |
| Incomplete count, any incomplete task | 2, true (psql displays t) |
| ID 99 | Zero rows; this is the database result before EF returns null |
| Work and its related tasks | Two rows: Write report and Review PR |
Done after the update | true (t) |
| New task | Returns ID 4; total count becomes 4 |
| Delete the new task | Total count returns to 3 |
The double quotes in the script preserve the case of names such as Todos and CategoryId, matching the default mapping in this example. PostgreSQL folds unquoted identifiers to lowercase, so do not treat "Todos" and todos as interchangeable. See PostgreSQL identifier rules.
Five things to remember
- LINQ composes a query first; it runs when you fetch a list or single item, or calculate a result.
- Fix the ordering before paginating.
ThenByadds a sort condition;OrderByresets the primary sort. - Use a projection to fetch only a related field; consider
Includewhen you need the related object. - Tracking,
Add,Remove, and saving are separate steps. Changing an in-memory object does not mean it has been written to the database. - PostgreSQL equivalents help explain the ideas; inspect Npgsql to see the actual translation and verify execution against a database.
Return to the tutorial: 15 EF Core basics · 16 Relationships and queries · 17 Complete CRUD. You can also see the FastAPI ↔ ASP.NET Core comparison.