Hello

I'm using LLBLGen 3.5, self-servicing, in a Winforms/.net 4.5/C# solution with SQL Server 2012 back end and I am having difficulty using LLBLGen to extract data from somebody else's schema design.

Can anybody help me identify the best approach, please?

There is a generic 'Class' table, in which objects are classified by a 'TypeId'. The relationships between objects appear in the 'ClassRelationship' table, which has a 'RelationshipTypeId' to distinguish different relationship types.

My problem is that I want to join Class (c1) -> ClassRelationship -> Class (c2) with some predicates and I'm not sure how best to do that.

The SQL I'm trying to achieve is ...

SELECT c2.* FROM component.Class c1 INNER JOIN component.ClassRelationship cr ON c1.Class_ID = cr.Class_ID INNER JOIN component.Class c2 ON cr.RelatedClass_ID = c2.Class_ID WHERE cr.RelationshipDefinition_ID = 48 AND c1.Type_ID = 43 AND c2.Type_ID = 47

Obviously, I'd like to return a collection from c2. What's my best approach here? Thanks for any comments or help you can give. Please be as specific as you can because I'm more than a little bit stupid!

Thanks very much

Carl

Hi Carl,

You need to alias the second 'class' entity on the relation, and add that alias to every filter you need on that 'c2' class table. Something like this:

var relations = new ClassCollection();
relations.Add(ClassEntity.Relations.ClassRelationshipEntityUsingClassId);
// alias the end entity of the relationship (Class entity)
relations.Add(ClassRelationshipEntity.Relations.ClassEntityUsingRelatedClassId, "c2");

var filter = new PredicateExpression();
filter.Add(ClassRelationshipFields.RelationshipDefinitionId == 48);
filter.Add(ClassFields.TypeId == 43);
filter.Add(ClassFields.TypeId.SetObjectAlias("c2") == 47);

var classes2 = new ClassCollection();
classes2.GetMulti(filter, relations);

This is pointed out at Advance filtering's documentation section.

Daelmo

Thanks very much for your reply - I appreciate your help.

Best regards

CRW