Where EF Core Fits: ORM Responsibilities, Relational Boundaries, and When Direct SQL Still Matters

Learning outcomes

The ServiceHub field-service platform already has a relational design: technicians receive work orders, customers own service locations, and database constraints protect valid states. The .NET team now wants faster application development without turning the database into an invisible persistence bucket. Entity Framework Core (EF Core) can remove repetitive mapping and data-access code, but only if the team understands exactly where its abstraction begins and ends.

1. ORM means mapping and coordination, not “database without SQL”

An object–relational mapper (ORM) connects an object model in application memory with a relational model in a database. EF Core is Microsoft’s modern ORM for .NET. Its public API lets C# code express models, queries, and persistence operations, but a relational provider must still turn those operations into database commands. For a SQLite lab, the provider is Microsoft.EntityFrameworkCore.Sqlite; for SQL Server it is Microsoft.EntityFrameworkCore.SqlServer. Providers are plug-ins with database-specific translation and type-mapping behavior.

A useful boundary map is C# application → EF Core model/query/change tracker → EF Core provider → ADO.NET driver → database engine. ADO.NET is the lower-level .NET database API family built around connections, commands, parameters, readers, and transactions. EF Core builds on provider services that ultimately use those primitives. The database still decides how SQL is optimized, locked, logged, constrained, and persisted.

2. What EF Core assumes responsibility for

Model metadata describes entity types, properties, keys, relationships, indexes, converters, and relational mappings. LINQ translation turns an IQueryable expression tree into provider-specific SQL where translation is possible. Materialization creates CLR objects from returned rows. For tracking queries, the identity map makes one tracked object instance represent one entity identity, and the change tracker records entity state and property changes. SaveChanges/SaveChangesAsync then translates tracked state into INSERT/UPDATE/DELETE commands. Migrations describe intentional model-to-schema changes over time.

These are substantial responsibilities, but none makes the relational model optional. If the database lacks a uniqueness constraint, EF cannot magically guarantee uniqueness against all writers. If a query has no useful index, generated SQL can still scan millions of rows. If two transactions race, isolation and concurrency rules still decide the outcome. If a database account has excessive privileges, an ORM does not restore least privilege.

3. Generated SQL is observable evidence

Consider a query for open high-priority work orders. The LINQ expression is not the database operation itself; it is a description that a relational provider translates. ToQueryString() is a diagnostic API that displays the SQL representation for many queries before execution. It is useful for learning and code review, but it is not an execution plan, a benchmark, or proof that the database will choose a good access path.

var query = context.WorkOrders    .Where(w => w.Status == WorkOrderStatus.Open && w.Priority >= 3)    .OrderByDescending(w => w.Priority)    .Select(w => new { w.Id, w.CustomerName, w.Priority });Console.WriteLine(query.ToQueryString());var rows = await query.ToListAsync();

With SQLite, the exact aliases and parameter names can vary by EF patch, but the shape should be recognizably a SELECT with a server-side WHERE and ORDER BY. The important observation is not a memorized SQL string. It is that filtering and projection remain on the database side instead of loading every row into .NET first.

SELECT "w"."Id", "w"."CustomerName", "w"."Priority"FROM "WorkOrders" AS "w"WHERE "w"."Status" = @__open_0 AND "w"."Priority" >= 3ORDER BY "w"."Priority" DESC;

4. Runtime model and tracker state are also evidence

EF Core’s abstraction can be inspected from inside the process. context.Model exposes the finalized runtime model. context.ChangeTracker.DebugView exposes tracked entities and their state. These are especially useful when code “looks right” but EF writes an unexpected column or tracks an entity you thought was detached.

var workOrderType = context.Model.FindEntityType(typeof(WorkOrder));Console.WriteLine(workOrderType?.GetTableName());Console.WriteLine(string.Join(", ", workOrderType!.GetProperties().Select(p => p.Name)));var one = await context.WorkOrders.FirstAsync();Console.WriteLine(context.ChangeTracker.DebugView.ShortView);

After a normal tracking query, the selected entity should appear as Unchanged. If you modify a scalar property and change detection runs, it becomes Modified. Chapter 11 goes deeply into this state machine; here the important point is that persistence decisions are inspectable rather than mystical.

5. What EF Core deliberately does not replace

A reliable EF application still needs relational design, database constraints, indexes, query-plan reasoning, transaction boundaries, concurrency strategy, backup/recovery, database security, deployment controls, and provider-specific operational knowledge. EF can create a migration that asks for an index; it does not determine from first principles whether that index is right for every workload. EF can begin a transaction; it does not make isolation anomalies disappear. EF can parameterize values; it does not make arbitrary dynamic identifiers safe.

This boundary is particularly important when other writers exist. A reporting script, ETL job, database administrator, another service, or legacy application may bypass EF entirely. Invariants that must hold for all writers generally belong in the database as constraints or other database-enforced mechanisms, with application validation providing earlier, friendlier feedback.

6. EF Core vs ADO.NET, Dapper, stored procedures, and handwritten SQL

These are not mutually exclusive religions. A production application can use EF Core for most transactional paths, a micro-ORM or ADO.NET for a carefully measured hot read, and stored procedures for a database-governed operation. The decision should follow semantics, observability, performance evidence, ownership, and deployment constraints.

7. A case where direct SQL is clearer: database-native administration

Suppose ServiceHub must record whether SQLite foreign-key enforcement is enabled for the current connection. That setting is SQLite-specific infrastructure state, not domain data. Hiding it behind an entity called DatabaseSetting would pretend the operation is ordinary relational CRUD. A direct provider command makes the boundary explicit.

