locked
EF Core filtering + paging with child entities RRS feed

  • 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:
    Data diagram

    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?





    Thursday, October 12, 2017 8:10 PM

All replies

  • 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

    Friday, October 13, 2017 5:52 AM
  • User-1471263033 posted

    Hi Billy,

    Sorry for my late reply.. I did not get a notification for your answer Frown.
    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

    Wednesday, October 18, 2017 7:08 AM
  • 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

    Wednesday, October 18, 2017 10:13 AM
  • 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

    Wednesday, October 18, 2017 7:30 PM
  • 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

    Thursday, October 19, 2017 2:55 AM