When working with Entity Framework Core, it is very common to retrieve database records directly as entity objects. This makes application development easier because developers can work with familiar C# classes instead of writing SQL queries for every operation.
However, there is one habit that can affect application performance as the database grows: retrieving complete entities when the application only needs a few properties.
Consider a customer management application. It contains a Customers table with information such as customer name, email address, phone number, address, registration date, and other details.
Now imagine an API that displays a simple customer list containing only the customer's name and email address.
A developer might write the following query:
var customers = await dbContext.Customers
.ToListAsync();
The query works, but EF Core retrieves all mapped columns for every customer.
The application only needs two properties, but it receives the complete entity.
This may not create a noticeable difference when the table contains a few hundred records. However, when the application starts handling thousands or millions of records, retrieving unnecessary columns can increase database traffic, memory consumption, and the amount of work required to process the results.
This is where projection becomes useful.
Projection allows developers to select only the properties required by the application instead of retrieving complete database entities.
In EF Core, projection is commonly implemented using the LINQ Select() method.
In this article, we'll understand how projection works, why it can improve EF Core query performance, and how to use it in real-world ASP.NET Core applications. We'll also explore DTO projection, related entity projection, aggregation, pagination, common mistakes, and practical recommendations for production applications.
Understanding Projection in Entity Framework Core
Projection means selecting specific properties from a data source and creating a result containing only the information required by the application.
In EF Core, we generally use the Select() method to achieve this.
Let's take a simple example.
Suppose we have the following Customer entity:
public class Customer
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public string Email { get; set; } = string.Empty;
public string PhoneNumber { get; set; } = string.Empty;
public string Address { get; set; } = string.Empty;
public DateTime RegistrationDate { get; set; }
public bool IsActive { get; set; }
}
Now suppose an API only needs to return the customer's name and email address.
One approach is to retrieve the complete entity:
var customers = await dbContext.Customers
.ToListAsync();
After retrieving the records, the application can access the required properties.
foreach (var customer in customers)
{
Console.WriteLine(customer.Name);
Console.WriteLine(customer.Email);
}
The problem is that the application retrieves more information than it uses.
Instead, we can use projection:
var customers = await dbContext.Customers
.Select(c => new
{
c.Name,
c.Email
})
.ToListAsync();
Now EF Core only needs to retrieve the Name and Email columns.
The generated SQL might look similar to this:
SELECT
c.Name,
c.Email
FROM Customers AS c;
Compare this with the earlier query, which retrieved all mapped customer columns.
The difference is simple but important.
In the first query, the application retrieves complete customer entities. In the second query, it retrieves only the information required by the API.
This reduces unnecessary data transfer and avoids materializing complete Customer entities.
Projection is particularly useful for read-heavy operations such as dashboards, reporting screens, search results, and listing APIs.
Why Projection Improves EF Core Query Performance
Projection is not just a way to make LINQ queries shorter. It changes the shape of the data retrieved from the database.
When an application retrieves complete entities unnecessarily, it introduces additional work at different stages of query execution.
Let's understand where projection can help.
Retrieving Fewer Columns From the Database
Every additional column selected by a query contributes to the amount of data returned by the database.
Consider a customer table containing the following information:
Column | Example Data |
|---|
Id | 101 |
Name | John Smith |
Email | [email protected] |
PhoneNumber | +1-555-0100 |
Address | Complete customer address |
RegistrationDate | 2025-01-15 |
IsActive | True |
ProfileImage | Binary image data |
Suppose an API only displays the customer's name and email address.
Retrieving every column is unnecessary.
Using projection:
var customers = await dbContext.Customers
.Select(c => new
{
c.Name,
c.Email
})
.ToListAsync();
EF Core can generate a query that selects only those two columns.
This becomes especially useful when tables contain large text fields, binary data, or additional information that is not required by the current operation.
Reducing Memory Consumption
After retrieving database results, EF Core materializes entity instances when the query returns entities.
These objects consume application memory.
For tracking queries, EF Core also maintains information about tracked entities.
Consider an API that retrieves 50,000 customers.
If the application loads complete Customer entities, it creates objects containing all the mapped properties.
However, if the API only needs names and email addresses, creating complete entities is unnecessary.
A projection query can return smaller result objects instead.
var customers = await dbContext.Customers
.Select(c => new
{
c.Name,
c.Email
})
.ToListAsync();
This reduces the amount of information that needs to be materialized by the application.
The actual memory savings depend on the entity structure, selected columns, result size, and database provider.
Reducing Network Traffic
In many applications, the database server and application server communicate over a network.
Retrieving unnecessary columns means transferring information that the application does not use.
This can become noticeable when:
The database is hosted separately from the application.
Queries return large result sets.
Tables contain large text or binary columns.
Multiple requests execute similar queries concurrently.
Projection helps reduce the amount of data transferred between the database and application.
Avoiding Unnecessary Entity Materialization
When EF Core retrieves complete entities, it needs to construct the corresponding .NET objects.
If an application only needs a few scalar values, materializing complete entities introduces unnecessary work.
Projection allows EF Core to construct only the requested result shape.
For read-only operations, this is often a more appropriate approach than retrieving complete entity instances.
Use DTO Projection Instead of Returning Complete Entities
Anonymous objects are useful when learning projection, but production applications generally benefit from using Data Transfer Objects (DTOs).
A DTO defines the structure of data that an application wants to return.
It also separates the API response from the database entity.
Let's continue with the customer example.
Create a DTO:
public class CustomerDto
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public string Email { get; set; } = string.Empty;
}
Now update the query:
var customers = await dbContext.Customers
.Select(c => new CustomerDto
{
Id = c.Id,
Name = c.Name,
Email = c.Email
})
.ToListAsync();
This query returns only the properties defined in CustomerDto.
The API does not need to expose the customer's phone number, address, registration date, or other database fields.
There are several reasons why this approach is useful.
First, it gives developers control over the API response.
Second, database entity changes do not automatically change the API contract.
Third, it avoids retrieving unnecessary columns.
Finally, DTOs make it easier to maintain different response structures for different application requirements.
For example, a customer listing API might need only three properties, while a customer details API might require additional information.
Instead of using one entity for both operations, we can define separate DTOs.
public class CustomerSummaryDto
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public string Email { get; set; } = string.Empty;
}
public class CustomerDetailsDto
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public string Email { get; set; } = string.Empty;
public string PhoneNumber { get; set; } = string.Empty;
public string Address { get; set; } = string.Empty;
public DateTime RegistrationDate { get; set; }
}
Now each API can return the information it actually needs.
This is particularly useful in applications with multiple clients, such as web applications, mobile applications, and administrative dashboards.
Projecting Data From Related Entities
One of the most useful features of projection is that it can retrieve information from related entities without loading the complete entity graph.
This is an area where developers sometimes use Include() unnecessarily.
Let's take an example.
Suppose an e-commerce application contains the following entities:
A customer can have multiple orders, and each order can contain multiple items.
Here are simplified entity classes:
public class Customer
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public string Email { get; set; } = string.Empty;
public ICollection<Order> Orders { get; set; }
= new List<Order>();
}
public class Order
{
public int Id { get; set; }
public DateTime OrderDate { get; set; }
public decimal TotalAmount { get; set; }
public int CustomerId { get; set; }
public Customer Customer { get; set; } = null!;
public ICollection<OrderItem> Items { get; set; }
= new List<OrderItem>();
}
public class OrderItem
{
public int Id { get; set; }
public string ProductName { get; set; } = string.Empty;
public int Quantity { get; set; }
public int OrderId { get; set; }
public Order Order { get; set; } = null!;
}
Now imagine a customer listing API that displays:
Customer name
Email address
Total number of orders
A common approach might be:
var customers = await dbContext.Customers
.Include(c => c.Orders)
.ToListAsync();
After retrieving the customers and their orders, the application can calculate the order count.
However, the application does not actually need the complete order collection.
We can directly project the order count:
var customers = await dbContext.Customers
.Select(c => new CustomerSummaryDto
{
Id = c.Id,
Name = c.Name,
Email = c.Email,
OrderCount = c.Orders.Count()
})
.ToListAsync();
Update the DTO:
public class CustomerSummaryDto
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public string Email { get; set; } = string.Empty;
public int OrderCount { get; set; }
}
EF Core translates the navigation property expression into SQL.
Conceptually, the SQL might look like this:
SELECT
c.Id,
c.Name,
c.Email,
(
SELECT COUNT(*)
FROM Orders o
WHERE o.CustomerId = c.Id
) AS OrderCount
FROM Customers c;
The database calculates the order count instead of requiring the application to retrieve every order.
This is a good example of how projection can simplify application code while reducing unnecessary data retrieval.
Use Projection for Aggregation Operations
Projection becomes even more useful when an application needs summary information rather than complete records.
Many applications contain dashboards and reporting screens that display totals, counts, averages, and other calculated values.
For example:
Instead of retrieving all the underlying records and calculating these values in C#, developers can use LINQ aggregation methods.
Calculate Total Orders
Suppose an application needs to display the number of orders placed by each customer.
var customerOrders = await dbContext.Customers
.Select(c => new
{
c.Id,
c.Name,
TotalOrders = c.Orders.Count()
})
.ToListAsync();
The database calculates the count for each customer.
Calculate Total Sales
Suppose an administrative dashboard displays the total sales amount for each customer.
var customerSales = await dbContext.Customers
.Select(c => new
{
c.Id,
c.Name,
TotalSales = c.Orders.Sum(o => o.TotalAmount)
})
.ToListAsync();
This retrieves customer information along with the calculated sales amount.
If the relationship allows customers to have no orders, be careful with aggregate expressions and the generated SQL. EF Core generally translates supported aggregate operations appropriately, but the actual SQL and null behavior should be verified for the database provider.
Calculate Average Order Value
An application might also need to display the average order value.
var customerStatistics = await dbContext.Customers
.Select(c => new
{
c.Id,
c.Name,
AverageOrderValue = c.Orders
.Select(o => (decimal?)o.TotalAmount)
.Average()
})
.ToListAsync();
Here, the nullable decimal conversion allows the average to be null when a customer has no orders.
This is useful when the application needs to distinguish between customers who have no orders and customers whose average order value is zero.
These examples demonstrate an important principle: if the database can calculate a summary efficiently, there is often no reason to retrieve all the underlying records just to perform the calculation in application memory.
Use Nested Projection for Complex API Responses
Real-world APIs rarely return information from a single table.
A customer details API might need customer information, recent orders, and selected order item details.
Projection supports nested result structures, allowing developers to build response objects directly from LINQ queries.
Consider the following requirement.
A customer details API should return:
We can implement this using nested projection.
First, define the DTOs:
public class CustomerDetailsDto
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public string Email { get; set; } = string.Empty;
public List<OrderDetailsDto> Orders { get; set; }
= new();
}
public class OrderDetailsDto
{
public int Id { get; set; }
public DateTime OrderDate { get; set; }
public decimal TotalAmount { get; set; }
public List<OrderItemDto> Items { get; set; }
= new();
}
public class OrderItemDto
{
public string ProductName { get; set; } = string.Empty;
public int Quantity { get; set; }
}
Now write the query:
var customer = await dbContext.Customers
.Where(c => c.Id == customerId)
.Select(c => new CustomerDetailsDto
{
Id = c.Id,
Name = c.Name,
Email = c.Email,
Orders = c.Orders
.OrderByDescending(o => o.OrderDate)
.Take(5)
.Select(o => new OrderDetailsDto
{
Id = o.Id,
OrderDate = o.OrderDate,
TotalAmount = o.TotalAmount,
Items = o.Items
.Select(i => new OrderItemDto
{
ProductName = i.ProductName,
Quantity = i.Quantity
})
.ToList()
})
.ToList()
})
.FirstOrDefaultAsync();
This query creates the required response structure directly.
Notice that we did not need to write multiple Include() statements to retrieve the complete entity graph.
We selected only the properties required by the response.
This approach is particularly useful for APIs that return nested JSON responses.
However, complex projections should always be tested against the actual database provider. EF Core must be able to translate the LINQ expressions into SQL. Some complex expressions or custom methods may not translate as expected.
Combine Projection With AsNoTracking()
Another common question is whether projection should always be combined with AsNoTracking().
The answer depends on what the query returns.
By default, EF Core tracks entity instances returned from queries.
For read-only operations, developers often use:
var customers = await dbContext.Customers
.AsNoTracking()
.ToListAsync();
This prevents normal change tracking for the returned entities.
However, when a projection contains only scalar values or DTO objects, there are generally no entity instances for EF Core to track.
For example:
var customers = await dbContext.Customers
.Select(c => new CustomerDto
{
Id = c.Id,
Name = c.Name,
Email = c.Email
})
.ToListAsync();
The result contains DTO instances rather than Customer entities.
In this case, adding AsNoTracking() is generally unnecessary for tracking purposes.
But consider this query:
var customers = await dbContext.Customers
.Select(c => new
{
Customer = c,
OrderCount = c.Orders.Count()
})
.ToListAsync();
Here, the projection contains the actual Customer entity.
EF Core tracks that entity by default.
If the query is read-only, you can use:
var customers = await dbContext.Customers
.AsNoTracking()
.Select(c => new
{
Customer = c,
OrderCount = c.Orders.Count()
})
.ToListAsync();
The important thing is to understand what your projection returns.
AsNoTracking() is useful when entity instances are returned and do not need change tracking. It is not a replacement for selecting only the required properties.
Combine Projection With Filtering and Pagination
Projection can reduce the amount of data returned by a query, but it does not automatically limit the number of records.
This is another area where developers need to be careful.
Suppose an application contains one million customer records.
The following query selects only three properties:
var customers = await dbContext.Customers
.Select(c => new CustomerDto
{
Id = c.Id,
Name = c.Name,
Email = c.Email
})
.ToListAsync();
Although the query retrieves fewer columns, it still returns every customer.
That can consume considerable memory and network resources.
For large datasets, combine projection with filtering and pagination.
For example:
var pageNumber = 1;
var pageSize = 20;
var customers = await dbContext.Customers
.Where(c => c.IsActive)
.OrderBy(c => c.Id)
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.Select(c => new CustomerDto
{
Id = c.Id,
Name = c.Name,
Email = c.Email
})
.ToListAsync();
This query:
Filters active customers.
Orders the results by customer ID.
Skips records from previous pages.
Retrieves only the requested page.
Selects only the required properties.
The ordering is important because pagination should use a deterministic ordering. When ordering by a non-unique column, add a unique key as a secondary ordering column.
For applications that primarily navigate forward or backward through large datasets, keyset pagination can be more efficient than repeatedly using large Skip() values.
For example:
var customers = await dbContext.Customers
.Where(c => c.IsActive && c.Id > lastCustomerId)
.OrderBy(c => c.Id)
.Take(20)
.Select(c => new CustomerDto
{
Id = c.Id,
Name = c.Name,
Email = c.Email
})
.ToListAsync();
This approach retrieves the next set of customers after the last customer ID from the previous page.
The choice between offset pagination and keyset pagination depends on the application's navigation requirements.
Use Projection With AutoMapper
In larger applications, manually writing projection expressions for every DTO can become repetitive.
Some development teams use AutoMapper to simplify object mapping.
AutoMapper provides a ProjectTo() method that can build projection expressions for supported mappings.
For example, consider the following mapping:
public class CustomerProfile : Profile
{
public CustomerProfile()
{
CreateMap<Customer, CustomerDto>();
}
}
You can then use:
var customers = await dbContext.Customers
.ProjectTo<CustomerDto>(mapper.ConfigurationProvider)
.ToListAsync();
Unlike retrieving complete entities and mapping them afterward, ProjectTo() builds a projection that EF Core can translate into a database query when the mapping expressions are translatable.
This means the database can retrieve only the columns required by CustomerDto.
However, there are a few things to remember.
First, projection must happen before materializing the query. Calling AutoMapper's ordinary Map() method after ToListAsync() means the complete entities have already been retrieved.
Second, not every custom mapping expression can be translated into SQL.
Third, ProjectTo() should generally be applied after filtering, ordering, and pagination operations.
For example:
var customers = await dbContext.Customers
.Where(c => c.IsActive)
.OrderBy(c => c.Name)
.Take(20)
.ProjectTo<CustomerDto>(mapper.ConfigurationProvider)
.ToListAsync();
For straightforward queries, manual Select() projection is often easier to understand and debug.
For larger applications with many DTO mappings, AutoMapper can reduce repetitive mapping code.
The important thing is to inspect the generated SQL rather than assuming that a mapping library automatically produces an efficient query.
Understand Client-Side Evaluation When Using Projection
Projection is powerful, but developers should understand one important detail: not every C# expression can be translated into SQL.
EF Core attempts to execute as much of a query as possible on the database server.
However, some custom .NET methods cannot be translated into SQL.
Consider this example:
public static string FormatCustomerName(string name)
{
return name.ToUpperInvariant();
}
Now suppose we use it in a projection:
var customers = await dbContext.Customers
.Select(c => new
{
c.Id,
DisplayName = FormatCustomerName(c.Name)
})
.ToListAsync();
Depending on the EF Core version and provider, this kind of custom method may not translate into SQL.
EF Core supports client evaluation in the top-level projection when parts of the projection cannot be translated. It retrieves the required data and evaluates the remaining expression in application memory.
This does not mean every expression inside Select() executes in the database.
If an untranslatable expression appears in a filtering or ordering operation, EF Core generally throws a runtime exception rather than silently retrieving all records for client-side evaluation.
For example:
var customers = await dbContext.Customers
.Where(c => FormatCustomerName(c.Name) == "JOHN SMITH")
.ToListAsync();
If the method cannot be translated, EF Core will generally throw an exception.
A better approach is to use expressions supported by the database provider.
For example:
var customers = await dbContext.Customers
.Where(c => c.Name.ToUpper() == "JOHN SMITH")
.Select(c => new CustomerDto
{
Id = c.Id,
Name = c.Name,
Email = c.Email
})
.ToListAsync();
The exact translation depends on the provider and database collation.
Also remember that applying functions to database columns can affect index usage. For case-insensitive searches, consider the database collation or an appropriate indexed normalized column rather than automatically applying string conversion to every query.
The main lesson is to understand which parts of a projection execute on the database server and which parts execute in application memory.
Common Mistakes Developers Should Avoid
Projection can improve query efficiency, but it is important to use it correctly.
Here are some common mistakes worth avoiding.
Retrieving Complete Entities and Mapping Them Later
Consider:
var customers = await dbContext.Customers
.ToListAsync();
var result = customers.Select(c => new CustomerDto
{
Id = c.Id,
Name = c.Name,
Email = c.Email
}).ToList();
Although the final result contains only three properties, the application has already retrieved and materialized all customer entities.
Instead, project directly in the database query:
var result = await dbContext.Customers
.Select(c => new CustomerDto
{
Id = c.Id,
Name = c.Name,
Email = c.Email
})
.ToListAsync();
The difference is where the projection happens.
In the first example, mapping happens after retrieving the database records. In the second, EF Core can translate the projection into SQL.
Calling ToList() Too Early
Consider:
var customers = await dbContext.Customers
.ToListAsync();
var activeCustomers = customers
.Where(c => c.IsActive)
.Select(c => new CustomerDto
{
Id = c.Id,
Name = c.Name,
Email = c.Email
})
.ToList();
The filtering and projection happen in application memory because the query has already been executed.
Instead, keep the operations inside the IQueryable pipeline:
var activeCustomers = await dbContext.Customers
.Where(c => c.IsActive)
.Select(c => new CustomerDto
{
Id = c.Id,
Name = c.Name,
Email = c.Email
})
.ToListAsync();
This allows EF Core to translate the filtering and projection into SQL.
Assuming Projection Automatically Makes Every Query Fast
Projection reduces unnecessary data retrieval, but it does not solve every database performance problem.
A query can still be slow because of:
Projection should be treated as one part of query optimization, not a replacement for proper database design.
Selecting Too Many Properties
A DTO containing almost every property from the database entity provides little benefit over retrieving the complete entity.
Think about what the endpoint actually needs.
For a listing API, keep the response focused. For a details API, include the additional information required by that operation.
Ignoring Generated SQL
A LINQ expression may look simple, but the generated SQL can still be complex.
Always inspect the generated query when optimizing important endpoints.
Inspect the SQL Generated by EF Core
One of the best ways to understand projection is to compare the SQL generated before and after optimization.
EF Core provides the ToQueryString() method for inspecting SQL without executing the query.
For example, consider a complete entity query:
var query = dbContext.Customers;
Console.WriteLine(query.ToQueryString());
Now compare it with a projection:
var query = dbContext.Customers
.Select(c => new CustomerDto
{
Id = c.Id,
Name = c.Name,
Email = c.Email
});
Console.WriteLine(query.ToQueryString());
You can compare the selected columns and understand what EF Core sends to the database.
For additional diagnostics, configure EF Core logging:
builder.Services.AddDbContext<AppDbContext>(options =>
{
options.UseSqlServer(
builder.Configuration.GetConnectionString("DefaultConnection"));
options.LogTo(
Console.WriteLine,
LogLevel.Information);
});
This allows developers to observe database commands during development.
For production applications, use structured logging and appropriate filtering. Avoid exposing sensitive parameter values in application logs.
When investigating performance, compare:
Query execution time.
Number of rows returned.
Number of columns selected.
Amount of data transferred.
Application memory allocations.
Database execution plans.
Index usage.
Do not assume that projection has improved performance simply because the SQL query is shorter.
Measure the actual difference using realistic database records.
Practical Recommendations for Production Applications
When using projection in real-world applications, a few practices can make a significant difference.
Use DTOs for API responses. They provide a clear separation between database entities and the data exposed by the API.
Keep projections focused. Select only the properties required by the current operation.
Apply filtering before materializing results. Keep Where(), OrderBy(), Skip(), and Take() inside the IQueryable pipeline.
Avoid retrieving large result sets without pagination. Projection reduces the size of individual records, but it does not automatically reduce the number of records returned.
Use aggregation methods for summary information. Count(), Sum(), Average(), Min(), and Max() can often be translated into database operations.
Be careful with custom methods inside projections. Verify whether EF Core can translate them or whether they will execute on the client.
Use AutoMapper ProjectTo() when it genuinely simplifies a large mapping layer, but inspect the generated SQL.
Avoid returning database entities directly from public API endpoints. DTOs give you control over the response structure and help prevent accidental exposure of database fields.
Finally, measure performance using production-like data. A query that works well with 100 records may behave very differently with 100,000 records.
Conclusion
Entity Framework Core provides powerful querying capabilities, but retrieving complete entities is not always the best approach.
When an application only needs a few properties, loading the entire entity introduces unnecessary data retrieval and object materialization.
Projection helps solve this problem by allowing developers to select exactly the information required by the application.
Using the Select() method, we can build DTOs, retrieve selected properties from related entities, calculate aggregates, and create nested API responses without loading complete entity graphs.
For read-heavy operations such as dashboards, reporting, search results, and listing APIs, projection is often a better starting point than retrieving complete entities.
However, projection alone does not guarantee that every query will be fast. Filtering, pagination, indexes, query translation, and database execution plans still matter.
The best approach is to design each query around the data the application actually needs, inspect the generated SQL, and measure performance before and after optimization.
A good EF Core query should not retrieve more data simply because it is available. It should retrieve only what the application needs to do its job.
Join the conversation! Your thoughts help the community grow.