**LLBLGEN Version: **3.1 Final .NET Version: 4.0 **Template Group: **Adapter **Database: **Oracle 10g **Inheritance Heirarchy: **None

Hello,

I have an entity class called EmlAddressEntity. This has a relation to a class called EmlBusinessUnit, in which the EmlAddressEntity class has a property for this related class. In my code I have an execute query method which fetches an EntityCollection<EmlAddressEntity> with a prefetch of the EmlBusinessUnit entity related.

In my business rules I need to order the collection by the EmlBusinessUnit.Description, of the related entity, then by the EmlAddressEntity.Description.

I am having trouble trying to figure out how to use the sorter to fetch the collection sorted as I need. Below is the code and the exception that is thrown when the search is executed. It looks like I am not using the SorterExpression correctly, I am just not sure how to remedy that.

Any help is much appreciated.

Thanks, Shaun

Execute Query Method:

private void ExecuteQuery()
        {
            var filter = BuildFilter();

            using (var adapter = GetAdapter())
            {
                PrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.EmlEmailAddressEntity);
                prefetchPath.Add(EmlEmailAddressEntity.PrefetchPathEmlBusinessUnit);
                SortExpression sorter = new SortExpression(EmlBusinessUnitFields.Description | SortOperator.Ascending);
                sorter.Add(EmlEmailAddressFields.Description | SortOperator.Ascending);
                EmailAddressesCollection.Clear();
                adapter.FetchEntityCollection(EmailAddressesCollection, filter, 0, sorter, prefetchPath);
            }

            if (EmailAddressesCollection.Count > 0)
                SelectedEmailAddress = EmailAddressesCollection[0];
        }

BuildFilter Method:

private RelationPredicateBucket BuildFilter()
        {
            var filter = new RelationPredicateBucket();
            bool useAnd = false;

            if (QueryEmailAddress.EmlBusinessUnit != null)
            {
                filter.PredicateExpression.Add(
                    new PredicateExpression(
                        EmlEmailAddressFields.CodeEmlBusinessUnit == 
                        QueryEmailAddress.EmlBusinessUnit.Code));

                useAnd = true;
            }

            if (!string.IsNullOrEmpty(QueryEmailAddress.Description))
            {
                var exp = new FieldLikePredicate(
                    EmlEmailAddressFields.Description, null, string.Format(
                    "%{0}%", QueryEmailAddress.Description.ToUpper()));

                if (useAnd)
                {
                    filter.PredicateExpression.AddWithAnd(exp);
                }
                else
                {
                    filter.PredicateExpression.Add(exp);
                }
            }

            return filter;
        }

Here is the exception information when I execute the ExecuteQuery method as coded above:

