Skip to main content

Reference CRUD pattern: the City slice

For master-data entities backed by legacy Mas*-prefixed scaffolded tables, follow the five-layer shape established by City end-to-end. Add new entities by copying this shape, not by inventing a new one.

Database-first rules​

  • The DbContext and entities under Infrastructure/Persistence/Scaffolded/ are generated. Never hand-edit generated files.
  • The re-scaffold command lives in the header comment of EosDbContext.cs.
  • Put hand-written additions in EosDbContext.partial.cs so they survive a re-scaffold.
  • The rest of the codebase must go through repository interfaces in Application/Common/Interfaces, never touch scaffolded entities directly.
  • Map scaffolded entities to hand-written domain models in the Infrastructure layer.

:::danger Real, confirmed schema drift across clients — never fetch a full scaffolded entity when you only need a few columns MenuRepository first documented this for MAS_MENU/MAS_APP_MENU/MAS_APP (different clients' real schemas have drifted from the scaffold); confirmed a second time, independently, on MAS_ORDER_TYPES (2026-07-28, building MasterOrder's live-stats computation) — ceva-eos's real table is missing several columns (BLTYPE, MBLType, a few PEMS* fields) the scaffolded MasOrderType model expects, so a full ToListAsync()/FirstOrDefaultAsync() (which SELECTs every mapped column regardless of what's actually used) throws a raw SqlException: Invalid column name.

This isn't a one-off fluke on one table — treat it as a standing risk on any Mas* table, especially the wide ones (MAS_ORDER_TYPES, MAS_MENU, MAS_STATUS, MAS_TRCODE, MAS_USER — anything with 50+ columns that's plausibly grown/changed shape differently per client over the years).

Always use a narrow .Select(x => new { x.OnlyTheColumnsYouNeed }) projection for any new Mas* read, never a bare full-entity fetch — a projection only ever SELECTs the columns it explicitly names, so it can't be broken by a column the current client's real table happens to be missing elsewhere in that same row. :::

Step 1: DTO​

Application/Modules/<Module>/Dto/<Entity>Dto.cs (e.g. CityDto.cs). Plain data shape at the Application boundary, mirroring every field the legacy write SP accepts — not just the fields a first pass happens to need. If the SP takes 17 parameters, the DTO carries all 17; otherwise an Update through the SP silently blanks the columns the DTO omitted. Never return a scaffolded EF entity from a repository.

Step 2: Repository interface​

Application/Common/Interfaces/I<Entity>Repository.cs (e.g. ICityRepository.cs). Declares GetByCodeAsync / ListAsync / CreateAsync / UpdateAsync / DeleteAsync in terms of the DTO. Use the entity's real key type (most Mas* tables key on a string code, not an int) — don't force-fit IReadRepository<T> if the key shape doesn't match.

Step 3: Repository implementation​

Infrastructure/Persistence/Repositories/<Module>/<Entity>Repository.cs (e.g. CityRepository.cs). Split reads and writes by what the legacy system actually does — don't assume one mechanism for both:

  • GetByCodeAsync / ListAsync — plain EF Core (AsNoTracking() + LINQ), mapping the scaffolded entity to the DTO inside the repository. City/Country's legacy Get*Lt list SPs (MAS_GetCityLt / MAS_GetCountryLt) are not used — reads go straight through EF.

  • CreateAsync / UpdateAsync / DeleteAsync — call the legacy combined "manage" stored procedure (e.g. MAS_ManCity, MAS_ManCountryDt) via a raw SqlCommand off context.Database.GetDbConnection(), CommandType.StoredProcedure, always through typed SqlParameters (never string-concatenated SQL). These SPs discriminate the operation with a single int parameter (@Action/@ActionType) — see the ManageAction enum:

    // Infrastructure/Persistence/Repositories/ManageAction.cs
    public enum ManageAction
    {
        Insert = 1,
        Update = 2,
        Delete = 3,
    }

    — and report outcome through an output parameter (@Result): the new row's identity on insert, rows-affected on update/delete. Confirm the real SP name, parameter list/order, and @Result semantics before wiring a new entity up — don't guess. The parameter set a delete needs can differ per SP (City's delete reuses the full insert/update parameter list; Country's delete sends only the key + action type, relying on SP-side defaults) — match what the legacy caller actually sends, not what feels symmetric.

  • Inject ILogger<T>. Log Information for normal reads/writes, Warning for handled failures (not-found, zero rows affected). Include Activity.Current?.Id as CorrelationId on every log line. Never log full DTOs or SP parameter dumps — only codes/counts. Never log lat/long, manifest doc paths, connection strings, or UpdatedBy-style actor fields.

Step 4: DI registration​

Register interface to implementation as scoped in Infrastructure/DependencyInjection.cs.

Step 5: Thin controller​

