Hi

I've come accross a problem that I have a hard time wrapping my head around, and I probably just need a simple explanation, but here goes:

For simplicity, I have the (relevant) entities PurchaseOrder (PO) and PurchaseOrderLine (POL). Classic parent-child relation. For each PO we have 1 to 10 POL's. Each POL have a number of attributes, the ones being relevant here are IsReadyForInvoice and IsInvoiced. So, basically we track if each POL can be invoiced to a customer, and if it has been invoiced.

Now, to select and display POs in a Telerik Grid, I would do something like this:

var purchaseOrders = new EntityCollection<PurchaseOrderEntity>();

            using (var adapter = new DataAccessAdapter(SessionHelper.NhldbConnectionString))
            {
                var qf = new QueryFactory();
                var q = qf.PurchaseOrder
                    .From(QueryTarget
                        .LeftJoin(PurchaseOrderEntity.Relations.CustomerEntityUsingCustomerId)
                        .LeftJoin(PurchaseOrderEntity.Relations.CustomerProjectEntityUsingCustomerProjectId)
                        .LeftJoin(PurchaseOrderEntity.Relations.EmployeeEntityUsingEmployeeId)
                        .LeftJoin(PurchaseOrderEntity.Relations.PurchaseOrderLineEntityUsingPurchaseOrderId)
                        .LeftJoin(PurchaseOrderEntity.Relations.SupplierEntityUsingSupplierId))
                    .WithPath(PurchaseOrderEntity.PrefetchPathCustomer)
                    .WithPath(PurchaseOrderEntity.PrefetchPathCustomerProject)
                    .WithPath(PurchaseOrderEntity.PrefetchPathEmployee)
                    .WithPath(PurchaseOrderEntity.PrefetchPathPurchaseOrderLines)
                    .WithPath(PurchaseOrderEntity.PrefetchPathSupplier)
                    ;

                switch (uiStatusFilter.SelectedValue)
                {
                    case "show_active":
                        // here we have a challenge...
                        break;
                    case "show_invoiceMissing":
                        q = q.Where(PurchaseOrderFields.Status.Equal(PurchaseOrderStatus.Open));
                        break;

                }
        adapter.FetchQuery(q, purchaseOrders);
            }

    uiPurchaseOrders.DataSource = purchaseOrders;

This fetches all POs along with a number of related entities. Somewhat inefficient, but it works ok (for now, I might add). The problem lies within the filter-part at the bottom, as we have some specific workflows where an employee might need to see the POs that fulfill some "crazy" filter criteria. Its easy enough to just filter on PO-fields, or POL-fields, but if I wanted this:

All POs where the number of child POLs that are "IsReadyForInvoice" is larger than the number of child POLs that are actually invoiced ("IsInvoiced").

Hence, all POs where something is ready to be invoiced. Maybe not the best of examples, but you get the idea.

The SQL that one could write would be something like

SELECT po.*
,(
    SELECT COUNT(*) 
    FROM purchaseorderline 
    WHERE purchaseorderline.`PurchaseOrderId` = po.id 
    AND purchaseorderline.`IsReadyForInvoice` = TRUE) AS polIsReady
,(
    SELECT COUNT(*) 
    FROM purchaseorderline 
    WHERE purchaseorderline.`PurchaseOrderId` = po.id 
    AND purchaseorderline.`IsInvoiced` = TRUE) AS polIsInvoiced
,(
    SELECT COUNT(*) 
    FROM purchaseorderline 
    WHERE purchaseorderline.`PurchaseOrderId` = po.id ) AS polTotal
FROM purchaseorder po
HAVING polIsReady > polIsInvoiced

