Map Stored Procedures, Views, Keyless Types, Legacy Constraints, and Imperfect Schemas Pragmatically

Learning outcomes

1. The practical problem: legacy databases contain more than clean tables with primary keys

The final ServiceHub legacy slice contains a reporting view, an import table with no primary key, awkward historical names, and a procedure contract in the production SQL Server deployment. An ORM works best when each updateable entity has stable identity and the database exposes relational constraints that match it. Database-first engineering must not erase the places where that assumption is false.

2. Keyless entity types are query shapes, not tracked entities

EF Core keyless entity types use HasNoKey() or [Keyless]. They are never tracked for changes and are never targets of normal insert/update/delete persistence. They are appropriate for views, query projections, and legacy tables with no declared key when you only need a read model.

CREATE VIEW work_order_queue_view ASSELECT  w.work_order_id,  w.work_order_number,  w.customer_name,  w.priority,  t.display_name AS technician_nameFROM work_orders AS wLEFT JOIN technicians AS t  ON t.technician_id = w.assigned_technician_id;

[Keyless]public sealed class WorkOrderQueueRow{    public long WorkOrderId { get; init; }    public string WorkOrderNumber { get; init; } = null!;    public string CustomerName { get; init; } = null!;    public long Priority { get; init; }    public string? TechnicianName { get; init; }}modelBuilder.Entity<WorkOrderQueueRow>(builder =>{    builder.HasNoKey();    builder.ToView("work_order_queue_view");});

ToView tells EF to treat the mapped object as a read-only query source; EF does not create the view merely because the mapping exists.

3. Reverse engineering can scaffold views and no-key tables—but that does not create identity

Modern EF reverse engineering can generate keyless types for views and tables without keys. The absence of a key is a hard semantic boundary. A repeated row from a view is not identity-resolved like a normal entity, and EF will not track it for updates.

CREATE TABLE legacy_import_rows (    import_batch TEXT NOT NULL,    source_line  INTEGER NOT NULL,    customer_text TEXT NULL,    payload_text  TEXT NOT NULL    -- deliberately no PRIMARY KEY);

4. Query keyless/view data and verify tracker behavior

var rows = await db.Set<WorkOrderQueueRow>()    .Where(x => x.Priority >= 3)    .OrderByDescending(x => x.Priority)    .ThenBy(x => x.WorkOrderId)    .ToListAsync(cancellationToken);Console.WriteLine($"Tracked entries: {db.ChangeTracker.Entries<WorkOrderQueueRow>().Count()}");// Expected: 0. Keyless entity types are not tracked for changes.

Generated SQL still matters. Inspect it with ToQueryString() and the database plan when performance matters. “Read-only” does not mean “cheap.”

5. Stored procedures: separate querying, CUD mapping, and scaffolding

Three ideas are commonly conflated. First, EF can query entity/result shapes from handwritten SQL/procedure calls using raw-SQL APIs, subject to provider composability rules. Second, EF Core supports mapping insert/update/delete operations to stored procedures where the provider/database supports the required behavior. Third, reverse engineering does not turn an arbitrary database procedure catalog into a complete typed application service layer. Procedure contracts need deliberate code.

6. SQL Server optional example: query a procedure safely

SQLite has no stored procedures, so this is an optional SQL Server-style illustration. The mandatory lab remains SQLite and uses a view/raw SQL query.

var minimumPriority = 3;var rows = await db.WorkOrders    .FromSql($"EXEC dbo.GetOpenWorkOrders @MinimumPriority={minimumPriority}")    .AsNoTracking()    .ToListAsync(cancellationToken);

Interpolated EF raw-SQL APIs parameterize values. They do not make dynamic table/procedure/column identifiers safe; identifiers are query shape and must come from trusted, allow-listed code. Also, SQL Server stored-procedure calls generally are not composable as arbitrary subqueries—materialize or design the procedure contract accordingly.

7. Stored procedure write mapping is explicit application model configuration

modelBuilder.Entity<LegacyContact>()    .InsertUsingStoredProcedure("LegacyContact_Insert", sp =>    {        sp.HasParameter(x => x.Name);        sp.HasResultColumn(x => x.Id);    })    .UpdateUsingStoredProcedure("LegacyContact_Update", sp =>    {        sp.HasOriginalValueParameter(x => x.Id);        sp.HasParameter(x => x.Name);        sp.HasRowsAffectedResultColumn();    });

