Hi,

retrieval: adapter LLBL: v2.0 code: C#

I'm new to LLBL and i was wondering...how would i do the equivalent of the following,

select a.* from MyOrders a inner join (select StateId, CustomerId, max(OrderDate) as mostRecent from MyOrders group by StateId, CustomerId) b on a.StateId = b.StateId and a.CustomerId = b.CustomerId and a.OrderDate = b.mostRecent where a.StateId = @stateId

i have an "MyOrders" table and i want to be able to retrieve a list of every customer's most recent order by state.

thanks in advance.

w

LLBL cannot process sub queries in the from clause. however you can rewrite the query this way

select MyOrders.*
from MyOrders 
where MyOrders.StateId = @State
      MyOrders.OrderDate = (
           select max(o.OrderDate)
           from MyOrders o
           where o.StateId = @state
           group by o.CustomerId
)

You can use the FieldSetPredicate to construct the sub select where clause. it would look something like this

EntityCollection<MyOrders> listOfOrders = new EntityCollection<MyOrders>();

IGroupingCollection grouping = new GroupingCollection();
grouping.Add(MyOrdersFields.CustomerId.SetObjectAlias("foo"))

IPredicateExpression predicate = new FieldSetPredicate(
     MyOrdersFields.OrderDate, null,
     MyOrdersFields.OrderDate.SetAggregate(Aggregate.Max).SetObjectAlias("foo"), null,
     SetOperator.Equals,
     MyOrdersFields.StateId.SetObjectAlias("foo") == "PA",
     null, null,
     grouping
);

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(MyOrdersFields.StateId == "PA");
bucket.PredicateExpression.Add(predicate);

using (IDataAccessAdapter adapter = new DataAccessAdapter())
{
       adapter.FetchEntityCollection(listOfOrders, bucket);
}
return listOfOrders;

i'm sure there is a problem somewhere in the code. It's off the top of my head, but it gets you started.

you could also write it this way.

EntityCollection<CustomerEntity> listOfCustomers = new EntityCollection<CustomerEntity>();

IPrefetchPath2 prefetch = new PrefetchPath2((int)EntityType.Customer);
prefetch.Add(CustomerEntity.PrefetchMyOrders, 1, new SortExpression(MyOrdersFields.OrderDate | SortOperator.Descending));

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(CustomerFields.StateId == "PA");
bucket.Relations.Add(CustomerEntity.Relations.MyOrdersViaCustomerId);

using (IDataAccessAdapter adapter = new DataAccessAdapter())
{
       adapter.FetchEntityCollection(listOfCustomers, bucket, prefetch);
}

EntityCollection<MyOrdersEntity> listOfOrders = new EntityCollection<MyOrdersEntity>();
foreach(CustomerEntity customer in listOfCustomers)
{
      listOfOrders.Add(customer.MyOrders[0]);
}

return listOfOrders;

the correct way would be:

select a.*
from MyOrders a
where a.StateId = @StateId
    and a.OrderDate = 
        (select max(OrderDate)
            from MyOrders b
            where a.StateId = b.StateId 
            and a.CustomerId = b.CustomerId
        )

The correct class you should look in to is FieldCompareSetPredicate let me know if you need further help.