I have 3 entities Webpage ---< Webpage_Element >-- Element

Webpage has WebPageID as PK Element has ElementID as PK WebPage_Element links the 2, allowing a webpage to have many Elements and allowing Elements to appear on multiple Webpages

I want a list of Elements ordered by Webpage_Element.Disp_Order

The last line of this code:

        Dim elements As New ElementCollection


        Dim sorter As ISortExpression = New SortExpression( _
         New SortClause(WebpageElementFields.DisplayOrder, SortOperator.Ascending))
        wp.SetCollectionParametersElementCollectionViaWebpageElement(0, sorter)

        elements = wp.GetMultiElementCollectionViaWebpageElement(True)

errors with:

[OracleException: ORA-00904: invalid column name]
   Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure) +303
   Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src) +45
   Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) +3701
   Oracle.DataAccess.Client.OracleCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +35
   SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +253

[ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: ORA-00904: invalid column name. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.]
   SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +406
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, ITransaction containingTransaction, IEntityCollection collectionToFill, Boolean allowDuplicates, IEntityFields fieldsUsedForQuery) +697
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGetMultiAction(ITransaction containingTransaction, IEntityCollection collectionToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, Int32 pageNumber, Int32 pageSize) +416
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetMulti(ITransaction containingTransaction, IEntityCollection collectionToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IEntityFactory entityFactoryToUse, IPredicate selectFilter, IRelationCollection relations, Int32 pageNumber, Int32 pageSize) +68
   SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase`1.GetMulti(IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relations, Int32 pageNumber, Int32 pageSize) +194
   SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase`1.GetMulti(IPredicate selectFilter, IRelationCollection relations) +84
   Fluor.GD.DAL.EntityClasses.WebpageEntity.GetMultiElementCollectionViaWebpageElement(Boolean forceFetch, IEntityFactory entityFactoryToUse) in D:\Data\vss\Gradop\GradOpSolution\DAL\EntityClasses\WebpageEntity.vb:471
   Fluor.GD.DAL.EntityClasses.WebpageEntity.GetMultiElementCollectionViaWebpageElement(Boolean forceFetch) in D:\Data\vss\Gradop\GradOpSolution\DAL\EntityClasses\WebpageEntity.vb:448
   main.AddElementsToPage(WebpageEntity wp) in D:\Data\vss\Gradop\GradOpSolution\GradOp\main.aspx.vb:66
   main.BuildPage() in D:\Data\vss\Gradop\GradOpSolution\GradOp\main.aspx.vb:31
   main.Page_Init(Object sender, EventArgs e) in D:\Data\vss\Gradop\GradOpSolution\GradOp\main.aspx.vb:17
   System.Web.UI.Control.OnInit(EventArgs e) +2072044
   System.Web.UI.Page.OnInit(EventArgs e) +9
   System.Web.UI.Control.InitRecursive(Control namingContainer) +321
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +692

Method Exit: CreateSelectDQ
Method Enter: DaoBase.ExecuteSingleRowRetrievalQuery
Method Exit: DaoBase.ExecuteSingleRowRetrievalQuery
Method Exit: DaoBase.PerformFetchEntityAction
Method Enter: DaoBase.PerformGetMultiAction
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT "GD"."ELEMENT"."ELEMENT_ID" AS "ElementId", "GD"."ELEMENT"."ELEMENT_TYPE" AS "ElementType", "GD"."ELEMENT"."DISP_ORDER" AS "DisplayOrder", "GD"."ELEMENT"."POSITION_LEFT" AS "PositionLeft", "GD"."ELEMENT"."POSITION_TOP" AS "PositionTop", "GD"."ELEMENT"."WIDTH" AS "Width", "GD"."ELEMENT"."HEIGHT" AS "Height", "GD"."ELEMENT"."CSS" AS "Css", "GD"."ELEMENT"."START_DATE" AS "StartDate", "GD"."ELEMENT"."END_DATE" AS "EndDate", "GD"."ELEMENT"."LAST_EDIT_DATE" AS "LastEditDate", "GD"."ELEMENT"."LAST_EDIT_BY" AS "LastEditBy" FROM  "GD"."WEBPAGE" "LPA__1", "GD"."WEBPAGE_ELEMENT" "LPA_W2", "GD"."ELEMENT" WHERE  "LPA__1"."WEBPAGE_ID"="LPA_W2"."WEBPAGE_ID" AND "GD"."ELEMENT"."ELEMENT_ID"="LPA_W2"."ELEMENT_ID" AND ( ( "LPA__1"."WEBPAGE_ID" = :WebpageId1)) ORDER BY "GD"."WEBPAGE_ELEMENT"."DISP_ORDER" ASC
    Parameter: :WebpageId1 : Int32. Length: 0. Precision: 9. Scale: 0. Direction: Input. Value: 1.

Method Exit: CreateSelectDQ
The thread 0xdb0 has exited with code 0 (0x0).
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: DaoBase.ExecuteMultiRowRetrievalQuery
A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.DLL

It appears that ordering by "GD"."WEBPAGE_ELEMENT"."DISP_ORDER" fails because the table has been aliased. Changing the ORDER BY Statement as follows fixs the problem:

SELECT   "GD"."ELEMENT"."ELEMENT_ID" AS "ElementId",
         "GD"."ELEMENT"."ELEMENT_TYPE" AS "ElementType",
         "GD"."ELEMENT"."DISP_ORDER" AS "DisplayOrder",
         "GD"."ELEMENT"."POSITION_LEFT" AS "PositionLeft",
         "GD"."ELEMENT"."POSITION_TOP" AS "PositionTop",
         "GD"."ELEMENT"."WIDTH" AS "Width",
         "GD"."ELEMENT"."HEIGHT" AS "Height", "GD"."ELEMENT"."CSS" AS "Css",
         "GD"."ELEMENT"."START_DATE" AS "StartDate",
         "GD"."ELEMENT"."END_DATE" AS "EndDate",
         "GD"."ELEMENT"."LAST_EDIT_DATE" AS "LastEditDate",
         "GD"."ELEMENT"."LAST_EDIT_BY" AS "LastEditBy"
    FROM "GD"."WEBPAGE" "LPA__1",
         "GD"."WEBPAGE_ELEMENT" "LPA_W2",
         "GD"."ELEMENT"
   WHERE "LPA__1"."WEBPAGE_ID" = "LPA_W2"."WEBPAGE_ID"
     AND "GD"."ELEMENT"."ELEMENT_ID" = "LPA_W2"."ELEMENT_ID"
     AND (("LPA__1"."WEBPAGE_ID" = :webpageid1))
ORDER BY "LPA_W2"."DISP_ORDER" ASC 

llblgen 2.0.0.0 Final 15 June 2007 Self-service

Oracle 9.2 ODP

Am I doing something wrong? Graham

I guess you will need to fetch the elements as follows: elements.GetMulti(...);

Passing the required filters and SortExpression. And pass the needed Relations in the relationCollection parameter to filter on the related entities and sort on a related entity as well.