EF Core filtering + paging with child entities

Question

User-1471263033 posted

I want to perform 1 query (to SQL Server) 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?

Answer 1

User-335504541 posted

Hi dennieku,

Do you want to get distinct customer in your result?

Please try to use :

var customers = await qry.GroupBy(x=>x.Customer).Select(s=>s.First()).Skip(0).Take(10).ToListAsync();

Best Regards,

Billy

Answer 2

User-1471263033 posted

Hi Billy,

Sorry for my late reply.. I did not get a notification for your answer .
When I use your solution, I do get 10 distinct customers, but only 1 order per customer. Some customers have multiple orders with OrderState == 0. I need all orders in the result.

Any other ideas?

Regards,
Danny

Answer 3

User-335504541 posted

Hi dennieku,

What kind of data do you want to get?

Do you want to get a data with a Customer with the orders?

Could you show me your model?

Best Regards,

Billy

Answer 4

User-1471263033 posted

Hi Billy,

The data that I want is all data in the attached data model image, so all customers with these kind of orders. Related to these orders I want the details which have a relation with products.

Regards,
Danny

Answer 5

User-335504541 posted

Hi dennieku,

Please try to use the following code:

                      var qry = from c in db.Customers
                      select new { Customer = c, Order = (from o in db.Orders
                                                          join d in db.OrderDetails on o.Id equals d.OrderId
                                                          join p in db.Products on d.ProductId equals p.Id
                                                          where o.CustomerId == c.Id && o.OrderState == 0
                                                          select new {
                                                              order = o,
                                                              orderdetails = d,
                                                              product = p,
                                                          })
                      };
                      qry = qry.Where(x=>x.Order.Count()>0);

Best Regards,

Billy