Hi,

I have an entity class with the following fields

id aField bField cField date-started

id is unique, but also the combination aField, bField and date-started is unique.

I want te retrieve an entity collection containing a fields but only that entity that hes the highest date-started.

Is it possible to create the entity collection given by

SELECT id, aField, bField, cField, date-started FROM aFile X WHERE date-started in (SELECT MAX(date-started) FROM aFile Y WHERE X.aField = Y.aField and X.bField = Y.bField)

I need this to bind a datasource to a gridview.

Best regards,

Jan

Hi Jan... There's a lot of threads about your question. Specially this would be very useful:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=6370&HighLight=1

Hi Daelmo

I tried to understand both the help info and the examples. I do not find it obvious. A clear example would help.

I'm using self service.

I understand that trying to find a solution myself helps understand the product. But then again, it is better to copy something good then invent something horrible.

Jan

I would just do a select top 1 and run the query like this.

SELECT top 1 id, aField, bField, cField, date-started FROM aFile order by date-started

you can do this using the aFile collection or through a dynamic list. This is a dynamic list example.

ResultsetFields fields = new ResultsetFields(5);
fields.DefineField(aFileFields.Id, 0);
fields.DefineField(aFileFields.AField, 1);
fields.DefineField(aFileFields.BField, 2);
fields.DefineField(aFileFields.CField, 3);
fields.DefineField(aFileFields.DateStarted, 4);

ISortExpression sorter = new SortExpression(aFileFields.DateStarted | SortOperator.Descending);

DataTable dynamicList = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, dynamicList, 1, sorter, null, null, false, null, null, 0, 0);

The SELECT TOP 1 would only give me 1 record.

I need multiple records.

The combination of aField and bField together with DateStarted is unique. I need to select records with aField, bField combinations that have the most recent DateStarted.

I need to work with predicates. I'm able to work with 'normal' predicates, but have problems finding a way to use specify a select to find the maximum date for a combination of fields in the main select.

SELECT id, aField, bField, cField, date-started FROM aFile X WHERE date-started in (SELECT MAX(date-started) FROM aFile Y WHERE X.aField = Y.aField and X.bField = Y.bField)

To implement the IN predicate use a FieldCompareSetredicate, please refer to the LLBLGen Pro manual "Using the generated code -> Filtering & Sorting" fro more details and examples.

Jan, Threre's a couple of good support threads (thanks Walaa and Otis ): http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=6370 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5249

However I can't find a very clear example of your scenario, so I made one for exercise with the **Northwind **database (_SQL2K, LLBLGen v2.0, Adapter_)

Statement: ** "I want to retrieve ALL ORDERS with the MOST RECENT ORDER'S DATE of the CUSTOMER-EMPLOYEE combination". ** Retrieval Example:

Whole data:
(OrderID, CustomerID, EmployeeID, OrderDate)
10835   ALFKI   1   1998-01-15 00:00:00.000
10952   ALFKI   1   1998-03-16 00:00:00.000
11011   ALFKI   3   1998-04-09 00:00:00.000
10692   ALFKI   4   1997-10-03 00:00:00.000
10702   ALFKI   4   1997-10-13 00:00:00.000
10643   ALFKI   6   1997-08-25 00:00:00.000
10759   ANATR   3   1997-11-28 00:00:00.000
10625   ANATR   3   1997-08-08 00:00:00.000
10926   ANATR   4   1998-03-04 00:00:00.000
10308   ANATR   7   1996-09-18 00:00:00.000

Result Statement Data:
(OrderID, CustomerID, EmployeeID, OrderDate)
10952   ALFKI   1   1998-03-16 00:00:00.000
11011   ALFKI   3   1998-04-09 00:00:00.000
10702   ALFKI   4   1997-10-13 00:00:00.000
10643   ALFKI   6   1997-08-25 00:00:00.000
10759   ANATR   3   1997-11-28 00:00:00.000
10926   ANATR   4   1998-03-04 00:00:00.000
10308   ANATR   7   1996-09-18 00:00:00.000

Possible SQL Query

SELECT * FROM orders
WHERE orders.orderDate = 
    ( select max(orderDate)
       from orders ordersMaxDate
       where   ordersMaxDate.customerID = orders.customerID 
            and ordersMaxDate.employeeID = orders.employeeID 
         )

Scenario's Characteristics: 1. Subquery can't be rewrite as a _JOINS _query. So you need to use FieldCompareSetPredicate. 2. Subquery filter indicate a relation. 3. Relation between Query and SubQuery do not implicate a Relation Database Object. So you can't use a IRelation object either a CustomFilter for EntityRelations. 4. In order to points 1, 2 and 3, you must to compare the query and subquery via a FieldCompareExpressionPredicate. 4. The equal (=) operator is used as the subquery must return only one value (same result as IN, however I don't know right now the performance's difference).

Code Retrieval Example

// collection to fill
EntityCollection<OrdersEntity> orders = new EntityCollection<OrdersEntity>(new OrdersEntityFactory());

// general filter
IRelationPredicateBucket filter = new RelationPredicateBucket();


// -- SETUP THE SUBQUERY --

/// CustomerID = OrdersMaxDate.CustomerID
/// Ref: Generated code - The predicate system - Adapter - FieldCompareExpressionPredicate
IPredicate subQueryFilter1 = 
    new FieldCompareExpressionPredicate(
        OrdersFields.CustomerId, null,
        ComparisonOperator.Equal, 
        new Expression(OrdersFields.CustomerId.SetObjectAlias("OrdersMaxDate")) );

