EF Core filtering + paging with child entities

Question

I want to perform 1 query using EF Core where I have to filter on a child entity and also apply paging. See my (sample) data model below: I want to retrieve all data (customers, orders order details and products). I have to apply a filter on Order.OrderState and I only want the 1st 10 records (customers)

This is the LINQ query I tried:

var customers = await _ctx.Customer
            .Include(c => c.Order.Where(o => o.OrderState == 0))
            .ThenInclude(o => o.OrderDetail)
            .ThenInclude(d => d.Product)
            .Skip(0).Take(10)
            .ToListAsync();

When executing this query I get the following error: InvalidOperationException: The property expression 'c => {from Order o in c.Order where ([o].OrderState == 0) select [o]}' is not valid. The expression should represent a property access: 't => t.MyProperty'. For more information on including related data

So I tried another query:

var qry = from c in _ctx.Customer
                  join o in _ctx.Order on c.Id equals o.CustomerId
                  join d in _ctx.OrderDetail on o.Id equals d.OrderId
                  join p in _ctx.Product on d.ProductId equals p.Id
                  where o.OrderState == 0
                  select new { Customer = c, Order = o, OrderDetail = d, Product = p };
        var customers = await qry.Skip(0).Take(10).ToListAsync();

Now the query does not produce an error, but the result is not what I want. Because of the 1-n relationships this query returns customers multiple times in the result, so I do not get the 1st 10 customers.

Does anybody have a better query to get the results that I want?

user_2353828 · Answer

I think you can use ".Distinct()" while building the query. Hope this will solve the problem.

    var qry = (from c in _ctx.Customer
                      join o in _ctx.Order on c.Id equals o.CustomerId
                      join d in _ctx.OrderDetail on o.Id equals d.OrderId
                      join p in _ctx.Product on d.ProductId equals p.Id
                      where o.OrderState == 0
                     select new { Customer = c, Order = o, OrderDetail = d, 
                      Product = p }).Distinct();

 var customers = await qry.Skip(0).Take(10).ToListAsync();

EF Core filtering + paging with child entities

1 Answers