LLBLGen Pro. Version: 2.6 Final (May 15th, 2009)

Hi,

I'm pretty new with llblgen so pardon me if this question doesn't make sense or the answer is too obvious.

I'm trying to fetch entities based on the predicate of a related entity and not sure how to do it. So I have a Customer entity with Order entities related to each customer. What I want to do it get all Customers that have at least one order in the past month and also sort them based on the Customer with the most recent order.

The code that I have so far is something like this

EntityCollection<Customer> customers = new EntityCollection<Customer>();

ISortExpression sortExpression = new SortExpression(orderDateField | SortOperator.Descending);
IPredicateExpression predicateExpression = new PredicateExpression(orderDateField >= DateTime.Now.AddMonths(-1));

IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.CustomerEntity);
prefetchPath.Add(CustomerEntity.PrefetchPathOrders, 1, predicateExpression, null, sortExpression);

DataAccessAdapter adapter = new DataAccessAdapter();
dataAdapter.FetchEntityCollection(customers, null, 0, null, prefetchPath);

Of course it doesn't work. All customers will be returned and not sorted the by their more recent Order date too. I'm not sure how I can use the fields from Order to get Customer the way I want.

Thanks!

So to clarify

Ignoring the prefetch for the moment, as that is actually unrelated to the query you are trying to do, and may be clouding the issue.

The SQL you are trying to get to would look something like

SELECT * FROM Customer WHERE
    (SELECT Count(*) FROM Order where Order.CustomerId  = Customer.Id 
     AND Order.OrderDate <in the last month>)

Is this what you are after ?

Matt

Yes, that's pretty much what I'm looking for.

I also want to sort customer by order date so it would be a join with the Order table and then order by. That's why I thought prefetch would help.

Thanks!

This is a similar sample. Please take a look: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=12038&StartAtMessage=0&#66842

That's exactly what I'm looking for. Looks like relation performs an INNER JOIN so I can call related fields for filters and sorting.

Thanks David!