Querying

db.Table<T>() returns a Table<T>, which is also a Query<T>. Queries are immutable: every method returns a new query, so you can share and extend a base query safely. Nothing runs until you call a terminal method. Every terminal method has an ...Async form and a synchronous form (ToListAsync() / ToList()).

var active = db.Table<Customer>().Where(c => c.Active);           // reusable
var newest = await active.OrderByDescending(c => c.CreatedAt).Take(10).ToListAsync();
var total = await active.CountAsync();

Building a query

Running a query

Aggregates respect Where. After Skip or Take, they apply to that page only, through a subquery:

var total = await orders.Where(o => o.Paid).SumAsync(o => o.Amount);
var oldest = await people.MaxAsync(p => (int?)p.Age);             // null for an empty table
var topThree = await scores.OrderByDescending(s => s.Points).Take(3).SumAsync(s => s.Points);

What translates

The translator handles a deliberately focused subset of C#. Anything else throws NotSupportedException instead of loading the table into memory. For anything outside the subset, use Where($"...").

var ids = new[] { 1, 2, 3 };
var minimum = 18;
db.Table<User>().Where(u => ids.Contains(u.Id) && u.Age >= minimum && u.Email != null);
// WHERE ((("id" IN (@p0, @p1, @p2)) AND ("age" >= @p3)) AND ("email" IS NOT NULL))

Notes:

• LIKE case sensitivity follows the database: case-sensitive in PostgreSQL, case-insensitive for ASCII in SQLite, and set by the collation in SQL Server and MySQL.
• Only mapped properties can appear in a query. Computed C# properties throw.
• Substring past the end of the string returns the shorter result instead of throwing, as SQL does.
• Date parts of a PostgreSQL timestamptz use the session's time zone. MicroORM doesn't translate DateTimeOffset members, because databases disagree on which offset the parts are in.
• Math.Round isn't translated: databases round halves differently from .NET, and from each other. Use Where($"ROUND(price, 2) = {value}") and pick the behaviour you want.
• Sorting by an expression works too: OrderBy(u => u.Name.ToLower()).

Raw conditions

Where($"...") mixes freely with translated predicates:

db.Table<User>()
  .Where(u => u.Active)
  .Where($"date_trunc('day', created_at) = {day}")
  .OrderBy(u => u.Name);

Set-based operations

Update or delete every matching row in one statement, without loading entities:

// UPDATE "users" SET "active" = @p0, "visits" = ("visits" + @p1) WHERE ("last_seen" < @p2)
await db.Table<User>()
    .Where(u => u.LastSeen < cutoff)
    .ExecuteUpdateAsync(s => s
        .Set(u => u.Active, false)
        .SetFrom(u => u.Visits, u => u.Visits + 1));
 
// DELETE FROM "sessions" WHERE ("expires_at" < @p0)
var removed = await db.Table<Session>().Where(s => s.ExpiresAt < DateTime.UtcNow).ExecuteDeleteAsync();

• Set(column, value) assigns a value. SetFrom(column, row => expression) assigns an expression of the row.
• Both methods return the number of affected rows.
• They reject OrderBy, Skip and Take, because those have no portable meaning in UPDATE and DELETE.
• Without Where, they affect the whole table.

ExecuteUpdateReturningAsync does the same update and returns the rows as updated, in the same statement: RETURNING in SQLite and PostgreSQL, OUTPUT INSERTED.* in SQL Server.

// UPDATE "accounts" SET "balance" = ("balance" - @p0) WHERE ("id" = @p1) RETURNING "id", "owner", "balance"
var debited = await db.Table<Account>()
    .Where(a => a.Id == id)
    .ExecuteUpdateReturningAsync(s => s.SetFrom(a => a.Balance, a => a.Balance - amount));

MySQL and MariaDB can't return updated rows, so there it throws NotSupportedException. On SQL Server, OUTPUT fails on a table with triggers.

Subqueries

Any on another typed query becomes EXISTS. Its predicate can use the outer row, so the subquery is correlated:

var orders = db.Table<Order>();
 
// WHERE EXISTS (SELECT 1 FROM "orders" AS "s0" WHERE ("s0"."customer_id" = "customers"."id"))
var buyers = await db.Table<Customer>().Where(c => orders.Any(o => o.CustomerId == c.Id)).ToListAsync();
 
// NOT EXISTS, with the subquery's own filter kept inside it.
var bigSpenders = orders.Where(o => o.Amount > 1_000);
var others = await db.Table<Customer>().Where(c => !bigSpenders.Any(o => o.CustomerId == c.Id)).ToListAsync();
 
