Hi all -- I'm trying to inner join with a couple of inequalities on tables that aren't explicitly related. For example, to find all course IDs that start within a range defined by two fields in the terms table:

SELECT DISTINCT Course.ID FROM Course INNER JOIN Term ON Course.StartDate >= Term.StartDate AND Course.StartDate < Term.EndDate

or even

SELECT DISTINCT Event.ID FROM Course, Term WHERE...

I've tried adding an otherwise blank relation (new EntityRelation()) with a custom filter, but I think I might be heading down the wrong path.

Anyone know if this is supported in LLBLGen?

Thanks, Alex

You should use a fieldcomparesetpredicate to create a subquery in cases where you need to filter on data which is not directly related to the entities you want to retrieve.

OR, you can create an EntityRelation object in code, similar to the generated code in the Relations classes. Then add your own created EntityRelation object to the RelationCollection.

A blank EntityRelation won't work, it needs at least 2 fields in 2 entities to be able to form a join.

Okay -- it wasn't letting me add any relations because the relations collections of the entities was empty, plus if I created a new EntityRelation with field names, the resulting query would always contain "on field1 = field2" in addition to the custom filter, which broke the query.

I think I figured it out, though. I got the latest version of LLBLGen and regenerated. The new version has a CustomFilterReplacesOnClause property which seems to have the desired effect. The code ends up looking like this: (It appears to work fine.)

IRelationCollection relations = new RelationCollection();
relations.Add( new EntityRelation( 
    EntityFieldFactory.Create( TermFieldIndex.TermId ), 
    EntityFieldFactory.Create( SectionFieldIndex.SectionId ), RelationType.OneToOne ) );
relations[0].CustomFilterReplacesOnClause = true;
relations[0].CustomFilter = filter; // the filter that contains the inequalitites

DataTable list = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, list, 0, null, filter, relations, false, null, null, 0, 0);

I believe the actual entity fields and relationship type used when creating the new entity relations are ignored -- they're only used to define which tables to join.

Thanks for your help and speedy reply! -Alex

alexfranke wrote:

Okay -- it wasn't letting me add any relations because the relations collections of the entities was empty, plus if I created a new EntityRelation with field names, the resulting query would always contain "on field1 = field2" in addition to the custom filter, which broke the query.

I think I figured it out, though. I got the latest version of LLBLGen and regenerated. The new version has a CustomFilterReplacesOnClause property which seems to have the desired effect. The code ends up looking like this: (It appears to work fine.)

IRelationCollection relations = new RelationCollection();
relations.Add( new EntityRelation( 
    EntityFieldFactory.Create( TermFieldIndex.TermId ), 
    EntityFieldFactory.Create( SectionFieldIndex.SectionId ), RelationType.OneToOne ) );
relations[0].CustomFilterReplacesOnClause = true;
relations[0].CustomFilter = filter; // the filter that contains the inequalitites

DataTable list = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, list, 0, null, filter, relations, false, null, null, 0, 0);

I believe the actual entity fields and relationship type used when creating the new entity relations are ignored -- they're only used to define which tables to join.

Correct . I didn't realize you needed this feature, but I'm glad you got it up and running