Read and write data

Build the endpoints. Insert, query, and stream rows through the typed context sets with predicates instead of SQL.

updated 26 Jun 20262 min readv0.3.3View as Markdown

With the context injected, the endpoints are a few lines each. Every set exposes typed reads and writes that take expression predicates, so the compiler checks your queries.

Create a project

InsertAsync defaults includeAutoFields to false, so the [Default] columns (Id, CreatedAt) are left for the database to fill:

app.MapPost("/projects", (Project project, ISocigyDatabaseFactory<IAppDb> db) =>
    db.ExecuteAsync(d => d.Projects.InsertAsync(project)));

List a project's tasks

ToListAsync takes an optional predicate and materializes the rows inside the scope:

app.MapGet("/projects/{id:guid}/tasks", (Guid id, ISocigyDatabaseFactory<IAppDb> db) =>
    db.ExecuteAsync(d => d.Tasks.ToListAsync(t => t.ProjectId == id)));

Add a task

app.MapPost("/projects/{id:guid}/tasks", (Guid id, TaskItem task, ISocigyDatabaseFactory<IAppDb> db) =>
{
    task.ProjectId = id;
    return db.ExecuteAsync(d => d.Tasks.InsertAsync(task));
});

The set API

Each table set (d.Projects, d.Tasks) exposes:

Method	Returns	Notes
`ToListAsync(predicate?)`	`Task<List<T>>`	all rows, or those matching the predicate
`FirstOrDefaultAsync(predicate)`	`Task<T?>`	first match or `null`
`ExistsAsync(predicate)`	`Task<bool>`	existence check
`CountAsync(predicate?)`	`Task<long>`	`SELECT COUNT(*)`
`InsertAsync(entity, includeAutoFields = false)`	`Task<bool>`	one row
`InsertMultipleAsync(entities, ...)`	`Task<int>`	batched multi-row insert
`UpdateAsync(entity)`	`Task<int>`	update by primary key
`DeleteAsync(predicate)`	`Task<int>`	delete matching rows
`ForEachAsync(predicate, onRow)`	`Task`	stream rows without buffering

To process many rows without loading them all into memory, stream with ForEachAsync:

app.MapPost("/projects/{id:guid}/complete-all", (Guid id, ISocigyDatabaseFactory<IAppDb> db) =>
    db.ExecuteAsync(d => d.Tasks.ForEachAsync(t => t.ProjectId == id, async task =>
    {
        task.Done = true;
        await d.Tasks.UpdateAsync(task);
    })));

NOTE

The set methods (d.Tasks.ToListAsync(...)) are distinct from the static query builder (TaskItem.Query(...)). The builder is for standalone use with your own connection and is covered under Querying; the set methods run inside the context scope.

The last endpoint, creating a project and its tasks atomically, needs a transaction. Continue to Transactions and tests.

← previousWire up the context

next →Transactions and tests