My two cents: As Walaa says, you only need the JOIN there. Here is a similar example where I want the Manager (parent) information from a specific employee (child).
[TestMethod]
public void FetchParentDetails()
{
// set the fields I want from the Parent
var fields = new EntityFields2(3);
fields.DefineField(EmployeeFields.EmployeeId, 0, "EmployeeId", "Parent");
fields.DefineField(EmployeeFields.FirstName, 1, "FirstName", "Parent");
fields.DefineField(EmployeeFields.LastName, 2, "LastName", "Parent");
// set the filter on the child, and add the appropriate relation
var employeeFilter = new RelationPredicateBucket(EmployeeFields.LastName.SetObjectAlias("Child") == "Davolio");
employeeFilter.Relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeIdReportsTo, "Child", "Parent", JoinHint.Left);
// fetch results
var results = new DataTable();
using (var adapter = new DataAccessAdapter())
{
adapter.FetchTypedList(fields, results, employeeFilter);
}
// test: Davolis's manager should be Fuller.
Assert.AreEqual(1, results.Rows.Count);
Assert.AreEqual("Fuller", results.Rows[0][2].ToString());
}
Note that the filter is on the child, because I want the parent information for the child whose last name is "Davolio". The only difference from your scenario is that you have an additional 1:1 table called PersonDetails. If employee in the example would have that, it would be something like this (add additional join between the parent and the details table:
[TestMethod]
public void FetchParentDetails()
{
// set the fields I want from the Parent
var fields = new EntityFields2(3);
fields.DefineField(EmployeeDetailsFields.EmployeeId, 0, "EmployeeId", "ParentDetails");
fields.DefineField(EmployeeDetailsFields.FirstName, 1, "FirstName", "ParentDetails");
fields.DefineField(EmployeeDetailsFields.LastName, 2, "LastName", "ParentDetails");
// set the filter on the child, and add the appropriate relation
var employeeFilter = new RelationPredicateBucket(EmployeeFields.LastName.SetObjectAlias("Child") == "Davolio");
employeeFilter.Relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeIdReportsTo, "Child", "Parent", JoinHint.Left);
employeeFilter.Relations.Add(EmployeeEntity.Relations.EmployeeDetailsEntityUsingEmployeeIdEmployeeId, "Parent", "ParentDetails", JoinHint.Inneer);
// fetch results
var results = new DataTable();
using (var adapter = new DataAccessAdapter())
{
adapter.FetchTypedList(fields, results, employeeFilter);
}
// test: Davolis's manager should be Fuller.
Assert.AreEqual(1, results.Rows.Count);
Assert.AreEqual("Fuller", results.Rows[0][2].ToString());
}
Basically you have to pay attention to the aliases, define them in the relation objects and reference them in your fields definition and filters.
glad it's solved!