Mapping and DB2 for i types

The provider maps .NET types to DB2 for i types with defaults that work against any existing schema. This page covers the levers specific to IBM i: Unicode storage, pinning an exact storage type (the key to coexisting with RPG and COBOL programs), scaffolding legacy databases, HiLo keys, and ROW CHANGE TIMESTAMP optimistic concurrency.

The mechanisms themselves (annotations, fluent API, conventions) are standard EF Core. See the Microsoft documentation. Only the DB2 for i rendering is detailed here.

Text and Unicode

By default, a string property becomes a VARCHAR column (length given by HasMaxLength, otherwise by the VarcharMaxLength option). The standard [Unicode] annotation (or IsUnicode() in fluent) stores the column as Unicode, in the CCSID configured by the UnicodeCcsid option (default 1208, UTF-8):

using Microsoft.EntityFrameworkCore;

public class Customer
{
    public int Id { get; set; }

    [Unicode]                    // Unicode column (default CCSID 1208)
    public string Name { get; set; } = "";

    [Unicode(false)]             // never Unicode, even if ForceUnicode() is active
    public string Code { get; set; } = "";
}

Or in fluent, on the context:

using Microsoft.EntityFrameworkCore;

public class CustomerContext : DbContext
{
    public DbSet Customers => Set();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity()
            .Property(c => c.Name)
            .IsUnicode();
    }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseNTi("server=MYIBMI;user=MYUSER;password=MYPASSWORD;database=MYLIB;");
}

The ForceUnicode() option on UseNTi switches the whole model to Unicode. [Unicode(false)] then excludes a property from it. See Entity Framework Core for the provider options.

Writes are strict. A value that cannot be represented in the target column's CCSID is rejected with an explicit error, never silently substituted or truncated.

Pinning an exact type: HasColumnType

When a table has to stay consumable by existing RPG or COBOL programs, or match a physical file already in place, pin the exact storage type with [Column(TypeName = ...)] or HasColumnType(...). Two points specific to DB2 for i:

• NUMERIC is the zoned decimal, DECIMAL the packed decimal. The choice dictates the physical format read by native programs.
• GRAPHIC / VARGRAPHIC declare DBCS columns. TIMESTAMP(n) accepts a precision from 0 to 12.

using System;
using System.ComponentModel.DataAnnotations.Schema;

public class InvoiceLine
{
    public int Id { get; set; }

    [Column(TypeName = "NUMERIC(7,2)")]    // zoned: the format the existing RPG program expects
    public decimal Amount { get; set; }

    [Column(TypeName = "DECIMAL(11,0)")]   // packed
    public decimal AccountNumber { get; set; }

    [Column(TypeName = "VARGRAPHIC(50)")]  // DBCS
    public string Label { get; set; } = "";

    [Column(TypeName = "TIMESTAMP(12)")]   // precision from 0 to 12
    public DateTime UpdatedAt { get; set; }
}

The fluent equivalent is Property(e => e.Amount).HasColumnType("NUMERIC(7,2)"). Without HasColumnType or HasPrecision, decimal properties get the precision and scale configured by the DecimalDefaults option.

Scaffolding an existing (legacy) database

Reverse engineering works with the standard tooling, a single pass of dotnet ef dbcontext scaffold:

dotnet ef dbcontext scaffold "server=MYIBMI;user=MYUSER;password=MYPASSWORD;database=MYLIB;" Aumerial.EntityFrameworkCore --output-dir Models

Scaffolding covers tables, views, indexes, foreign keys, comments, and sequences, and knows how to read legacy artifacts:

• zoned (NUMERIC) and packed (DECIMAL) decimals, restored with their exact storage type so later migrations don't alter them.
• FOR BIT DATA columns (CCSID 65535), binary by nature, mapped to byte[] and never converted to text (unless the connection string asserts a default ccsid).
• DECFLOAT columns.
• tables without a primary key, generated keyless (HasNoKey) and queryable for reads.
• views.

// typical excerpt from an entity scaffolded off a legacy physical file
[Column(TypeName = "NUMERIC(5,0)")]
public decimal Qty { get; set; }
public byte[] LegacyData { get; set; } = null!;   // CHAR(16) FOR BIT DATA

HiLo keys

UseHiLo generates keys client side from a DB2 for i sequence (NEXT VALUE FOR), by reserving blocks. No identity round trip per insert. This is the standard EF Core mechanism (sequences), backed here by a DB2 for i sequence created by the migrations.

using Microsoft.EntityFrameworkCore;

public class Order
{
    public int Id { get; set; }
    public string Customer { get; set; } = "";
}

public class OrderContext : DbContext
{
    public DbSet Orders => Set();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity()
            .Property(o => o.Id)
            .UseHiLo("ORDERS_HILO");     // sequence created and managed by the migrations
    }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseNTi("server=MYIBMI;user=MYUSER;password=MYPASSWORD;database=MYLIB;");
}

modelBuilder.UseHiLo() applies the mechanism to the whole model.

Optimistic concurrency: IsRowChangeTimestamp

IsRowChangeTimestamp() declares a concurrency token backed by the native ROW CHANGE TIMESTAMP column, rewritten by the server on every UPDATE. It's the DB2 for i counterpart to SQL Server's rowversion. On a concurrent change, SaveChangesAsync throws the standard DbUpdateConcurrencyException.

using System;
using Microsoft.EntityFrameworkCore;

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public DateTime RowVersion { get; set; }
}

public class CatalogContext : DbContext
{
    public DbSet Products => Set();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity()
            .Property(p => p.RowVersion)
            .IsRowChangeTimestamp();
    }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseNTi("server=MYIBMI;user=MYUSER;password=MYPASSWORD;database=MYLIB;");
}

After an UPDATE, reload the entity before saving it again from the same context. The server regenerates the token on every update, so the value the context is tracking goes stale the moment the save completes.

using Microsoft.EntityFrameworkCore;

await using var db = new CatalogContext();
var product = await db.Products.FirstAsync(p => p.Id == 1);
product.Name = "New name";
await db.SaveChangesAsync();

await db.Entry(product).ReloadAsync();   // picks up the new ROW CHANGE TIMESTAMP
product.Name = "Corrected name";
await db.SaveChangesAsync();

What's next?

• Entity Framework Core : versions, UseNTi, provider options
• Migrations : Migrate, idempotent scripts, DB2 for i limits
• Blazor Server CRUD with EF Core : a complete example with Entity Framework Core on DB2 for i