Hello, I have a resultset that currently looks something like this:

ResultsetFields lrsFields = new ResultsetFields(14);
lrsFields.DefineField(UserFieldIndex.FirstName, 1, "FirstName");
...                 

lrsFields.DefineField(UserFieldIndex.WorksFor, 12, "WorksFor");

This is working OK, but WorksFor is a foreign key that references itself (User table). How can I add UserFieldIndex.FirstName, but for the WorksFor relationship? Is there a way to do something like this when a table references itself?

I can do this using a prefetched path, but I can't seem to do it using the resultset.

UserEntity user = new UserEntity(10);
//create prefetched path

//fetch entity

user.Firstname; //Users first name
user.User.FirstName; //Managers first name

Hi Skeeterbug. You can do that using relations and Aliases (Ref: LLBLGenPro Help - Using generated code - Adapter - Using TypedViews, TypedLists and DynList - Using Dynamic lists).

Copy from Help, here is a snippet that do what you want:

// C# DataAccessAdapter adapter = new DataAccessAdapter(); ResultsetFields fields = new ResultsetFields(3); fields.DefineField(EmployeeFields.FirstName, 0, "FirstNameManager", "Manager"); fields.DefineField(EmployeeFields.LastName, 1, "LastNameManager", "Manager"); fields.DefineField(EmployeeFields.LastName, 2, "AmountEmployees", "Employee", AggregateFunction.Count); IRelationPredicateBucket bucket = new RelationPredicateBucket(); bucket.Relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeId, "Employee", "Manager", JoinHint.None);

IGroupByCollection groupByClause = new GroupByCollection(); groupByClause.Add(fields[0]); groupByClause.Add(fields[1]); DataTable dynamicList = new DataTable(); adapter.FetchTypedList(fields, dynamicList, bucket, 0, null, true, groupByClause);

daelmo wrote:

Hi Skeeterbug. You can do that using relations and Aliases (Ref: LLBLGenPro Help - Using generated code - Adapter - Using TypedViews, TypedLists and DynList - Using Dynamic lists).

Copy from Help, here is a snippet that do what you want:

// C# DataAccessAdapter adapter = new DataAccessAdapter(); ResultsetFields fields = new ResultsetFields(3); fields.DefineField(EmployeeFields.FirstName, 0, "FirstNameManager", "Manager"); fields.DefineField(EmployeeFields.LastName, 1, "LastNameManager", "Manager"); fields.DefineField(EmployeeFields.LastName, 2, "AmountEmployees", "Employee", AggregateFunction.Count); IRelationPredicateBucket bucket = new RelationPredicateBucket(); bucket.Relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeId, "Employee", "Manager", JoinHint.None);

IGroupByCollection groupByClause = new GroupByCollection(); groupByClause.Add(fields[0]); groupByClause.Add(fields[1]); DataTable dynamicList = new DataTable(); adapter.FetchTypedList(fields, dynamicList, bucket, 0, null, true, groupByClause);

Thanks a lot, I must have missed that some how.