/// EmployeeID = OrdersMaxDate.EmployeeID
/// Ref: Generated code - The predicate system - Adapter - FieldCompareExpressionPredicate
IPredicate subQueryFilter2 =
    new FieldCompareExpressionPredicate(
        OrdersFields.EmployeeId, null,
        ComparisonOperator.Equal,
        new Expression(OrdersFields.EmployeeId.SetObjectAlias("OrdersMaxDate")));


/// CustomerID == OrdersMaxDate.CustomerID and EmployeeID == OrdersMaxDate.EmployeeID
IPredicateExpression subQueryFilters = new PredicateExpression();
subQueryFilters.Add(subQueryFilter1);
subQueryFilters.AddWithAnd(subQueryFilter2);


/// OrderDate = ( MAX(OrderDate) FROM Orders ALIAS OrdersMaxDate 
/// WHERE CustomerID == OrdersMaxDate.CustomerID and EmployeeID == OrdersMaxDate.EmployeeID
/// Ref: Generated code - The predicate system - Adapter - FieldCompareSetPredicate
/// Ref: Generated code - Field expressions and aggretates
filter.PredicateExpression.Add(
    new FieldCompareSetPredicate( 
        OrdersFields.OrderDate, null,
        OrdersFields.OrderDate.SetAggregateFunction(AggregateFunction.Max).SetObjectAlias("OrdersMaxDate"), null,
        SetOperator.In,
       subQueryFilters));

/// ... data retrieval code (depends of the LLBLGen Generator Mode) 

So please adapt the concepts to your case (it's the same scenario) and let us know if you have troubles.

Hope helpful

Hi Daelmo,

I cannot get yours to work. Could it be because I use self service?

There is not that much documentation on this item delivered with the project. De given Set example is simple.

I tried code from other suggestions in this forum aswell. Tend to get an Object reference not set to an object message.

I'm thinking to use inline SQL or call a stored proc. It's taking me to long.

Jan. My mistake: the example only works for Adapter (only tested for Adapter), as the Object reference not set to an object message it's because the IFieldPersistenceInfo is set to null. Here I post the example to work for SelfServicing. Apologies.

// all ORDERS with MOST RECENT DATE for EACH CUSTOMER-EMPLOYEE COMB.

// collection to fill
OrdersCollection orders = new OrdersCollection(new OrdersEntityFactory());

// general filter
IRelationPredicateBucket filter = new RelationPredicateBucket();


// -- SETUP THE SUBQUERY --

// CustomerID = OrdersMaxDate.CustomerID
IPredicate subQueryFilter1 =
    new FieldCompareExpressionPredicate(
        OrdersFields.CustomerId,
        ComparisonOperator.Equal,
        new Expression(OrdersFields.CustomerId.SetObjectAlias("OrdersMaxDate")));

// EmployeeID = OrdersMaxDate.EmployeeID
IPredicate subQueryFilter2 =
    new FieldCompareExpressionPredicate(
        OrdersFields.EmployeeId,
        ComparisonOperator.Equal,
        new Expression(OrdersFields.EmployeeId.SetObjectAlias("OrdersMaxDate")));


// CustomerID == OrdersMaxDate.CustomerID and EmployeeID == OrdersMaxDate.EmployeeID
IPredicateExpression subQueryFilters = new PredicateExpression();
subQueryFilters.Add(subQueryFilter1);
subQueryFilters.AddWithAnd(subQueryFilter2);


/// OrderDate = ( MAX(OrderDate) FROM Orders ALIAS OrdersMaxDate 
/// WHERE CustomerID == OrdersMaxDate.CustomerID and EmployeeID == OrdersMaxDate.EmployeeID
filter.PredicateExpression.Add(
    new FieldCompareSetPredicate(
        OrdersFields.OrderDate,
        OrdersFields.OrderDate.SetAggregateFunction(AggregateFunction.Max).SetObjectAlias("OrdersMaxDate"),
        SetOperator.In,
       subQueryFilters));

// data retrieval
orders.GetMulti(filter.PredicateExpression);

Note: This can be rewritten in a few lines, but for explanation I do it long play.

The learning curve is always a cost-benefit thing. Next time is much more easy.

David

Hi David,

I followed your suggestion and it seems to work. Thanks.

How does a novice user know which of the predicate message to use, especially when Adapter and Self Servicing are both available in the dll's? Is there documentation available other than the pdf that is supplied with the product? Did I miss something?

Jan

Please check the reference manual on disk (in the llblgen pro installation folder) (or you can download the .chm file from the customer area). The .net 2.0 ref manual is available inside vs.net 2005.

Hi David,

I followed your suggestion and it seems to work. Thanks.

I'm glad it finally works!

How does a novice user know which of the predicate message to use, especially when Adapter and Self Servicing are both available in the dll's? Is there documentation available other than the pdf that is supplied with the product? Did I miss something?

I like this path to solve a situation:

1. Read the Documentation (for navigability, I prefer the CHM file). This for understand the architecture, the concepts, guidelines. There are simple examples to understand the concepts.

2. Read the "How do I..." section. Sometimes here are examples with a combination of concepts (like yours).

**3. Review LLBLGen samples to understand a nearly real implementation. ** 4. Review Support Forums existing threads. Is almost sure someone else posted your situation before.

5. Ask for support at Forums.

As a matter of fact, I didn't face your problem scenario before. But this path guide me towards the answer. And the next time as I said before it's a piece of cake.

David

So, I guess the way it went was the way it should