Hi.. need help with writing the query below as a dynamic query..

SELECT Table2.Id, Table2.Name, Table1.ID FROM Table1
RIGHT JOIN Table2 ON Table1.SomeID = Table2.ID AND SomeColumn = 4 WHERE Table2.AnotherColumn = 1

BTW: I am using Adapter templates and LLBLGenPro v2.5

You can achieve that using a _DynamicList _(read-only) and Custom Relation Filters. Your code should look like:

// define the fields
ResultsetFields fields = new ResultsetFields( 3 );
fields.DefineField( Table2Fields.Id, 0, "Table2Id" );
fields.DefineField( Table2Fields.Name, 1, "Table2Name");
fields.DefineField( Table1Fields.Id, 2, "Table2Id");

// this bucket is the filter+relations
IRelationPredicateBucket bucket = new RelationPredicateBucket();


// make the custom relation

IEntityRelation relationToAdd = new EntityRelation(Table1Fields.SomeId, Table2Fields.Id, RelationType.OneToMany);

// specify the right join
relationToAdd.HintForJoins = JoinHint.Right;

// this custom filter allows you to specify the expression used in the join clause
relationToAdd.CustomFilter = new PredicateExpression(SomeTable2.SomeColumn == 4);
relationToAdd.CustomFilterReplacesOnClause = true;


// add the relation and the whole filter (WHERE clause )
bucket.Relations.Add(relationToAdd);
bucket.PredicateExpression.Add(Table2Fields.AnotherColumn == 1);

// fetch the results
DataTable results = new DataTable();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList( fields, results, bucket );
}

For more info, read: LLBLGenPro Help - Using the genrated code - Adapter - Filtering and sorting - Advance filtering usage - Custom filter for Entity Relations

Cheers

Thanks David.. you guys are great