Hi,

I'm running LLBLGen 3.5/.NET 4.0 (Self-Servicing | NO adapter), and I was wondering how I can self join a table using a non-PK and partial compound-PK ON clause. i.e.

Table A
----------
PKA1
PKA2
PKA3
C1

Where A1+A2+A3 are a compound PK, and I would like to run the following:

SELECT 
FROM A ab LEFT JOIN A cd
ON (ab.C1 = cd.PKA3)
WHERE ab.C1 = 'X' AND cd.PKA1 = 'Y'

There are more filtering and extra self-joining going on that would give me the correct dataset (I have omitted them for simplicity). I would like to know how I can implement the above in LLBLGen (self-servicing).

Thank you.

You can implement a self-join using DynamicRelation and use the same entity for both left and right joint, i.e.

IPredicateExpression pred = new PredicateExpression()
pred.Add(AFields.C1.SetObjectAlias("ALeft") == 'X');
pred.Add(AFieldsPKA1.SetObjectAlias("ARight") == 'Y');

DynamicRelation selfJoin = new DynamicRelation(EntityType.AEntity, JointHint.Left, EntityType.AEntity, "ALeft", "ARight", AFields.C1.SetObjectAlias("ALeft") == AFields.PKA3.SetObjectAlias("ARight"));

IRelationCollection relations = new RelationCollection();
relations.Add(selfJoin); 
// Do other joins

You also can do it this way:

// create the self relation
var rel = new EntityRelation(RelationType.OneToMany);
rel.AddEntityFieldPair(EmployeeFields.ReportsTo, CustomerFields.CustomerId);
// ... other field pairs

// add the relation and distinguish the tables with aliases
var relations = new RelationCollection();
relations.Add(rel, "e1", "e2", JoinHint.Left);

// filter on both sides of the relation
var filter = new PredicateExpression();
filter.Add(EmployeeFields.Title.SetObjectAlias("e1") == "Mr.");
filter.Add(EmployeeFields.LastName.SetObjectAlias("e2") % "E");

// fetch using the custom filter and relations ...

Thank you daelmo.

I was totally ignorant of the method you shared. Is there a performance increase using your method?

Kilan wrote:

I was totally ignorant of the method you shared.

It's documented here....

Kilan wrote:

Is there a performance increase using your method?

No. Basically you will end up with a different SQL. The one with dynamic relation will be different from this one. I think you don't need a DynamicRelation here. Anyway, test the two approaches to experiment with the resulting SQL.