Exception: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException Exception Message: An exception was caught during the execution of a retrieval query: ORA-00904: "EMLSVCS"."EML_BUSINESS_UNIT"."DESCRIPTION": invalid identifier. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. StackTrace: at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath) at DCBS.Applications.BulkEmailSystem.ViewModels.EmailSourceListsViewModel.ExecuteQuery() in C:\Repo\Trunk\Applications\DCBS_General\BulkEmailSystem\BulkEmailSystem\ViewModels\EmailSourceListsViewModel.cs:line 202 at DCBS.Applications.BulkEmailSystem.ViewModels.EmailSourceListsViewModel.Command_ExecuteQueryExecuted(Object sender, ExecutedRoutedEventArgs e) in C:\Repo\Trunk\Applications\DCBS_General\BulkEmailSystem\BulkEmailSystem\ViewModels\EmailSourceListsViewModel.cs:line 147 at System.Windows.Input.CommandBinding.OnExecuted(Object sender, ExecutedRoutedEventArgs e) at System.Windows.Input.CommandManager.ExecuteCommandBinding(Object sender, ExecutedRoutedEventArgs e, CommandBinding commandBinding) at System.Windows.Input.CommandManager.FindCommandBinding(CommandBindingCollection commandBindings, Object sender, RoutedEventArgs e, ICommand command, Boolean execute) at System.Windows.Input.CommandManager.FindCommandBinding(Object sender, RoutedEventArgs e, ICommand command, Boolean execute) at System.Windows.Input.CommandManager.OnExecuted(Object sender, ExecutedRoutedEventArgs e) at System.Windows.UIElement.OnExecutedThunk(Object sender, ExecutedRoutedEventArgs e) at System.Windows.Input.ExecutedRoutedEventArgs.InvokeEventHandler(Delegate genericHandler, Object target) at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target) at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs) at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised) at System.Windows.UIElement.RaiseEventImpl(DependencyObject sender, RoutedEventArgs args) at System.Windows.UIElement.RaiseEvent(RoutedEventArgs args, Boolean trusted) at System.Windows.Input.RoutedCommand.ExecuteImpl(Object parameter, IInputElement target, Boolean userInitiated) at System.Windows.Input.RoutedCommand.ExecuteCore(Object parameter, IInputElement target, Boolean userInitiated) at MS.Internal.Commands.CommandHelpers.CriticalExecuteCommandSource(ICommandSource commandSource, Boolean userInitiated) at System.Windows.Controls.Primitives.ButtonBase.OnClick() at System.Windows.Controls.Button.OnClick() at System.Windows.Controls.Primitives.ButtonBase.OnMouseLeftButtonUp(MouseButtonEventArgs e) at System.Windows.UIElement.OnMouseLeftButtonUpThunk(Object sender, MouseButtonEventArgs e) at System.Windows.Input.MouseButtonEventArgs.InvokeEventHandler(Delegate genericHandler, Object genericTarget) at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target) at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs) at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised) at System.Windows.UIElement.ReRaiseEventAs(DependencyObject sender, RoutedEventArgs args, RoutedEvent newEvent) at System.Windows.UIElement.OnMouseUpThunk(Object sender, MouseButtonEventArgs e) at System.Windows.Input.MouseButtonEventArgs.InvokeEventHandler(Delegate genericHandler, Object genericTarget) at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target) at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs) at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised) at System.Windows.UIElement.RaiseEventImpl(DependencyObject sender, RoutedEventArgs args) at System.Windows.UIElement.RaiseTrustedEvent(RoutedEventArgs args) at System.Windows.UIElement.RaiseEvent(RoutedEventArgs args, Boolean trusted) at System.Windows.Input.InputManager.ProcessStagingArea() at System.Windows.Input.InputManager.ProcessInput(InputEventArgs input) at System.Windows.Input.InputProviderSite.ReportInput(InputReport inputReport) at System.Windows.Interop.HwndMouseInputProvider.ReportInput(IntPtr hwnd, InputMode mode, Int32 timestamp, RawMouseActions actions, Int32 x, Int32 y, Int32 wheel) at System.Windows.Interop.HwndMouseInputProvider.FilterMessage(IntPtr hwnd, WindowMessage msg, IntPtr wParam, IntPtr lParam, Boolean& handled) at System.Windows.Interop.HwndSource.InputFilterMessage(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled) at MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled) at MS.Win32.HwndSubclass.DispatcherCallbackOperation(Object o) at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs) at MS.Internal.Threading.ExceptionFilterHelper.TryCatchWhen(Object source, Delegate method, Object args, Int32 numArgs, Delegate catchHandler)

Inner Exception

Exception: Oracle.DataAccess.Client.OracleException Exception Message: ORA-00904: "EMLSVCS"."EML_BUSINESS_UNIT"."DESCRIPTION": invalid identifier StackTrace: at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck) at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) at Oracle.DataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)

You need to add the EmlEmailAddress - EmlBusinessUnit relation in order to sort on the EmlBusinessUnit relates entity. Example:

filter.Relations.Add(EmlEmailAddressEntity.Relations.EmlBusinessUnitEntityUsingBusinessUnitId);

Thank you very much! Exactly what I was looking for.

-Shaun