Hi, When user wants to fetch Main Entity while grabbing the Sub Entity. Prefetch is used. During the fetch, I want to filter Main and Sub Entity's Field which can be done through adding QueryParameters.RelationsToUse. With this, OnClause Relation is customizable using DynamicRelation. WHERE filtering can be done using Main and Sub's Field.

But, Sub's Field has been used to filter the Main Entity, it didnt bring the WHERE filter from the Main Entity back to the Sub Entity. The Sub Entity returned is not tally.

I have tried to see if Prefetched Entities can also do the same, custom WHERE filter into the Prefetched. Because LLBL I think will just use predefined relationship (Original Primary Key) to filter based on the value returned Main Entity.

Can LLBL support custom Filter for Prefetch Entities?

You should specify the filter on the prefetch path definition. E.g. the SubPath.Add() call allows you to specify a filter for that node.

Please specify the code you used so we can give you hints what to do. (and what version you're using etc.)

Otis wrote:

You should specify the filter on the prefetch path definition. E.g. the SubPath.Add() call allows you to specify a filter for that node.

Please specify the code you used so we can give you hints what to do. (and what version you're using etc.)

Thank you for the reply. What I needed to achieve is doing something like this: I have 3 Tables: Customer, Order, OrderDetails Relationship: Customer-Order (OrderId), Order-OrderDetails (DetailsId)

I am using QueryParameters to FetchEntityCollection() in which I need to use Relation or DynamicRelation on the QueryParameters.RelationToUse. In order to filter with the OrderTable, I must get the Original LLBL Relationship and set to this QueryParameters.RelationToUse. By doing this, now the fields from Order can be set at the WHERE clause. I understand that FetchEntityCollection will only based on the EntityType that will be the Customer, and fetch the fields based on Customer Table only. But with this “workaround”, I can manage to use fields from Order. The Pseudo-SQL below shows how LLBL is generated.

SELECT Customer.* FROM Customer 
LEFT JOIN Order ON Customer.OrderId = Order.OrderId WHERE Order.Name = “…”

The result returned.

But now I want to prefetch OrderDetails with the Customer. OrderDetails is under Order Table. I still can get the LLBL Relationship and set to the QueryParameters.RelationToUse. But it says that “Relation at Index 1 doesn't contain an entity already added to the FROM clause. Bad alias?” error. Because this relation cannot be generated the WHERE clause using OrderDetails cannot be done.

SELECT Customer.* FROM Customer 
LEFT JOIN Order ON Customer.OrderId = Order.OrderId 
LEFT JOIN OrderDetails ON Order.DetailsId = OrderDetails.DetailsId
WHERE OrderDetails.Details = "..."

Hope to have your reply. Thank you.

Our runtime has 3 query api's: QuerySpec, Linq and the low level API. Please use one of them to fetch your data. QueryParameters is used to group dedicated data together to generate SQL, it's not meant to be used to write your queries, as it's not documented as such.

The prefetch path APIs in each of the 3 query APIs offer what you want.

Otis wrote:

Our runtime has 3 query api's: QuerySpec, Linq and the low level API. Please use one of them to fetch your data. QueryParameters is used to group dedicated data together to generate SQL, it's not meant to be used to write your queries, as it's not documented as such.

The prefetch path APIs in each of the 3 query APIs offer what you want.

Thank you I will try look into it.