Entity Framework Core for IBM i with NTi

Overview

Entity Framework Core is the reference ORM in the .NET ecosystem. It allows a database to be manipulated directly from C# objects, without writing SQL manually: queries, migrations and mapping are all handled automatically.

With the NTi EF Core extension, this approach applies natively to DB2 for i on IBM i. Compatible with .NET 8, .NET 9 and .NET 10, .NET developers can work with an IBM i database exactly as with any other database supported by EF Core, using the same tooling, the same conventions and the same commands.

• Code First or DB First: start from scratch with C# entities and let EF Core create the tables, or generate your entities from an existing DB2 for i database.
• LINQ to SQL: write your queries in C#, EF Core automatically translates them into SQL optimized for DB2 for i.
• Automatic migrations: evolve your schema by updating your entities, EF Core generates and applies the changes to the database automatically.
• Database-agnostic: the same syntax and the same code work on DB2 for i as on SQL Server, PostgreSQL or Oracle.
• Cross-platform: deploy on Windows, Linux, macOS, Docker...

For example, this C# LINQ query:

var result = db.Orders.Where(x => x.Products.Name.Contains(filter));

Is automatically translated into SQL by EF Core:

SELECT o.ORDERID, o.PRODUCTID, o.QUANTITY, o.ORDERDATE
FROM ORDERS AS o
INNER JOIN PRODUCTS AS p ON o.PRODUCTID = p.PRODUCTID
WHERE POSITION(@filter, p.NAME) > 0

💡This documentation does not cover general Entity Framework Core concepts in detail, as these are standardized. For advanced topics and EF Core best practices, refer to the official Microsoft documentation.

Prerequisites and installation

Prerequisites

• .NET SDK 8, 9 or 10
• Visual Studio 2022 or equivalent.
• An accessible DB2 for i database.

Installation

Add the NuGet package Aumerial.EntityFrameworkCore via the NuGet package manager in Visual Studio, or from the command line:

dotnet add package Aumerial.EntityFrameworkCore

Then import the namespace in your project:

using Aumerial.EntityFrameworkCore;

Microsoft.EntityFrameworkCore.Design is required for migrations and scaffolding. Use the version matching your .NET version to avoid any incompatibility.

Configuration du DbContext

Create a class inheriting from DbContext, which will define your entities and database configuration:

using Microsoft.EntityFrameworkCore; 

public class AppDbContext : DbContext 
{ 
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { } 
    
    // Add your DbSet here  
    public DbSet<MyEntity> MyEntities  { get; set; } 
}

In a web project, register your DbContext in Program.cs via dependency injection:

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");

builder.Services.AddDbContextFactory(options =>
    options.UseNTi(connectionString));

For asynchronous tasks or background services, use AddDbContextFactory.

Handling specific data types

BLOB support

Store files or images directly in DB2 for i using BLOB column types:

[Column(TypeName = "BLOB(1M)"), DataType(DataType.Upload)]
public byte[] ProfilePhoto { get; set; }

Global length configuration

Set a default length for varchar, varbinary and vargraphic types when registering the DbContext:

builder.Services.AddDbContextFactory<AppDbContext>(options =>
    options.UseNTi(connectionString, opt => opt.VarfieldMaxLength(1024, 256, 512)));

• 1024: default length for VARCHAR
• 256: for VARBINARY
• 512: for VARGRAPHIC

You can also specify the length directly on your entities using the [MaxLength] attribute:

[Column(TypeName = "VARCHAR"), MaxLength(128)]
public string Description { get; set; }

Create entities and migrations (Code First)

Choose the schema

Add a default schema to your connection string so that all created tables are placed in it:

server=Server;user=User;password=Pwd;database=MYSCHEMA;

To place specific tables in dedicated schemas, configure them explicitly in the DbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>().ToTable("Product", "MYSCHEMA");
    modelBuilder.Entity<Order>().ToTable("Order", "MYSCHEMA");
}

Define the entities

Create classes to represent your tables:

public class Order
{
    public int Id { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal TotalAmount { get; set; }
}

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Order> Orders { get; set; }
}

Add these entities to the DbContext:

public DbSet<Order> Orders { get; set; }
public DbSet<Product> Products { get; set; }

Generate and apply a migration

Create an initial migration:

dotnet ef migrations add InitialCreate

Apply the migration to create the tables in DB2 for i:

dotnet ef database update

To add or modify a table, update the relevant entity, then generate a new migration:

dotnet ef migrations add NomDeLaMigration
dotnet ef database update

To remove the last migration before it is applied:

dotnet ef migrations remove

To roll back to a previous database version:

dotnet ef database update NomDeLaMigration

Replace MigrationName with the name of the migration you want to roll back to, for example InitialCreate.

Retrieve entities from an existing database (DB First)

To generate entities from an existing DB2 for i database, first create an empty DbContext:

public class MyDbContext : DbContext
{
    public MyDbContext(DbContextOptions<MyDbContext> options) : base(options) { }
}

Then run the scaffold command:

dotnet ef dbcontext scaffold "server=myserver;user=myuser;password=mypassword;database=mydb" Aumerial.EntityFrameworkCore --output-dir Data/Models --schema mydb

• --output-dir : location of the generated entities (e.g. Data/Models)
• --schema : schema to retrieve (e.g. myschema)

If no migration has been created yet, generate an empty initial migration:

dotnet ef migrations add InitialCreate

Empty the Up and Down methods in the generated file to avoid any accidental modification of existing tables:

protected override void Up(MigrationBuilder migrationBuilder)
{
    // Leave empty
}

protected override void Down(MigrationBuilder migrationBuilder)
{
    // Leave empty
}

This prevents accidentally dropping or recreating existing tables when applying migrations.

To add a new table, create the corresponding entity, add it to the DbContext, then generate and apply a migration:

public class MyNewTable
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime CreatedAt { get; set; }
}

public DbSet<MyNewTable> MyNewTables { get; set; }

dotnet ef migrations add AddMyNewTable
dotnet ef database update

What's next?

• CRUD avec EF Core 8 - exemple complet avec Entity Framework Core sur DB2 for i
• Connexion - chaîne de connexion, pool, MFA
• Guide de démarrage rapide - première connexion et premiers appels IBM i