Hi, I need to convert the following SQL Query to LLBLGen Code.

I am using VB.Net (.NET Framework 2.0), Visual Studio 2008 and Code Generated using LLBLGen 2.6

SELECT Table1.Name,
       Table1.CreateUserID,
       Table1.EditUserID,
       Creator.Username AS CreatorUsername,
       Editor.Username AS EditorUsername
  FROM Table1
       LEFT OUTER JOIN Table2 AS Creator ON (Table1.CreateUserID = Creator.ID)
       LEFT OUTER JOIN Table2 AS Editor ON (Table1.EditUserID = Editor.ID)

You could use a DynamicList using aliases to identify the relations to the same table. Here is an approximate code:

DataAccessAdapter adapter = new DataAccessAdapter();
ResultsetFields fields = new ResultsetFields(4);
fields.DefineField(Table1Fields.CreateUserId, 0);
fields.DefineField(Table1Fields.EditUserId, 1);
fields.DefineField(Table2Fields.Username, 2, "CreatorUsername", "Creator");
fields.DefineField(Table2Fields.Username, 3, "EditorUsername", "Editor");
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(Table1Entity.Relations.Table2EntityUsingCreatorId, "Creator", JoinHint.Left);
bucket.Relations.Add(Table1Entity.Relations.Table2EntityUsingEditorId, "Editor", JoinHint.Left);

DataTable dynamicList = new DataTable();
adapter.FetchTypedList(fields, dynamicList, bucket, 0, null, true, null);