This capability exists in EF Core, but procedure definition, result/rows-affected contract, provider support, triggers, transactions, concurrency, and schema ownership must be tested against the actual engine. The SQLite lab cannot reproduce SQL Server procedure semantics and should not pretend to.

8. Legacy names and types: adapt deliberately, do not falsify the store

A database-first adapter may expose friendlier C# names while mapping exact historical column names. Type conversions can make application code easier to use, but only if they are reversible and consistent with queries/writes. Unsupported provider types may not scaffold at all.

modelBuilder.Entity<LegacyCustomer>(builder =>{    builder.ToTable("T_CUST_1998");    builder.HasKey(x => x.Id);    builder.Property(x => x.Id).HasColumnName("CUST_NO");    builder.Property(x => x.DisplayName).HasColumnName("CUST_NM");    builder.Property(x => x.ActiveFlag).HasColumnName("ACT_FLG");});

If the database stores ambiguous sentinel values, overloaded columns, or denormalized text payloads, document that debt. A converter can change the CLR representation; it cannot create missing constraints or repair corrupted data semantics.

9. Failure case: force an updateable entity onto a no-key table

A developer chooses source_line as the key because it is unique in one import batch. Two batches both contain line 42. EF identity resolution now treats those rows as the same entity key inside one context, or update predicates target the wrong logical row if the database is modified. The repair is to model the real key in the database if one exists, use a composite key only if the database guarantees it, or keep the object keyless/read-only.

10. When a separate data-access path is more honest

Using more than one data-access tool is not architectural failure. The important boundary is explicit correctness, observability, transactions, security, and testability.

11. Lab: view + keyless table + safe raw SQL

1. Add work_order_queue_view and legacy_import_rows to the disposable SQLite database.
2. Re-scaffold the database into scratch and inspect whether the provider generates keyless mappings.
3. Query the view and verify zero tracked entries.
4. Insert duplicate source_line values across different batches and explain why source_line is not a safe key.
5. Create a handwritten keyless LegacyImportRow mapping if the generated shape needs clearer naming.
6. Use parameterized SqlQuery<T> or a keyless query to read a bounded projection; inspect SQL.
7. Attempt no tracked update of the keyless type; instead use a deliberately reviewed raw SQL command only if the lab needs to mutate staging data.
8. Reset the database from DDL.

public sealed class LegacyImportProjection{    public string ImportBatch { get; init; } = null!;    public long SourceLine { get; init; }    public string? CustomerText { get; init; }}public static Task<List<LegacyImportProjection>> LoadImportsAsync(    ServiceHubLegacyContext db,    string batch,    CancellationToken cancellationToken)    => db.Database.SqlQuery<LegacyImportProjection>($"""        SELECT import_batch AS ImportBatch,               source_line AS SourceLine,               customer_text AS CustomerText        FROM legacy_import_rows        WHERE import_batch = {batch}        ORDER BY source_line        """)        .ToListAsync(cancellationToken);

Confirm the parameter is sent as a parameter in EF command logs. Do not concatenate untrusted values into SQL text.

12. Production judgment and bridge to performance engineering

Database-first succeeds when the adapter tells the truth about the store. Views and no-key tables are not automatically updateable entities. Stored procedures are explicit contracts with provider-specific semantics. Legacy naming/types can be mapped, but missing identity and constraints must not be invented in application code. Test the generated/read-write behavior against the actual production-like provider.

Chapter 17 now asks the next operational question: once the model—generated or hand-built—is correct, how much does it cost? The course moves into projection strategy, compiled queries/models, pooling, and evidence from real database execution plans.

Authoritative references

Reverse engineering is provider- and version-sensitive. Re-check these primary sources when regenerating the chapter or adapting it to another database engine.

• Keyless Entity Types - EF Core — keyless tracking/update limitations and view/query scenarios
• Reverse Engineering - EF Core — view/no-key scaffolding and inference limits
• SQL Queries - EF Core — FromSql, SqlQuery, parameterization and composability
• Stored procedure mapping - EF Core 7+ — insert/update/delete stored procedure mapping concepts
• Entity Types - EF Core — table/view mapping semantics
• SQLite provider — mandatory local provider context