I'm trying to join a table to itself, where no join exists in the database. The join will be based on a few specific criteria. Since no relation exists, I'm not sure how to create the join in my dynamic typed list. I also haven't used LLBLGen for a while (the good news is: we are using it on several new projects now ).

I'm about 90% sure that I am not thinking about this correctly, and that I can accomplish the same thing with a subquery.

Here is my current query. Basically I want to count similar events that happened previous to the current event (and include the current in the count). I had to change some names because it's a client project. Hoepfully it still makes sense.

    SELECT
        COUNT(t2.transactioneventid) count
    FROM
        TransactionEvent t1
        INNER JOIN TransactionEvent t2 ON 
            (t2.transactioneventdate <= t1.transactioneventdate 
            AND t2.transactioneventtype = t1.transactioneventtype
            AND t2.otherId = t1.otherId)
    WHERE
        t1.transactioneventid = 123456

Thanks for any help.

Any thoughts on this?

Sorry for the delay, it must have slipped through the cracks.

A join which isn't present as a relation can be done by creating a new EntityRelation object. You then add the fields participating in that relation to the EntityRelation and add that to a RelationCollection (or bucket.Relations) as you'd do otherwise. Please see one of the generated Relations classes for ideas how to create your own EntityRelation object. You don't need to add inheritance info as its done in the generated relation classes.