Configure Entity Types, Properties, Tables, Schemas, Columns, and Facets with Fluent API

Learning outcomes

ServiceHub's WorkOrder class already has useful C# types, but a production relational contract needs more than “EF found five properties.” Column names, nullability, maximum lengths, numeric precision, Unicode behavior, comments, and schemas affect DDL, data integrity, storage, query behavior, migrations, and portability. This lesson uses Fluent API to make those decisions visible while keeping provider differences explicit.

1. Start with the relational contract, not the Fluent API catalog

For each persisted value, ask two questions: what does the domain mean, and what must the database guarantee/store? string in C# does not encode a database length. decimal does not by itself choose the same precision and scale on every provider. Nullable-reference syntax can participate in convention inference, but the database NULL constraint is still a relational concern.

2. Map the table and columns explicitly where naming is intentional

protected override void OnModelCreating(ModelBuilder modelBuilder){    var workOrder = modelBuilder.Entity<WorkOrder>();    workOrder.ToTable("work_orders");    workOrder.HasKey(x => x.Id);    workOrder.Property(x => x.Id)        .HasColumnName("work_order_id");    workOrder.Property(x => x.CustomerName)        .HasColumnName("customer_name")        .HasMaxLength(120)        .IsRequired();    workOrder.Property(x => x.Summary)        .HasColumnName("summary")        .HasMaxLength(400)        .IsRequired();    workOrder.Property(x => x.Priority)        .HasColumnName("priority");    workOrder.Property(x => x.OpenedUtc)        .HasColumnName("opened_utc");}

This deliberately changes the Chapter 01 table/column names, so it must be treated as schema evolution. Generate a migration, inspect whether it is a rename or a drop/create, and protect existing data. Do not casually rename a property/table and assume migrations infer intent perfectly. Chapter 15 covers rename/data-preservation strategy in depth.

3. Requiredness is a mapping decision with C# input

With nullable reference types enabled, EF conventions can infer that string is required and string? is optional. Fluent API can make the decision explicit.

workOrder.Property(x => x.CustomerName)    .IsRequired()    .HasMaxLength(120);// Example only if the domain adds an optional dispatcher note:workOrder.Property<string?>("DispatcherNote")    .HasColumnName("dispatcher_note")    .HasMaxLength(500)    .IsRequired(false);

Do not confuse database requiredness with every application validation rule. A NOT NULL column prevents null storage; it does not ensure a string is non-blank, matches a business format, or satisfies authorization rules. Database constraints and application validation complement each other.

4. Length, precision, scale, and Unicode are facets, not universal SQL types

workOrder.Property(x => x.CustomerName)    .HasMaxLength(120)    .IsUnicode(true);// Example new value for later ServiceHub billing estimates:workOrder.Property<decimal>("EstimatedHours")    .HasColumnName("estimated_hours")    .HasPrecision(7, 2);

HasMaxLength, HasPrecision, and IsUnicode add metadata that providers use when selecting store types and DDL. Microsoft explicitly notes that Unicode configuration has no effect for databases that do not distinguish Unicode and non-Unicode text types. SQLite is one such case: its type system differs sharply from SQL Server's nvarchar/varchar distinction.

A facet is not necessarily a runtime validation mechanism. EF does not promise to validate every max length or precision in memory before sending a value. Treat the database schema and application validation as explicit enforcement layers.

5. HasColumnType is a provider-specific escape hatch

// SQL Server-specific example; do NOT add this to the SQLite baseline model.workOrder.Property(x => x.CustomerName)    .HasColumnType("nvarchar(120)");

Specifying the full database type bypasses part of the provider's normal type-selection logic. That can be appropriate for a legacy schema or a required engine feature, but it reduces portability. Prefer provider-neutral facets when they express the requirement; use HasColumnType when the store type itself is part of the contract and test that provider explicitly.

6. Schema mapping is not a universal relational abstraction

// Provider-specific deployment example, not the SQLite mandatory lab.modelBuilder.Entity<WorkOrder>()    .ToTable("work_orders", schema: "servicehub");

SQL Server and PostgreSQL have schema namespaces with their own rules. SQLite does not support schemas in that sense; the EF SQLite provider documents schema limitations and may ignore/translate unsupported migration operations. MySQL/MariaDB use database/catalog naming differently. Therefore “same EF model” does not imply “same physical namespace contract.”

