Question posted in
The official documentation can be found here.

Asp.net – EF Core filtered Include not working with Select transformation

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

Tags:

2

Answers


  1. 0

    In your select statement, you are querying the OrderItems without any filtering. You can remove the include and put the where clause within the select statement, like this:

    var ordersDtoAbove100 = context.Orders
    //.Include(o => o.OrderItems.Where(m => m.Price > 100)) - do this in the select statement
    .Select(m => new OrderDto()
    {
        CustomerName = m.CustomerName,
        Id = m.Id,
        OrderDate = m.OrderDate,
        Items = m.OrderItems
            .Where(i => i.Price > 100)  // Put your where clause here
            .Select(item => new OrderItemDto()
            {
                Price = item.Price,
                ProductName = item.ProductName,
                Quantity = item.Quantity,
            })
    })
    .AsNoTracking()
    .ToList();

    https://dotnetfiddle.net/r4ZcrY

    Login or Signup to reply.
  2. 0

    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,
    })
})
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search