EF Core Architecture
By the end of this lesson
Describe how EF Core turns a LINQ query into SQL and results into objects.
You have seen that EF Core takes a query written in C# and returns objects. This lesson is about the machinery in between, because you cannot reason about performance or fix a wrong result without knowing which stage produced it.
Four things happen, in order. Your query is captured as a data structure. A database provider translates that structure into SQL. The database runs the SQL and sends rows back. EF Core turns those rows into objects and, by default, starts watching them for changes.
The same four stages, in more detail:
Your LINQ is recorded, not run
A DbSet such as context.Employees is an IQueryable. Calling Where or OrderBy on it does not look at any data. It builds an expression tree: a tree of objects describing the comparison, the property, and the value you asked about. Nothing has touched the database yet, which is why you can keep adding clauses across several lines of code.
A provider translates the tree into SQL
The database provider is a NuGet package for one database engine — SQL Server, PostgreSQL, SQLite, and others. It walks the expression tree and writes a statement in that engine's dialect. This is also where a query fails with a translation error: if the tree contains a C# method the provider has no SQL equivalent for, it cannot produce a statement.
The database executes one statement
EF Core opens a connection, sends the SQL with its parameters, and waits. Everything before this point was work on your own machine, measured in microseconds. This step crosses a process boundary and usually a network, and it is where the time goes.
Rows are materialised into objects
EF Core reads the result set and creates an instance per row, matching columns to properties by the mapping in its model. Each instance is then recorded in the change tracker so that later edits can be turned into UPDATE statements. Materialisation is the stage that makes a query returning fifty thousand rows expensive in memory as well as in time.
IQueryable<Employee> query = context.Employees
.Where(e => e.Department.Name == "Engineering")
.OrderBy(e => e.Name);
// Still nothing has run. The database is contacted here:
List<Employee> engineers = await query.ToListAsync();- The first statement produces an IQueryable. That type is the signal that you are composing a query rather than filtering a list in memory.
- e.Department.Name reaches across a relationship. EF Core knows Employee and Department are related, so it can express this as a join rather than two queries.
- ToListAsync is the point of execution. Count, First, Single, Any, and a foreach loop do the same thing: they need real data, so they force the query to run.
- Splitting composition from execution is deliberate. A method can return an IQueryable and let its caller add paging, and the database still receives one statement.
SELECT [e].[Id], [e].[Name], [e].[DepartmentId], [e].[AnnualSalary]
FROM [Employees] AS [e]
INNER JOIN [Departments] AS [d] ON [e].[DepartmentId] = [d].[Id]
WHERE [d].[Name] = N'Engineering'
ORDER BY [e].[Name]- One statement for three clauses. The Where became a WHERE, the OrderBy became an ORDER BY, and the property access across the relationship became an INNER JOIN.
- The SELECT list names every mapped column rather than using an asterisk. EF Core needs known positions to materialise reliably.
- The literal appears inline here because it was written into the code. Had it come from a variable, EF Core would have sent a parameter instead — which both protects against SQL injection and lets the database reuse its execution plan.
The parts of EF Core you will hear named, and what each one is responsible for:
- The model
- EF Core's internal description of your entities, their properties, their keys and their relationships. It is built once, the first time a context is used, and cached for the life of the application. Everything else consults it.
- The database provider
- The package that knows one database engine: its SQL dialect, its type names, and its capabilities. Swapping providers is how the same model targets a different database, and it is never entirely free, because dialects differ in what they can translate.
- The query pipeline
- The stage that turns an expression tree into SQL plus a plan for reading the results back. It caches compiled queries, so the second time you run the same shape of query the translation work is skipped.
- The change tracker
- The record of every entity the context has loaded or been given, along with its original values. SaveChanges compares current values against those originals to decide what to write. Covered in the next lesson.
Summary
- LINQ over a DbSet builds an expression tree; no data moves until something forces execution
- A database provider translates that tree into SQL for one specific database engine
- The database round trip is the expensive stage, so the number of queries matters more than the shape of one
- Materialisation turns rows into objects and, by default, registers them with the change tracker
- Composing a query across several lines still produces a single statement
Practice
Attempt each one before opening the solution. Getting it wrong first is how the idea sticks.
Think about it
Where does the work actually happen?
You write a method that returns context.Employees.Where(e => e.AnnualSalary > 50000) without calling ToListAsync. A caller then adds .Take(10) and awaits it.
How many statements reach the database, and what does the database do with the salary filter and the limit?
Show solution
One statement. Your Where and the caller's Take were both added to the same expression tree, so the provider translated them together into a single SELECT with a WHERE clause and a row limit.
This is the benefit of deferred execution: the filtering and the limiting happen inside the database, which reads fewer pages and sends fewer rows over the wire.
Change the method's return type to List<Employee> and the picture inverts. The query runs before the caller sees it, every matching employee is materialised into memory, and Take then discards all but ten of them. The C# reads almost identically. The cost does not.
Challenge
Predict the SQL, then check it
Write a query that returns the name of every employee in the Finance department, sorted by salary, highest first.
Before running it, write down the SQL you expect on paper: which columns will be in the SELECT list, and will there be a join?
Show solution
If you projected to a name with Select, the SELECT list should contain one column, not the whole Employees table. Asking for less is the single easiest performance win in EF Core, and it is available before you know anything else about tuning.
There will be a join if you filtered on e.Department.Name, and no join if you filtered on a foreign key value you already had, such as e.DepartmentId == financeId. Both are correct. The second does less work because the answer was already in the Employees table.
The reason for writing the prediction down first is that it turns a guess into a testable claim. The next lesson but one shows how to switch on logging so you can compare your prediction against what was sent.
Knowledge check
Nothing is recorded and there is no score. The explanation appears either way.
Saved in this browser only.