Api/Controllers/<Consumer>/<Entities>Controller.cs (e.g. CitiesController.cs). Constructor-injects only the repository interface, contains no business logic and no logging of its own (logging lives in the repository, at the DB/SP boundary): GET-all, GET-by-key, POST, PUT, DELETE that just call the repository and return Ok / NotFound / CreatedAtAction / NoContent.

Step 6: In-memory cache the ListAsync result — lookup tables only​

Inject IMemoryCache and wrap ListAsync's query in cache.GetOrCreateAsync(key, ...) with entry.AbsoluteExpirationRelativeToNow set to a sensible duration (1 hour for City/Country). Key convention: "Mas:<Entity>:All". Every CreateAsync/UpdateAsync/successful DeleteAsync must call cache.Remove(key) on success so the cache never serves stale rows after a write — this is not optional, it's the invalidation half of the same pattern.

This applies only to Mas*/lookup/reference tables (rarely-written master data like City, Country, currency, unit-of-measure). Do not cache GetByCodeAsync (single-row reads stay live) and do not apply this pattern to transactional data — Operations/Rf/Reporting/Edi entities (consignments, shipments, statuses, anything that changes per business event) must always read live from the database. Caching transactional data risks serving stale operational state; this pattern exists specifically because Mas* tables change rarely and the whole list is small.

:::caution "The whole list is small" doesn't hold for every Mas* table in every real client's data MAS_CITY turned out to be large enough in at least one real client's database that the first request after a cold cache (app start, or the 1hr TTL expiring) paid a real, multi-second uncached DB query + full-table serialization — noticeable specifically as "the Cities page is slow the first time it's opened," not on later opens once the cache is warm.

Fixed by warming ICityRepository.ListAsync() once at API startup (Program.cs, before app.Run()), so that cost lands at boot instead of on whichever user gets there first — wrapped in try/catch so a warm-up failure can never block the app from starting. If another Mas* entity turns out to be similarly large in some client's real data, give it the same startup warm-up rather than leaving the first user to pay for it. :::

Reference files: CityDto.cs, ICityRepository.cs, CityRepository.cs, CitiesController.cs, ManageAction.cs (and their Country counterparts), MasListRepositoryBase.cs.

Shared read-side base class​

MasListRepositoryBase<TEntity, TDto> (Infrastructure/Persistence/Repositories/Mas/MasListRepositoryBase.cs) factors out the cache-or-create block behind Step 6's ListAsync/ListByTypeAsync and the DB-paged Skip/Take behind ListPagedAsync, plus their log lines — CityRepository/CountryRepository/ChoiceRepository all inherit it today.

It's read-only by design: writes (Step 3) stay entity-specific (SqlCommand/MAS_Man* vs. plain EF, different key shapes) rather than force-fit into the base. Its two protected methods take the query, sort, and map as delegates supplied by the caller rather than overridden abstracts — this is what lets Choice's ListByTypeAsync (active-only) and ListPagedAsync (all rows, see the exception note below) use two different base queries for the same entity without any per-instance state.

Reuse this base for the next simple Mas* reference-data repository that would otherwise duplicate this shape; don't force-fit a territory-gated or otherwise-special entity (WarehouseOrder, etc.) into it. See the Server-Side Pagination page and the Territory Access Pattern page for more on gated entities.

Exception: entities with no legacy manage stored procedure​

Step 3 above assumes a MAS_Man* SP exists to write through. Confirm that before assuming it — some Mas* tables never had one (nothing in the legacy app ever wrote them through a stored proc).

MAS_CHOICES (ChoiceRepository) is the first such entity: its CreateAsync/UpdateAsync/DeleteAsync go through plain EF Core (context.MasChoices.Add/Remove + SaveChangesAsync) instead of a SqlCommand/ManageAction call. Everything else about the pattern still applies unchanged — DTO mirrors every writable column, cache invalidation on every successful write, ILogger at Information/Warning, thin controller. If a future entity also turns out to have no manage SP, use this same plain-EF-Core write path rather than inventing a third mechanism.

MAS_CHOICES also composite-keys on (ChoiceType, ChoiceCode) and is shared by many unrelated picklists — its admin page (ChoicesPage.tsx) filters the grid by a ChoiceType selector (sourced from the separate MAS_CHOICE_TYPES lookup table via ChoiceTypesController/IChoiceTypeRepository, same read-only-lookup shape as AddressTypesController) rather than showing one global unfiltered grid.

Its GetByType endpoint also has a real semantic difference between its two paged/unpaged shapes, not just a payload-size one: unpaged (page omitted) stays active-only and cached — what real consumers like LanguageSelector read — while the paged admin shape includes inactive rows and is never cached, since it's the write surface itself and needs to reflect edits immediately.

Reference files: ChoiceDto.cs, ChoiceTypeDto.cs, IChoiceRepository.cs, IChoiceTypeRepository.cs, ChoiceRepository.cs, ChoiceTypeRepository.cs, ChoicesController.cs, ChoiceTypesController.cs, ChoicesPage.tsx.