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 IBM i specific levers: Unicode storage, pinning an exact store 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 from HasMaxLength, otherwise from 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 (CCSID 1208 by default)
    public string Name { get; set; } = "";

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

Or in fluent, in 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 of UseNTi switches the whole model to Unicode; [Unicode(false)] then excludes a property from it. See Introduction and configuration 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 store type: HasColumnType

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

• NUMERIC is the zoned decimal, DECIMAL is the packed decimal: the choice commits 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 expected by the existing RPG program
    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 database (legacy)

Reverse engineering works with the standard tooling (one 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 reads legacy artifacts:

• zoned (NUMERIC) and packed (DECIMAL) decimals, rendered with their exact store type so that later migrations do not alter them;
• FOR BIT DATA columns (CCSID 65535), binary by nature: mapped as byte[], 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 of an entity scaffolded from 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 on the client side from a DB2 for i sequence (NEXT VALUE FOR), by block reservation: no identity round trip per insert. It 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: the DB2 for i analogue of SQL Server's rowversion. On a concurrent modification, 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 tracked by the context is stale as soon as 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();   // fetches the new ROW CHANGE TIMESTAMP

product.Name = "Corrected name";
await db.SaveChangesAsync();

What's next?

• Introduction and configuration: versions, UseNTi, provider options
• Migrations: Migrate, idempotent scripts, DB2 for i limitations
• CRUD with EF Core 8: complete example with Entity Framework Core on DB2 for i