await context.Database.OpenConnectionAsync();try{    await using var command = context.Database.GetDbConnection().CreateCommand();    command.CommandText = "PRAGMA foreign_keys;";    var enabled = Convert.ToInt32(await command.ExecuteScalarAsync()) == 1;    Console.WriteLine($"SQLite foreign_keys enabled: {enabled}");}finally{    await context.Database.CloseConnectionAsync();}

The SQL is intentionally provider-specific and contains no untrusted value. If dynamic values are required, use parameters where the database syntax permits. If dynamic table/column identifiers are required, parameters do not solve that problem; identifiers must come from trusted allow-lists or a safer query design.

8. Deliberately wrong approach: materialize first, filter later

A common “ORM makes the database irrelevant” mistake is to force enumeration before applying a filter. The following code executes a broad query, materializes every row into memory, and only then applies the C# predicate. On a tiny sample it appears correct; on production data it can become a latency and memory incident.

var all = await context.WorkOrders.ToListAsync();var urgent = all    .Where(w => w.Status == WorkOrderStatus.Open && w.Priority >= 3)    .ToList();

The repair is to keep the expression as IQueryable<WorkOrder> until the intended server-side operations are composed, then execute once:

var urgent = await context.WorkOrders    .Where(w => w.Status == WorkOrderStatus.Open && w.Priority >= 3)    .OrderByDescending(w => w.Priority)    .ToListAsync();

Verification requires more than checking the returned objects. Inspect ToQueryString() or EF command logs and confirm that the SQL contains the filter. For large-data diagnosis, also inspect the database execution plan and measured rows/latency. The ORM-level observation and database-level observation answer different questions.

9. Hands-on lab: prove the EF/database boundary

This lab uses .NET 10, EF Core 10.0.11, and Microsoft’s SQLite provider 10.0.11. SQLite is free, cross-platform, and requires no server installation, which makes it a good first provider. If the .NET 10 SDK is not installed yet, perform the installation verification in Lesson 3 and then return here.

mkdir ServiceHubEfCoursecd ServiceHubEfCoursedotnet new console -n ServiceHub.EfLab -f net10.0cd ServiceHub.EfLabdotnet add package Microsoft.EntityFrameworkCore.Sqlite --version 10.0.11

Create a small WorkOrder entity and ServiceHubContext. For this first boundary experiment it is acceptable to call EnsureDeletedAsync/EnsureCreatedAsync against a disposable file, because the goal is not schema evolution. Lesson 15 replaces that prototype path with migrations and explains why EnsureCreated is not a migration strategy.

using Microsoft.EntityFrameworkCore;var options = new DbContextOptionsBuilder<ServiceHubContext>()    .UseSqlite("Data Source=servicehub-boundary.db")    .LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information)    .Options;await using var db = new ServiceHubContext(options);await db.Database.EnsureDeletedAsync();await db.Database.EnsureCreatedAsync();db.WorkOrders.AddRange(    new WorkOrder { CustomerName = "Northwind Clinic", Priority = 4, Status = WorkOrderStatus.Open },    new WorkOrder { CustomerName = "Riverside Lab", Priority = 2, Status = WorkOrderStatus.Open },    new WorkOrder { CustomerName = "Metro Office", Priority = 5, Status = WorkOrderStatus.Closed });await db.SaveChangesAsync();var query = db.WorkOrders.Where(w => w.Status == WorkOrderStatus.Open && w.Priority >= 3);Console.WriteLine(query.ToQueryString());var result = await query.ToListAsync();Console.WriteLine(db.ChangeTracker.DebugView.ShortView);public sealed class ServiceHubContext(DbContextOptions<ServiceHubContext> options) : DbContext(options){    public DbSet<WorkOrder> WorkOrders => Set<WorkOrder>();}public sealed class WorkOrder{    public int Id { get; set; }    public string CustomerName { get; set; } = "";    public int Priority { get; set; }    public WorkOrderStatus Status { get; set; }}public enum WorkOrderStatus { Open, Closed }

Verification checklist

• dotnet run creates only the disposable servicehub-boundary.db file.
• Command logs show INSERT statements and a SELECT with a database-side filter.
• ToQueryString() resembles the executed query shape but is treated as diagnostic text, not a query plan.
• The tracker debug view shows queried WorkOrder instances as tracked/unchanged after the query.
• You can point to one responsibility owned by EF and one still owned by SQLite.

10. Production judgment and next bridge

Choose EF Core when its model, translation, tracking, and schema-evolution capabilities reduce application complexity without hiding a database behavior you must control. Choose a lower-level path when the database contract itself is the primary artifact, but preserve parameterization, transaction ownership, cancellation, observability, and tests. Never adopt a blanket “all data access must go through a generic repository” rule before understanding the actual query and unit-of-work boundaries.

The chapter baseline is EF Core 10.0.11 on .NET 10. Microsoft’s SQL Server and SQLite provider packages have matching 10.0.11 releases. Provider major versions normally align with EF major versions, and third-party providers have independent release/support cadences. Record the package/provider/database versions in reproducible evidence instead of relying on “EF Core 10” as if it were one immutable environment.

Lesson 2 makes that version contract explicit: LTS support, target frameworks, package families, SDK feature bands, stable-versus-preview versions, and what a safe upgrade check actually looks like.

Authoritative references

• EF Core overview — Microsoft EF Core concepts and documentation hub
• Database providers — provider plug-in model and version-compatibility warning
• DbContext lifetime and configuration — unit-of-work lifetime and configuration model
• Tracking vs. no-tracking queries — tracking and identity-resolution behavior
• Simple logging — development logging and sensitive-data warnings
• SQLite provider — Microsoft-maintained SQLite provider and supported SQLite baseline