Read and write data

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

updated 5 Jun 20262 min readv0.3.2View 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