Keep the mandatory Chapter 03 lab schema-less on SQLite. Provider-deep-dive chapters later make engine-specific mappings intentional.

7. Comments document schema intent—but provider support still matters

workOrder.ToTable("work_orders", tb =>    tb.HasComment("ServiceHub field-service work orders"));workOrder.Property(x => x.Summary)    .HasComment("Human-readable problem summary supplied by dispatch");

Relational comments are useful where the provider/database persists them, but they are documentation—not enforcement. Verify generated migrations/DDL for the target provider before depending on comments as an operational catalog feature.

8. Inspect the configured relational metadata

using Microsoft.EntityFrameworkCore.Metadata;var entity = db.Model.FindEntityType(typeof(WorkOrder))!;var table = StoreObjectIdentifier.Table(    entity.GetTableName()!, entity.GetSchema());foreach (var property in entity.GetProperties()){    Console.WriteLine($"{property.Name}");    Console.WriteLine($"  column:    {property.GetColumnName(table)}");    Console.WriteLine($"  configuredType: {property.GetColumnType() ?? "<provider convention>"}");    Console.WriteLine($"  resolvedType:   {property.GetRelationalTypeMapping().StoreType}");    Console.WriteLine($"  maxLength: {property.GetMaxLength()?.ToString() ?? "-"}");    Console.WriteLine($"  precision: {property.GetPrecision()?.ToString() ?? "-"}");    Console.WriteLine($"  scale:     {property.GetScale()?.ToString() ?? "-"}");    Console.WriteLine($"  unicode:   {property.IsUnicode()?.ToString() ?? "-"}");}

StoreObjectIdentifier matters because one property can participate in different store-object mappings in advanced scenarios. This is more precise than asking only for a property-level column annotation.

9. Deliberately wrong approach: assume CLR type equals database type

A team adds decimal EstimatedHours, never chooses precision, tests only small values in SQLite, and later deploys to SQL Server. The provider chooses a mapping, but the unspoken precision/scale assumption becomes part of the schema. Another team hard-codes nvarchar(max) everywhere and then claims the model is provider-portable.

10. Hands-on lab: map, migrate, inspect, and explain

1. Change the disposable SQLite mapping from WorkOrders to work_orders and add snake-case column names.
2. Add explicit max lengths and requiredness for CustomerName/Summary.
3. Add a shadow EstimatedHours decimal property with precision 7,2 solely for the mapping experiment.
4. Generate a migration and inspect operations before applying.
5. Generate the SQLite migration SQL and record what facets appear in DDL.
6. Print runtime metadata with StoreObjectIdentifier.
7. Query a seeded work order and print ToQueryString() so column aliases/names are observable.
8. Remove the experiment cleanly or keep it only if the course model intentionally evolves.

dotnet tool run dotnet-ef -- migrations add Chapter03RelationalMapping --project src/ServiceHub.EfLabdotnet tool run dotnet-ef -- migrations script --project src/ServiceHub.EfLab# Review first; then only against the disposable lab:dotnet tool run dotnet-ef -- database update --project src/ServiceHub.EfLab

Verification checklist

• Runtime metadata reports the intended table/column names.
• The migration is reviewed for rename/drop-create risk.
• You can explain which facets SQLite does not enforce like SQL Server would.
• No SQL Server-specific store type exists in the mandatory SQLite model.
• Generated SQL references the mapped column names.

11. Summary and bridge

Fluent API turns implicit mapping assumptions into metadata you can inspect. The important skill is not method memorization; it is separating CLR semantics, provider-neutral relational intent, and provider-specific storage behavior. The same C# property can generate different DDL across engines.

Lesson 3 compares Fluent API with data annotations and conventions. You will deliberately create conflicting configuration, observe which source wins, and establish a team policy that avoids hidden precedence battles.

Authoritative references

• Entity Properties — column names/types, requiredness, max length, precision/scale, Unicode, comments, and property facets
• Entity Types — table/schema mapping and table comments
• Creating and Configuring a Model — Fluent API, annotations, conventions, and model organization
• SQLite EF Core Database Provider Limitations — SQLite schema/type/migrations limitations relevant to portability
• RelationalPropertyExtensions — relational property metadata APIs
• Microsoft.EntityFrameworkCore.Sqlite 10.0.11 — current SQLite provider checkpoint