Hi,

What would you recommend as the best way to determine if a pre-fetch request would actually return any results?

So, if I'm retrieving a customer, what would be the best way to determine if the customer had made any orders without actually doing a pre-fetch on the orders?

You see, if any orders existed, I wouldn't want to display them. I would just want to link to a web page where they would be displayed. If there aren't any, then I want to hide the link.

Cheers,

Ian.

You could try using

if(new OrdersCollection().GetDbCount(PredicateFactory.CompareValue(CustomersFieldIndex.CustomerId, ComparisonOperator.Equals, yourCustomerId)) > 0)
{
//display link
}

that would do the trick. I just personally wish this method was static since it shouldnt really require an instance of the collection to run it.

Matt

Thanks Matt.

It's not static, because it has to re-use an existing transaction object if present, so a GetDbCount query won't block on an existing transaction. The transaction object is stored in the base class.

Ahh, that totally makes sense now! I didnt even think of the transactional nature of these methods.

Thanks.

Matt

What if I wanted to return a list of customers? What would be the best way to look up whether an order exists for each row all in one query?

I could join to the orders and then group by the customer fields and just add up the orderIds but isn't this a bit intense?

I think it would be cleaner to just have sub-query go and look to see if an order exists for each customer returned.

This seems to me as a property of the customer that should be added as a field to the customer table (OrdersCount). Incremented and decremented upon adding/deleting an order.

Otherwise this could be implemented as a database view, and then you map this view to a TypedView or to an Entity in LLBLGen Pro Designer.

These are the best design-wise options I could think of.

if your customer collection is smaller tahn say 500 objects, you could indeed use a query with a range, (select orderid from orders where customerid in (.... ))

Do you mean a seperate query which is run after the inital select has been performed?

Ian wrote:

Do you mean a seperate query which is run after the inital select has been performed?

Yes, though you of course could also formulate one query without fetching any data at all, by specifying your customer filter as teh filter for a FieldCompareSetPredicate.