Query Result Mapping

Map complex join results to custom view models using AutoMapper or manual projection.

In this lesson, you will learn how to bridge the gap between complex database joins and clean, application-specific data structures using manual projection and AutoMapper.

The Challenge of Domain Models

When you join multiple tables (like Orders, Customers, and Products), your database result set rarely maps perfectly to a single entity. Using your domain entities directly in your UI often results in "over-fetching" data or creating circular reference issues. Instead, we map these results into a lightweight ViewModel or DTO (Data Transfer Object) that contains exactly what the user needs.

Manual Projection

The most efficient way to map query results in Entity Framework Core is through LINQ projection. By using .Select(), you instruct EF Core to generate SQL that only retrieves the specific columns required for your view model, which is much faster than loading full entities.

var orderSummary = await _context.Orders
    .Where(o => o.Status == "Shipped")
    .Select(o => new OrderViewModel 
    {
        OrderId = o.Id,
        CustomerName = o.Customer.FullName,
        TotalAmount = o.OrderItems.Sum(oi => oi.Price * oi.Quantity)
    })
    .ToListAsync();

Automating with AutoMapper

While manual projection is performant, it becomes repetitive as your application grows. AutoMapper simplifies this by providing the .ProjectTo<T> extension method. It integrates with EF Core’s IQueryable provider to translate your mapping configuration into a valid SQL SELECT statement automatically.

To use it, you define a profile:

CreateMap<Order, OrderViewModel>()
    .ForMember(dest => dest.CustomerName, opt => opt.MapFrom(src => src.Customer.FullName));

Then, execute the query:

var results = await _context.Orders
    .ProjectTo<OrderViewModel>(_mapper.ConfigurationProvider)
    .ToListAsync();

Whether you choose manual projection for fine-grained control or AutoMapper for cleaner code, the goal remains the same: decouple your database schema from your presentation layer. Always perform the mapping inside the LINQ query before calling ToListAsync() to ensure the work happens on the database server rather than pulling unnecessary data into application memory.

Key Takeaway: Map complex join results into specialized DTOs using projection to improve both performance and maintainability.