From what I can find in the documentation, the last example on this page (https://www.llblgen.com/Documentation/5.7/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_expressionsaggregates.htm) selects something like it, but with a DynamicQuery, which I cant use when binding a dataset to the grid. And I cant figure out how to otherwise incorporate the "counting of certain child elements and then filtering on those count values" in my EntityQuery above.

What I've been thinking about would be to create a DynamicQuery before my EntityQuery. The purpose would be to construct a list of relevant PO Ids that fulfill the desired filter criteria. Fetch this list of Ids and then use that list as a where-clause in the entityquery like

 .Where(filteredListOfIds.Contains(PurchaseOrderFields.Id))

This might actually be more efficient down the road, but i have a feeling its not necessary.

Am I doing this the wrong way - missing something obvious? Any ideas?`

BR, Jan

MySQL version 5.6.43 LLBLGen version 5.7 (5.7.1) RTM DevArt dotConnect for MySQL Express version 8.16

Hi Jan!

JanRHansen wrote:

Now, to select and display POs in a Telerik Grid, I would do something like this:

var purchaseOrders = new EntityCollection<PurchaseOrderEntity>();

            using (var adapter = new DataAccessAdapter(SessionHelper.NhldbConnectionString))
            {
                var qf = new QueryFactory();
                var q = qf.PurchaseOrder
                    .From(QueryTarget
                        .LeftJoin(PurchaseOrderEntity.Relations.CustomerEntityUsingCustomerId)
                        .LeftJoin(PurchaseOrderEntity.Relations.CustomerProjectEntityUsingCustomerProjectId)
                        .LeftJoin(PurchaseOrderEntity.Relations.EmployeeEntityUsingEmployeeId)
                        .LeftJoin(PurchaseOrderEntity.Relations.PurchaseOrderLineEntityUsingPurchaseOrderId)
                        .LeftJoin(PurchaseOrderEntity.Relations.SupplierEntityUsingSupplierId))
                    .WithPath(PurchaseOrderEntity.PrefetchPathCustomer)
                    .WithPath(PurchaseOrderEntity.PrefetchPathCustomerProject)
                    .WithPath(PurchaseOrderEntity.PrefetchPathEmployee)
                    .WithPath(PurchaseOrderEntity.PrefetchPathPurchaseOrderLines)
                    .WithPath(PurchaseOrderEntity.PrefetchPathSupplier)
                    ;
...            

Above is not necessary the .LeftJoin clauses since you are not filtering in those relations directly. Try to get rid of unnecessary relationships.

JanRHansen wrote:

The problem lies within the filter-part at the bottom, as we have some specific workflows where an employee might need to see the POs that fulfill some "crazy" filter criteria. Its easy enough to just filter on PO-fields, or POL-fields, but if I wanted this...

Here is approximate code for: All orders where the total count of lines ready for invoice is greater than the total count of lines already invoiced.

.Where(qf.PurchaseOrderLine
    .CorrelatedOver(PurchaseOrderLineEntity.Relations.PurchaseOrderEntityUsingCustomerId)
    .Where(PurchaseOrderLineFields.IsReadyForInvoice == true)
    .CountRow().GreaterThan(
        qf.PurchaseOrderLine
       .CorrelatedOver(PurchaseOrderLineEntity.Relations.PurchaseOrderEntityUsingCustomerId)
        .Where(OrderFields.IsInvoiced == true)
        .CountRow()
    )
);

I will give you an example using Northwind. I want all customers that the total count of orders shipped with express (shipvia==1) is greater than the total count of his orders shipped vía federal shipping (shipvia==3). This is the code:

var qf = new QueryFactory();

var q = qf.Customer
        .Where(qf.Order
            .CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId)
            .Where(OrderFields.ShipVia == 1)
            .CountRow().GreaterThan(
                qf.Order
                .CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId)
                .Where(OrderFields.ShipVia == 3)
                .CountRow()
            )
        );

adapter.FetchQuery(q, customers);

You can find more examples in your LLBLGen installation folder: LLBLGen Pro v5.8\Frameworks\LLBLGen Pro\ExampleQueries\QuerySpec

I Hope that helps

Hi David

Thanks a lot. Very detailed answer, and spot on! I actually worked out that exact solution 20 minutes before I got your reply

However, one question still remains unanswered: is it a good idea?

You write that the .LeftJoin parts can be left out, as they are not used to filter - but they are used to fetch child elements, so that once I get to work with every grid item in the "_OnItemCreated" event, I have the main element and its child elements ready at hand and can enable/disable controls etc. based on different parameters and logic.

I think the problem is actually more like - every time a grid requires data, we effectively get all data for the entire grid (not considering paging) and the grid renders the relevant part of it, taking any built-in filtering, grouping and paging into account. It works (for now) - but I have a feeling that complex data sources with a large-ish number of child entities just don't play well with grids with filtering, grouping and paging. In that case it might be better to follow a different strategy, like only fetching the top level "main" elements, and once the grid is created, child elements are fetched as needed. That would result in a higher number of fetch operations, but less data each time and that might prove more efficient. Any thoughts on that subject?

/Jan

JanRHansen wrote:

Thanks a lot. Very detailed answer, and spot on! I actually worked out that exact solution 20 minutes before I got your reply

Good that you figured it out!

JanRHansenHowever, wrote:

You write that the .LeftJoin parts can be left out, as they are not used to filter - but they are used to fetch child elements, so that once I get to work with every grid item in the "_OnItemCreated" event, I have the main element and its child elements ready at hand and can enable/disable controls etc. based on different parameters and logic.

The LLBLGen Pro runtime framework creates a sub-query for each node in a Prefetch Path to be able to filter child nodes on the query results of the parent nodes. So it's really very efficient producing the best queries that enhance the performance. Of course at the end of the day this also depends upon other factors like DB design, amount of data, your own code, etc.
Here are more about PrefetchPaths Optimization.

JanRHansen wrote:

I think the problem is actually more like - every time a grid requires data, we effectively get all data for the entire grid (not considering paging) and the grid renders the relevant part of it, taking any built-in filtering, grouping and paging into account. It works (for now) - but I have a feeling that complex data sources with a large-ish number of child entities just don't play well with grids with filtering, grouping and paging. In that case it might be better to follow a different strategy, like only fetching the top level "main" elements, and once the grid is created, child elements are fetched as needed. That would result in a higher number of fetch operations, but less data each time and that might prove more efficient. Any thoughts on that subject?

I think I would recommend two things based on what worked for me before:

1. Use server-side paging, unless your data is small and you prefer to paging on the client. Normally paging on the server requires to work a little more on your GUI, but it's best in the long term.
2. Fetch the detail on-demand. This could work if the grid is showing only the header information, when you click on some row button, it takes you to another detailed page. This depends on your user UI journey/experience.