Skip to main content

Site Column Map Guide

The ISiteColumnMap interface is a core component of the Site Profile system. It provides a bridge between C# property names and site-specific database column names, allowing your application to query diverging schemas without changing business logic.

What is ISiteColumnMap?​

ISiteColumnMap maps property names to database columns at runtime. It has three primary capabilities:

  1. Rename: Map a property to a custom column name.
  2. Remove: Exclude a property from queries if the column doesn't exist for a site.
  3. Add: Include extra columns that exist only for a specific site.

Default Mapping Convention​

Muonroi provides a DefaultSiteColumnMap that follows the ecosystem's standard naming convention: PascalCase property names are converted to UPPER_SNAKE_CASE column names.

  • BookingNo → BOOKING_NO
  • ContainerNo → CONTAINER_NO
  • Id → ID

Most sites should inherit from DefaultSiteColumnMap and override only the columns that diverge from this convention.

Customizing Mappings (The Bravo Example)​

The following example demonstrates how to implement a custom column map for a site named "Bravo".

using Muonroi.Tenancy.SiteProfile.Web.Dapper;

namespace MyProject.Sites.Bravo;

public sealed class BravoColumnMap : DefaultSiteColumnMap
{
    // Define extra columns specific to this site
    private static readonly SiteExtraColumn[] s_extras =
    [
        // Params: PropertyName, ColumnName, ClrType, IsNullable
        new("TrackingReference", "BRAVO_TRACKING_REF", typeof(string), true),
    ];

    public override string Column(string propertyName) => propertyName switch
    {
        // 1. RENAME: Bravo uses BOOKING_NUMBER instead of the standard BOOKING_NO
        "BookingNo" => "BOOKING_NUMBER",
        
        // Use default convention for all other properties
        _ => base.Column(propertyName)
    };

    // 2. REMOVE: Bravo does not have the 'LegacyField' column
    public override bool HasColumn(string propertyName) => propertyName != "LegacyField";

    // 3. ADD: Bravo has a tracking reference column not in the core entity
    public override IReadOnlyList<SiteExtraColumn> ExtraColumns => s_extras;
}

Per-Entity Column Mapping​

Sometimes the same property name maps to different columns depending on which table the query targets. For example, BookingNo might be BOOKING_NUMBER in the CHART_DATA table but ORDER_BOOKING_EXT in the ORDER_DETAIL table.

The Column(string propertyName, string tableName) overload solves this:

// ISiteColumnMap interface — default implementation delegates to Column(propertyName)
string Column(string propertyName, string tableName) => Column(propertyName);

Override Pattern​

Override the two-parameter Column method using a switch expression on both property and table:

public sealed class BravoColumnMap : DefaultSiteColumnMap
{
    public override string Column(string propertyName, string tableName)
        => (propertyName, tableName) switch
        {
            ("BookingNo", "ORDER_DETAIL") => "ORDER_BOOKING_EXT",
            ("BookingNo", _) => "BOOKING_NUMBER",
            _ => base.Column(propertyName)
        };
}

How it works:

  • ("BookingNo", "ORDER_DETAIL") — matches the specific table, returns the table-specific column
  • ("BookingNo", _) — wildcard fallback for all other tables using this property
  • _ => base.Column(propertyName) — delegates to the single-parameter Column() for all other properties

Zero Breaking Change​

The default implementation of Column(string, string) delegates to Column(string). Sites that do not need per-entity mapping require no changes — existing column maps continue to work exactly as before.

See SQL Builder Guide for how SelectFrom() and Col() use this overload automatically.

Registration​

Column maps require two registrations: the keyed singleton and the site resolver.

// Step 1: Register site-specific column maps (keyed by site ID)
services.AddKeyedSingleton<ISiteColumnMap, BravoColumnMap>(SiteIds.BRAVO);
services.AddKeyedSingleton<ISiteColumnMap, TciColumnMap>(SiteIds.TCI);
// DEFAULT uses DefaultSiteColumnMap automatically (no registration needed)

// Step 2: Register the site resolver (resolves correct map per request)
services.AddSiteResolvedService<ISiteColumnMap>();
Both lines are required

AddKeyedSingleton registers the map for a specific site. AddSiteResolvedService registers the factory that resolves the correct map based on the current site code at request time. Without AddSiteResolvedService, injecting ISiteColumnMap will fail.

Registration in SiteProfile​

Alternatively, register in RegisterAdditionalServices inside each site profile:

public partial class BravoSiteProfile
{
    partial void RegisterAdditionalServices(IServiceCollection services, IConfiguration configuration)
    {
        services.AddKeyedSingleton<ISiteColumnMap, BravoColumnMap>(SiteIds.BRAVO);
    }
}

The AddSiteResolvedService<ISiteColumnMap>() call is typically made once in Program.cs or in a shared infrastructure setup method.

Default Fallback​

Sites without a custom ISiteColumnMap registration automatically fall back to DefaultSiteColumnMap (PascalCase → UPPER_SNAKE_CASE convention). You do not need to explicitly register DefaultSiteColumnMap for the default site.

Two-Layer Mapping Architecture​

Muonroi uses column mapping at two different layers to ensure consistency:

LayerTechnologyMechanism
PersistenceEF CoreIEntityTypeConfiguration<T> in the site's DbContext.
Raw SQLDapperISiteColumnMap used by SiteSqlBuilder.
Sync Required

It is critical to keep your EF Core configurations and your ISiteColumnMap in sync. If you rename a column in EF Core, you must also rename it in your ISiteColumnMap to ensure Dapper queries behave correctly.

Impact on Queries​

  • SiteSqlBuilder.Select(): Automatically filters out columns where HasColumn() returns false.
  • SiteSqlBuilder.Col(): Returns the mapped column name (e.g., BOOKING_NUMBER).
  • InterpolateMarkers(): Replaces [[PropertyName]] with the mapped column name and throws if the column is removed.

Source Files​

  • src/Muonroi.Tenancy.SiteProfile.Web/Dapper/ISiteColumnMap.cs
  • src/Muonroi.Tenancy.SiteProfile.Web/Dapper/DefaultSiteColumnMap.cs
  • src/Muonroi.Tenancy.SiteProfile.Web/Dapper/SiteExtraColumn.cs
  • samples/TestProject.Service/src/TestProject.Service.Sites.Bravo/BravoColumnMap.cs

Next Steps​