I have just found what looks to be the fastest way to get a limited subset of child entities per parent entity (the parent entity 'equ' primary key is tag_number below - child is wko) from a relational constraint in sql server. I'm now perplexed as to how to implement this in LLBLGen.

My Query is:

select wko2.* from ttt.wko wko2 INNER JOIN ( select ttt.wko.wo_number, row_number() over (partition by tag_number order by reqst_date desc) as tag_counts from ttt.wko) wko1 on wko1.wo_number= wko2.wo_number and tag_counts<=5 INNER JOIN ttt.equ equ on equ.tag_number=wko2.tag_number and equ.fac='FAC1' or equ.fac='FAC2'

any push in the right direction would be great!

thanks andrew

ps. in fact i'd like to do this as a prefetch path....with equ...

but IExpression doesnt seem right, neither does DbFunctionCall.... IPredicate seems like it could be the go...but row_number requires the subselect, and seems like overkill... anyone had a go at direct sql->entitycollection parsing (efficient?) ? hmmm...

pps.

maybe it might work with.. bucket.PredicateExpression.Add(new FieldCompareSetPredicate( wko.tag_number, null, new EntityField2("gettags", DbFunctionCall([callafunctiontogetlistofgoodtags], null, SetOperator.In, null));

where callafunctiontogetlistofgoodtags is an abbreviated sql query of the one above? hmmm....

This is called a 'derived table' and support for that comes in v2.6. (a select as an operand in a JOIN statement). In v2.5, you either have to join the inner tables of the select with the main select and move the predicates to the main query (which is sometimes slower), or create a view from your subquery.

I must utilize the existing WkoEntity type.

So how would you recommend I implement that? with a view?

Will we be able to utilise row_number() in 2.6?

Cheers Andrew

The row_number() is a paging construct which you can rewrite as a COUNT(*) subquery grouped over tag_number, as you use it solely to filter out elements which have a tag set on more than 5 different dates, am I correct?

So you can rewrite that derived table query as a field compare value predicate to filter out the elements which have more than 5 days with a given tag.

something like this:

select  wko2.* 
from    ttt.wko wko2 INNER JOIN ttt.wko.wo_number wko1 
        on wko1.wo_number= wko2.wo_number
        INNER JOIN ttt.equ equ on equ.tag_number=wko2.tag_number 
        and equ.fac='FAC1' or equ.fac='FAC2'
where   (
            select  count(reqst_date) as NumberOfDates
            from    ttt.wko.wo_number wko3
            where wko3.wo_number = wko1.wo_number
            group by tag_number
        ) <= 5

(I assumed wo_number is the PK field of wko) that where clause is a field compare value predicate where you place the scalar query as an expression on the field compared to 5

Otis wrote:

The row_number() is a paging construct which you can rewrite as a COUNT(*) subquery grouped over tag_number, as you use it solely to filter out elements which have a tag set on more than 5 different dates, am I correct?

No not quite, I am getting the last 5 wko for each equ (ie 5 last wko for each equ based on the request_date).

I would be interested to see if we could do this with LLBL?

To solve it I made a few of the original sql above, and use llbl to do the join (but it means I'm restricted to the number 5) on the WkoEntityCollection. It would be great to solve this so we could use any number (ie last 2/3/900 records). Row_number is really the best thing i've seen for this.

What do you think?

thanks heaps! a

Then you indeed need a derived table which comes with v2.6.

I was assuming you needed just the count as you didn't refer to the rest in the main query (as far as I understood, I didn't run the query)