Frans,

I've been using your demo for about 15 days and I really like LLBLGenPro. It's been really easy to figure out how to use it for most things... Except this:

I have two tables: Definition, and DefinitionType. Definition is sort of self-referencing (not directly). DefinitionType has two FK relations on the Defintion.DefinitionID column. The tables look like this:

Definition table:

DefinitionID
Definition

DefinitionType table:

DefinitionTypeID
DefinitionGroupID
DefinitionID

DefinitionTypeID is a FK on Definition.DefinitionID. DefinitionGroupID is also a FK on Definition.DefinitionID.

So, I might have in my Definition table data like this:

DefinitionID Definition
1                Ongoing
2                Major
3                Minor
4                Cancelled
5                Project Type
6                Status
7                Stopped
8                Completed
9                In Progress


DefinitionTypeID DefinitionGroupID DefinitionID
1                        5                        1
2                        5                        2
3                        5                        3
4                        5                        4
5                        6                        7
6                        6                        8

So, what I want to do is return a list of DefintionID's and Definitions for a certain group, and I don't want to fetch it with the GroupID, but instead the parent Definition. (Fetching via the GroupID is easy, but I don't want to have to know ID numbers).

What I need to do is build a query with LLBLGenPro that does this:

SELECT Definition.DefinitionID, Definition.Definition FROM Definition 
WHERE DefinitionID IN (
SELECT DefinitionType.DefinitionID FROM Definition INNER JOIN DefinitionType ON
Definition.DefinitionID = DefinitionType.DefinitionGroupID
WHERE Definition.Definition = 'Project Type')

How can I build this query with LLBLGenPro?

Edit:

I can also do this query in SQL without a subselect, like so:

SELECT 
a.DefinitionID,
a.Definition
FROM DefinitionType dt 
INNER JOIN Definition a ON dt.DefinitionID = a.DefinitionID
INNER JOIN Definition b ON dt.DefinitionGroupID = b.DefinitionID
WHERE b.Definition = 'Project Type'
ORDER BY a.DefinitionID

But I can't exactly figure out how to make this happen with the LLBLGenPro SelfServicing code...

Your subquery version can be done with a FIeldCompareSet predicate class, which you create by specifying a RelationCollection to which you've added the relation DefinitionEntity.Relations.DefinitionTypeUsingDefinitionGroupID and a PredicateExpression to which you've added a CompareValue predicate which compares DefinitionFieldIndex.Definition with 'Project type'.

that FieldCompareSetPredicate (please check the HowDoI section for a sample of how to construct such a query) is then added to a PredicateExpression which you pass to GetMulti() of the DefinitionCollection.

Joins are done using a RelationCollection to which you add the relations of the entities to use. Please see the how do I section and filtering and sorting in the documentation, and also the Northwind example application's customers per product form which shows you how to perform a large join query.