(LLBLGen Pro V5.5, adapter. I guess this problem is not related to database type etc. I can provide those details if needed.)
With a standard schema where a Customer can have multiple Orders.
How do I retrieve all Customers of a region which has at least one Order with Status "Pending".
This was the existing code which retrieves all Customers including all Orders for a certain region with paging.
var qf = new QueryFactory();
var q = qf.Customer
.Where(CustomerFields.Region.Equal("Europe"))
.WithPath(CustomerEntity.PrefetchPathOrders
.WithOrdering(OrderFields.Nr.Ascending()))
.OrderBy(CustomerFields.ID.Descending())
.Page(pageNr, customersPerPage);
Tried adding this filter on PrefetchPathOrders
.WithFilter(OrderFields.Status.Equal("Pending"))
But this will only filter all orders and the result will include Customers which had no pending orders.
Do I have to use joins of some sort?
I'd like to think that I, instead of a filter, could add
.Where(OrderFields.Status.Equal("Pending"))
Or something similar.