// Contains on a projection of one value.
var ordered = orders.Select(o => o.CustomerId);
var withOrders = await db.Table<Customer>().Where(c => ordered.Contains(c.Id)).ToListAsync();

• A subquery that has Where, Skip or Take becomes a derived table, so its own filters (including query filters) and paging apply inside it, as in LINQ.
• Contains is translated to EXISTS, not IN, and matches NULL the way C# does. With NOT IN, a single NULL in the subquery would make the condition match nothing.
• Subqueries nest. Each level gets its own alias (s0, s1, ...), so a query can refer to the same table it's filtering.
• Subqueries also work in Select, where they become CASE WHEN EXISTS (...) THEN 1 ELSE 0 END, and in ExecuteUpdate and ExecuteDelete.
• The subquery itself can't depend on the row: correlate in the predicate (orders.Any(o => o.CustomerId == c.Id)), not with orders.Where(o => o.CustomerId == c.Id).Any(). Both queries must come from databases with the same dialect.
• A subquery is always translated, never run while the outer query is being built.

Projections

Select chooses what each row returns. Filter, sort and page first, then project:

// Anonymous types: each value is aliased with its member name.
var names = await people.Where(p => p.Active).OrderBy(p => p.Name)
    .Select(p => new { p.Id, Upper = p.Name.ToUpper(), Adult = p.Age >= 18 })
    .ToListAsync();
// SELECT "id" AS "Id", UPPER("name") AS "Upper", CASE WHEN ("age" >= @p0) THEN 1 ELSE 0 END AS "Adult" ...
 
// Records (by constructor parameter name), DTOs (by property name) and single values.
var summaries = await people.Select(p => new PersonSummary(p.Id, p.Name, p.Age)).ToListAsync();
var dtos = await people.Select(p => new NameDto { Id = p.Id, Upper = p.Name.ToUpper() }).ToListAsync();
var emails = await people.Where(p => p.Email != null).Select(p => p.Email).ToListAsync();

Each projected value can use anything from What translates. Conditions become CASE WHEN ... THEN 1 ELSE 0 END, because SQL Server doesn't allow a bare predicate in a SELECT list.

A projection has the terminal methods of a query, and a few of its own:

var moods = await people.Select(p => p.Mood).Distinct().ToListAsync();
var cities = await customers.Select(c => c.City).Distinct().CountAsync();

Grouping

GroupBy groups the rows by a key. Select the key and aggregates from each group:

// SELECT "customer_id" AS "CustomerId", COUNT(*) AS "Orders", COALESCE(SUM("amount"), 0) AS "Total"
// FROM "orders" WHERE ("paid" = @p0) GROUP BY "customer_id" HAVING (COUNT(*) > @p1)
// ORDER BY COALESCE(SUM("amount"), 0) DESC LIMIT 10
var top = await db.Table<Order>()
    .Where(o => o.Paid)
    .GroupBy(o => o.CustomerId)
    .Having(g => g.Count() > 1)
    .OrderByDescending(g => g.Sum(o => o.Amount))
    .Take(10)
    .Select(g => new { CustomerId = g.Key, Orders = g.Count(), Total = g.Sum(o => o.Amount) })
    .ToListAsync();

• Where before GroupBy filters rows. Having after it filters groups.
• Sort and page groups with OrderBy, ThenBy, Skip and Take before Select.
• Aggregates over a group: g.Count(), g.Count(x => ...), g.LongCount(), g.Sum(x => ...) (zero when every value is NULL, as in LINQ), g.Min(x => ...), g.Max(x => ...) and g.Average(x => ...) (integers averaged as floating point; decimals stay exact).
• A composite key is an anonymous type: GroupBy(p => new { p.Country, p.City }), then select g.Key.Country and g.Key.City. OrderBy(g => g.Key) sorts by each part.
• A key can be computed, such as GroupBy(p => p.CreatedAt.Year). Computed keys, and sources with Skip or Take, are grouped through a derived table, so the key is computed once and the page is taken before grouping, as in LINQ.
• CountAsync() on a grouped query counts the groups.
• Subqueries aren't supported inside grouped queries, and whole groups can't be selected (Select(g => g)). Select the key and aggregates instead.

For joins and other shapes a single table can't express, write the SQL and materialize into a record (see also Joins and result sets):

public record CustomerTotal(int Id, string Name, decimal Spend);
 
var totals = await db.QueryAsync<CustomerTotal>($"""
    SELECT c.id, c.name, SUM(o.amount) AS spend
    FROM customer c JOIN orders o ON o.customer_id = c.id
    WHERE o.placed_at >= {since}
    GROUP BY c.id, c.name
    """);