Docs
Kiwi ORM
Version Current

How-to guide

Entity mode

Map tables to typed entities, query with C# expressions, insert, update and delete, track changes and navigate relationships.

ORM Current 3 min read Updated

Prerequisites

  • A context and an entity (see the [quickstart](../getting-started/quickstart.md))

Tip

Included in the free, source-available edition. See Editions and features.

Entity mode is for strongly typed, domain-oriented access. You get CRUD without hand-written SQL, change tracking and relationship mapping, while queries stay transparent and fast.

Map a table#

csharp
[DbContextObject("Benchmarks")]
[TableInfo("bm_orders")]
public class Order : Entity<Order>
{
    private int _orderId;
    private int _customerId;
    private decimal _totalAmount;

    [ColumnInfo("orderid", IsPrimaryKey = true, IsAutoIncrement = true)]
    public int OrderId { get => _orderId; set => SetAndNotify(ref _orderId, value); }

    [ForeignKey("bm_customers", "customerid")]
    [ColumnInfo("customerid")]
    public int CustomerId { get => _customerId; set => SetAndNotify(ref _customerId, value); }

    [ColumnInfo("totalamount")]
    public decimal TotalAmount { get => _totalAmount; set => SetAndNotify(ref _totalAmount, value); }

    // navigation: resolved through the foreign key
    [ColumnReference(nameof(CustomerId))]
    public EntityRef<Customer> Customer { get; set; } = null!;
}
  • [TableInfo] names the table (schema-qualified where needed). [ColumnInfo] maps a column and carries IsPrimaryKey, IsAutoIncrement and similar settings.
  • [ForeignKey] and [ColumnReference] declare a relationship. Composite keys are supported. EntityRef<T> is a reference navigation and EntityList<T> a collection navigation.
  • SetAndNotify records which properties changed, which is what makes SaveChanges work.

To avoid the boilerplate, generate entities from a database (Generate entities and migrations) or let the Roslyn source generator create the properties from fields.

Read#

csharp
// by primary key
var c = await db.SelectByPrimaryKeyAsync(new Customer { CustomerId = 42 });

// filter, order, page
var london = await db.Select<Customer>()
    .Where(c => c.City == "London" && c.IsActive)
    .OrderBy(c => c.CustomerId)
    .ExecuteAsync();

var top = await db.Select<Customer>()
    .Where(c => c.Country == "UK")
    .OrderByDescending(c => c.CustomerId)
    .Take(100)
    .ExecuteAsync();

// projection to an anonymous or DTO type
var slim = await db.Select<Customer>()
    .Where(c => c.City == "London")
    .OrderBy(c => c.CustomerId)
    .ExecuteAsync(c => new { c.CustomerId, c.Name });

Filter expressions are translated to parameterised SQL, so values are never concatenated into the statement.

Filter by a parent while loading children:

csharp
var items = await db.Select<OrderItem>()
    .WhereRelated<Order>(o => o.CustomerId == id, i => i.Order)
    .OrderBy(i => i.ItemId)
    .ExecuteAsync();

Large binary columns are not loaded with the row. Load them on demand with LoadBlobs (all blob columns) or LoadBlob (one property). Use SyncByPrimaryKey to refresh an entity from the database.

Write#

csharp
await db.Insert(new Customer { Name = "Ada", City = "London", IsActive = true }).ExecuteAsync();

customer.City = "Leeds";
await db.Update(customer).ExecuteAsync();      // update from the entity
db.SaveChanges(customer);                      // writes only the properties that changed

await db.Delete(new Customer { CustomerId = 42 }).ExecuteAsync();

There are also set-based Update<T>() and Delete<T>() command builders that apply a condition to many rows. Insert accepts either an object or an initialiser action (db.Insert<Customer>(c => c.Name = "Ada")).

Change tracking#

SaveChanges(entity) compares the properties flagged by SetAndNotify and updates only those columns. Because change tracking is explicit and per entity, there is no hidden unit-of-work state to reason about.

Entity mode and the rest of the pipeline#

Entity commands share the connection, transaction and (in the Enterprise edition) audit trail with every other mode, so you can mix them freely. See Transactions.

Expected result

  • Typed reads and writes with change tracking and relationship loading