Frans,

Can you confirm whether this is possible using the Prefetch functionality:

Eg. 3 Tables

Company CompanyID CompanyName

Employee CompanyID PersonID HireDate

Person PersonID PersonName

I want to fetch a CompanyCollection with a Prefetch on the m:n relationship with Person to get the company employees, but I want to sort the employees (PeopleEntity) by HireDate which is defined in the link table Employee. Can I specify a ISortExpression in the PrefetchPath on Person which includes fields in the Employee link table?

Please say "Yes"...

Marcus

I have put together the following Test:

EntityCollection companys = new EntityCollection(new CompanyEntityFactory());

ISortExpression sorter = new SortExpression();
sorter.Add(SortClauseFactory.Create(EmployeeFieldIndex.HireDate, SortOperator.Ascending));

IRelationCollection relations = new RelationCollection();
relations.Add(CompanyEntity.Relations.EmployeeEntityUsingCompanyID, JoinHint.Inner);

IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.CompanyEntity);
prefetchPath.Add(CompanyEntity.PrefetchPathEmployees, 0, null, relations, sorter);

adapter.FetchEntityCollection(companys, null, prefetchPath);

But it appears to ignore the sorting...

It appears that the SQL generated from the above code is wrong:

SELECT [Person].[PersonID] AS [PersonID],
[Person].[PersonUID] AS [PersonUID],
[Person].[Title] AS [Title],
[Person].[FirstName] AS [FirstName],
[Person].[MiddleName] ...
FROM ((( [Company] 
INNER JOIN [Employee] Employee__ALIAS 
ON  [Company].[CompanyID]=Employee__ALIAS.[CompanyID]) 
INNER JOIN [Person] ON  [Person].[PersonID]=Employee__ALIAS.[PersonID]) 
INNER JOIN [Employee] ON  [Company].[CompanyID]=[Employee].[CompanyID]) 
WHERE ( Employee__ALIAS.[CompanyID] IN (SELECT [Company].[CompanyID] AS [CompanyID] 
FROM [Company])) ORDER BY [Employee].[HireDate] DESC

I have removed some of the select fields and the [blah].[dbo]. to make things clearer.

I've fixed it, and it does work.

relations.Add(PersonEntity.Relations.EmployeeEntityUsingPersonID, JoinHint.Inner);

The trick is to add the Relation from the Person, not from the Company...

Cool!

However, the relations you pass to the prefetch path shouldn't be necessary, as the m:n prefetch code already joins the 3 tables together. Though I think that the issue is that the merge between the two entity groups (person and company) uses a 2nd fetch of solely PK-PK combinations from both entities to see which entity belongs to which entity (as that's not clear because that's stored in the intermediate entity which isn't fetched to save bandwith). I've to check but it can be that the sorter isn't passed on to that 2nd fetch.

If you pass just the sorter without the relations it doesn't work?

Otis wrote:

If you pass just the sorter without the relations it doesn't work?

If I leave out the relations:

prefetchPath.Add(CompanyEntity.PrefetchPathEmployees, 0, null, null, sorter);

it throws...

LLBLGen.Templates.TestClass.TestFetchWithPrefetch : SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException : An exception was caught during the execution of a retrieval query: The column prefix 'ModernArk_Ark.dbo.Ark_Employee' does not match with a table name or alias name used in the query.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Ah, but that can be solved with an inside-info trick .

The intermediate entity is aliased to avoid conflicts (that's also the reason why the join with Employees as you added it does work). The alias is: Employee__ALIAS.

Specify that as teh object alias for the sortfield, then it should work

Otis wrote:

Ah, but that can be solved with an inside-info trick .

The intermediate entity is aliased to avoid conflicts (that's also the reason why the join with Employees as you added it does work). The alias is: Employee__ALIAS.

Specify that as teh object alias for the sortfield, then it should work

I should have spotted that myself from the generated SQL above!!!

Otis wrote:

that's also the reason why the join with Employees as you added it does work

Actually the JOIN with Employee from Companies didn't work for a different reason... It was not a table name alias problem. I was getting duplicate results all joined incorrectly...

"__ALIAS" has been changed to "_". Please download the latest shared templates archive. If you have code relying on that suffix, you have to change that code to work with the new templates.