Using v2.6, .NET 2.0, SelfServicing...

I'm having problems with this query:

select BOOK_TOC.*, versionOuter.* from BOOK_TOC_VERSION as versionOuter

inner join
    (
        select BOOK_TOC_ID, max(VERSION_ID) as MAX_VERSION_ID 
        from BOOK_TOC_VERSION
        where VERSION_ID <= @versionId
        group by BOOK_TOC_ID
    ) 
    as versionInner 
    on 
        versionOuter.BOOK_TOC_ID = versionInner.BOOK_TOC_ID 
        and 
        versionOuter.VERSION_ID = versionInner.MAX_VERSION_ID

inner join 
    BOOK_TOC on versionOuter.BOOK_TOC_ID = BOOK_TOC.BOOK_TOC_ID

-- plus varying WHERE clauses 
-- e.g. where BOOK_TOC_VERSION.ANOTHER_FIELD = 1234
-- e.g. where BOOK_TOC.YET_ANOTHER_FIELD = 'abc'

Specifically, the problem is the inner join to the subquery. For that I was trying dynamic relations and derived tables but that usually got me as far as:

select from (s) inner join t

whereas I am trying to get:

select from t inner join(s)

The main query is executed on a BookTocCollection, and I have a prefetch and relation to get the BookTocVersionCollection. So the current result is:

            PrefetchPath prefetch = new PrefetchPath(EntityType.BookTocEntity);
            prefetch.Add(BookTocEntity.PrefetchPathBookTocVersion);

            RelationCollection relations = new RelationCollection();
            relations.Add(BookTocEntity.Relations.BookTocVersionEntityUsingBookTocId);

            /* something needs to go here to make magic :) */

            BookTocCollection bookTocCollection = new BookTocCollection();
            bookTocCollection.GetMulti(
                predicate, //my varying where clause passed into the method
                0,
                new SortExpression(BookTocVersionFields.SiblingOrder | SortOperator.Ascending),
                relations,
                prefetch);

Any assistance will be much appreciated.

Thanks

Hi there,

You can achieve that using Derived tables and dynamic relations. This is an approximate code snippet (not tested) I cooked for you:

// first specify the elements in the derived table select (which is a dyn. list)
ResultsetFields dtFields = new ResultsetFields(2);
dtFields.DefineField(BookTocVersionFields.BookTocId, 0);
dtFields.DefineField(BookTocVersionId.SetAggregateFunction(AggregateFunction.Sum), 1, "MaxVersionId");
GroupByCollection dtGroupBy = new GroupByCollection(dtFields[0]);
DerivedTableDefinition dtDefinition = new DerivedTableDefinition(dtFields, "versionInner", 
    new PredicateExpression(BookTocVersionFields.VersionId <= someVersionId), dtGroupBy);

// then specify the relation. 
// derivedtable spec, join type, end entity type, alias first element, alias end element, on clause filter
DynamicRelation relation = new DynamicRelation(dtDefinition, JoinHint.Inner, 
                    EntityType.OrderEntity, "versionOuter",                 
                    BookTocVersionFields.BookTocIdId.SetObjectAlias("versionInner")
                           == BookTocVersionFields.BookTocId.SetObjectAlias("versionOuter")
                    &&
                    BookTocVersionFields.VersionId.SetFieldAlias("MaxVersionId").SetObjectAlias("versionInner") 
                           == BookTocVersionFields.VersionId.SetObjectAlias("versionOuter") );

// then specify the rest of the query elements
RelationCollection relations = new RelationCollection();
relations.Add(relation);
relations.SelectListAlias = "versionOuter";

// filter
IPredicate filter = .... some filter

// prefetchpath
PrefetchPath prefetch = new PrefetchPath(EntityType.BookTocEntity);
prefetch.Add(BookTocEntity.PrefetchPathBookTocVersion);
            
// sorter
SorExpression sorter = ....         
            
// then fetch the data
BookTocVersionCollection versions = new BookTocVersionCollection();
BookTocVersionCollection.GetMulti(filter, 0, sorter, relations, prefetchpath);

Let us know if you need further help

This was the approach I had figured out myself and did not even come close to resolving the problem.

My impression is llblgen cannot do what I need it to do, so we decided to replace it with standard Linq to SQL.

Thanks for your assistance.

winter_limelight wrote:

This was the approach I had figured out myself and did not even come close to resolving the problem.

My impression is llblgen cannot do what I need it to do, so we decided to replace it with standard Linq to SQL.

Thanks for your assistance.

There's nothing linq to sql can do that we can't (except union). Linq to sql requires .net 3.5, so if you're willing to move to .net 3.5, you can also use our linq provider to write the query.

Also, have you tried David's code and what query did it result in? (i.o.w.: was the SQL generated indeed not what you asked for?)