EF Core filtered Include not working with Select transformation

Question

I'm facing a problem when i try to do an Ef Core request with a filtered include and a select

The request is well filtered without à select but when i had a select for transforming my entities to my DTO the request is no longer filtered

Example :

This request return well all OrderItems with item who have a price above 100

var ordersAbove100 = context.Orders
.Include(o => o.OrderItems.Where(m => m.Price > 100))
.AsNoTracking()
.ToList();

But when i add a select for transforming my entities to my DTO the request is no longer filtered and all my orders are returned

var ordersDtoAbove100 = context.Orders
.Include(o => o.OrderItems.Where(m => m.Price > 100))
.Select(m => new OrderDto()
{
    CustomerName = m.CustomerName,
    Id = m.Id,
    OrderDate = m.OrderDate,
    Items = m.OrderItems.Select(item => new OrderItemDto()
    {
        Price = item.Price,
        ProductName = item.ProductName,
        Quantity = item.Quantity,
    })
})
.AsNoTracking()
.ToList();

Complete exemple : https://dotnetfiddle.net/tBgLP4

Why .Select change the behavior of my request ? And What's the correct way to achieve this ?

I'm in .NET9

Answer 1

Include is not meant for filtration! It's just to load some related data with the same request.

Select on the other hand, is the Projection of what you need exactly to return, if you use Include with Select that has no meaning because we already say what we exactly want in Select Statement

Here what you want.

var ordersDtoAbove100 = context.Orders  
.Select(m => new OrderDto()
{
    CustomerName = m.CustomerName,
    Id = m.Id,
    OrderDate = m.OrderDate,
    Items = m.OrderItems.Where(m => m.Price > 100)
    .Select(item => new OrderItemDto()
    {
        Price = item.Price,
        ProductName = item.ProductName,
        Quantity = item.Quantity,
    })
})