I tell you what, I must be the thickest guy who has ever tried to use this product - the concepts just keep washing over me.

Today I have been trying to select data into an entity collection using filters set on columns in related tables. Here is the query I am hoping to create (of course each table here has it's own Entity/Entity Collections generated):

select Spec.* from dbo.Spec inner join dbo.BreedType on dbo.Spec.BreedTypeFK = dbo.BreedType.BreedTypeK inner join dbo.Breed on dbo.BreedType.BreedFK = dbo.Breed.BreedK inner join dbo.Type on dbo.BreedType.TypeFK = dbo.Type.TypeK where dbo.Breed.Code = @Breed and dbo.Type.Code = @Type

The database has all relationships, foreign/primary keys created correctly and the generated code reflects this.

I have tried using prefetchPaths etc but when I view the select query it never contains the inner joins. Please help.... my head hurts!!

Even if someone out there could tell me that I am looking in the wrong place and point me in the right direction, I would be super thankfull!

(BTW I am using the self-servicing template)

Let's break your query down in a couple of parts SQL queries are very simple, they have several parts and LLBLGen Pro has the same parts.

select  Spec.* 
from    dbo.Spec inner join dbo.BreedType 
        on dbo.Spec.BreedTypeFK = dbo.BreedType.BreedTypeK 
        inner join dbo.Breed 
        on dbo.BreedType.BreedFK = dbo.Breed.BreedK 
        inner join dbo.Type 
        on dbo.BreedType.TypeFK = dbo.Type.TypeK 
where   dbo.Breed.Code = @Breed 
        and 
        dbo.Type.Code = @Type

In your query you have 3 parts: 1) SELECT Spec.* 2) FROM dbo.... 3) WHERE dbo...

As LLBLGen Pro's code let's you query objects, you should see the complete set of data as the complete set of objects to retrieve, and you define a filter on the total set of objects to retrieve the subset of the objects you want to retrieve. The same goes for SQL: you select data (SELECT Spec.*) from the complete set of data, using a filter (build using FROM and WHERE clauses).

The FROM clauses you build using relations, as they define the relation set with all the entities (tables) from which the data should be retrieved, the WHERE clauses you build with a predicate expression, using one predicate for each clause in your where (in this case 2).

Ok, you want spec objects, so let's define that:

SpecCollection specs = new SpecCollection();

Now, we have to define the relations for the FROM clause.

RelationCollection relations = new RelationCollection();
relations.Add(SpecEntity.Relations.BreedTypeEntityUsingBreedTypeFK);
relations.Add(BreedTypeEntity.Relations.BreedEntityUsingBreefFK);
relations.Add(BreedEntity.Relations.TypeEntityUsingTypeFK);

you start at the entity you want to retrieve and work towards the entity you want to filter on.

Now it's time to setup the WHERE clauses.

PredicateExpression filter = new PredicateExpression();
filter.Add(PredicateFactory.CompareValue(BreedFieldIndex.Code, ComparisonOperator.Equal, _breedValue));
filter.AddWithAnd(PredicateFactory.CompareValue(TypeFieldIndex.Code, ComparisonOperator.Equal, _typeValue));

Done! we can now query the database:

specs.GetMulti(filter, relations);

For adapter, it's similar, there you define an entity collection using:

EntityCollection specs = new EntityCollection(new SpecEntityFactory());

and the relationcollection and predicate expression are bundled in one object: RelationPredicateBucket.