Hi guys. I have a table named CertificationEvents wich has a VersionNumber field (i.e. the rows are versioned). For example, I can have these records:

ID VersionNumber

1 1 1 2 1 3 2 1 3 1 3 2

I need to retrieve only the last version of each row, i.e. for the previous sample, I want to get:

ID VersionNumber

1 3 2 1 3 1

I found this: http://stackoverflow.com/questions/1971801/nested-select-in-llblgen, but does not work because I need to refer to the same table

Any ideas?

Hi Jose,

Are you using Adapter or SelfServicing? What LLBLGen version are you using?

jselesan wrote:

ID VersionNumber

1 3 2 1 3 1

Maybe you meant:

ID VersionNumber

1 3 2 1 3 2

since, (2) is the last versionNumber for ID (3).

jselesan wrote:

I found this: http://stackoverflow.com/questions/1971801/nested-select-in-llblgen, but does not work because I need to refer to the same table

Any ideas?

It's different from the StackOverflow question, because your table is kind of an intermediate m:n, where your PK is composite (ID and VersionNumber). So the query you want to execute is something like:

SELECT * FROM CertificateEvents
WHERE  VersionNumber = 
     (SELECT MAX(VersionNumber) FROM CertificateEvents C2
      WHERE C2.Id = CertificateEvents.Id)

... to do that you need an ScalarQueryExpression in the filter. This is an approximate code:

var subqueryExp = new ScalarQueryExpression(
     CertificateEventFields.VersionNumber
          .SetObjectAlias("C2")
          .SetAggregateFunction(AggregateFunction.Max),
    (CertificateEventFields.Id.SetObjectAlias("C2") == CertificateEventFields.Id));

var filter = new RelationPredicateBucket(CertificateEventFields.VersionNumber == subqueryExp );

var certificates = new EntityCollection<CertificateEvent>();
using (var adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(certificates, filter);
}

Hope helpful

Hi David, thanks for your answer. I'm using SelfServicing, and I have some other filter (CertificationEvent.IsPredefined == false)

How can I combine RelationPredicateBucket with IPredicate?

thanks

The IRelationPredicateBucket is a container that has both IPredicateExpression and IRelationCollection, and it's used for Adapter only. For SelfServicing you must use the predicate and the relations separately. The above code, in SelfServicing should look like this:

var subqueryExp = new ScalarQueryExpression(
     CertificateEventFields.VersionNumber
         .SetObjectAlias("C2")
         .SetAggregateFunction(AggregateFunction.Max),
    (CertificateEventFields.Id.SetObjectAlias("C2") == CertificateEventFields.Id));

var filter = new PredicateExpression(CertificateEventFields.VersionNumber == subqueryExp );

// fetch
var certificates = new CertificateEventCollection();
certificates.GetMulti(filter);

Hi David, it worked fine!