Hello all,

I have a question about sorting in related table and have not has any solution yet. I'm appreciated your helps.

This is the DB relation and I would like to sort on the field "Name" of table "BUIDHierarchy(3)"

AggregationGroup----->BUIDHierarchy (1) ----->BUIDParent----->BUIDHierarchy(2)----->BUIParent----->BUIDHierarchy(3)

This is the prefetch path:

IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.AggregationGroupEntity);
IPrefetchPathElement2 buidHierarchyPath = prefetchPath.Add(AggregationGroupEntity.PrefetchPathBUIDHierarchy);
IPrefetchPathElement2 buidCRParentPath = buidHierarchyPath.SubPath.Add(BUIDHierarchyEntity.PrefetchPathBUIDParent);
IPrefetchPathElement2 buidCRPath  = buidCRParentPath.SubPath.Add(BUIDParentEntity.PrefetchPathBUIDHierarchy_);

IPrefetchPathElement2 buidDCRParentPath = buidCRPath.SubPath.Add(BUIDHierarchyEntity.PrefetchPathBUIDParent);
buidDCRParentPath.SubPath.Add(BUIDParentEntity.PrefetchPathBUIDHierarchy_);

I had problem on the add relation. I don't know how to add the relation between BUIDHierarchy(2)----->BUIParent----->BUIDHierarchy(3)

This is my code but it is not correct

               bucket.Relations.Add(AggregationGroupEntity.Relations.BUIDHierarchyEntityUsingBuidhierarchyId);
             bucket.Relations.Add(AggregationGroupEntity.Relations.AggregationCustomerEntityUsingAggregationGroupId, JoinHint.Left); 
             bucket.Relations.Add(AggregationCustomerEntity.Relations.CustomerEntityUsingCustomerId, JoinHint.Left);  
           bucket.Relations.Add(BUIDHierarchyEntity.Relations.BUIDParentEntityUsingBUIDHierarchyID, JoinHint.Left);           bucket.Relations.Add(BUIDParentEntity.Relations.BUIDHierarchyEntityUsingParentBUIDHierarchyID, "BUIDHierarchyCreaditRiskManager", JoinHint.Left);

6: bucket.Relations.Add(BUIDHierarchyEntity.Relations.BUIDParentEntityUsingBUIDHierarchyID, JoinHint.Left);

7: bucket.Relations.Add(BUIDParentEntity.Relations.BUIDHierarchyEntityUsingParentBUIDHierarchyID, "BUIDHierarchyDistrictCreaditRiskManager", JoinHint.Left);

The code in line 6 & 7 is wrong because I understand this code has the relation with AggregationGroup, not has the relation with "BUIDHierarchyCreaditRiskManager".

I would like to write the relation between "BUIDHierarchyDistrictCreaditRiskManager" and "BUIDHierarchyCreaditRiskManager". "BUIDHierarchyDistrictCreaditRiskManager" is the parent of "BUIDHierarchyCreaditRiskManager"

I'm very appreciated all of your helps.

I use LLBLGen Pro Version 2.5 Final and SQL Server 2005

Hi ttran,

The first code snippet with PrefetchPaths is not relevant if you just want to sort on the final node. You just need relations. Now, to add such relations you need to work with start and final aliasses so you can know to which exactly node you are creating the relation. Try this:

// AggregationGroup----->BUIDHierarchy (1)
bucket.Relations.Add(AggregationGroupEntity.Relations.BUIDHierarchyEntityUsingBuidhierarchyId, "AG", "BH1", JoinHint.Inner);

// BUIDHierarchy (1) ----->BUIDParent
bucket.Relations.Add(BUIDHierarchyEntity.Relations.BUIDParentEntityUsingBUIDHierarchyID, "BH1", "P1", JoinHint.Left);           

// BUIDParent----->BUIDHierarchy(2)
bucket.Relations.Add(BUIDParentEntity.Relations.BUIDHierarchyEntityUsingParentBUIDHierarchyID, "P1", "BH2" JoinHint.Left);

UIDHierarchy(2)----->BUIParent
bucket.Relations.Add(BUIDHierarchyEntity.Relations.BUIDParentEntityUsingBUIDHierarchyID, "BH2", "P2", JoinHint.Left);

// BUIParent----->BUIDHierarchy(3)
bucket.Relations.Add(BUIDParentEntity.Relations.BUIDHierarchyEntityUsingParentBUIDHierarchyID, "P2", "BH3" JoinHint.Left);

Then, when sorting you need to specify the final 'BH3' alias...

//...
SortExpression sorter = new SortExpression();
sorter.Add(BUIDHierarchyFields.Name.SetObjectAlias("BH3") | SortOperator.Ascending);
// ...

Thanks Daelmo very much. You answer is very helpful for me.

And I have another question. After that

totalAg = adapter.GetDbCount(new AggregationGroupEntityFactory().CreateFields(), bucket, null, false);
adapter.FetchEntityCollection(AgList, bucket, 0, sorter, prefetchPath, pageNumber, pageSize);

It show the error "The multi-part AggregationGroup... identifier could not be bound"

I found that the SELECT does not has alias

The SQL when I run the script is:

SELECT DISTINCT [ePropertyDev].[dbo].[AggregationGroup].[AggregationGroupID] AS [AggregationGroupId],

FROM [ePropertyDev].[dbo].[BUIDHierarchy] [LPA_R1]  
INNER JOIN [ePropertyDev].[dbo].[AggregationGroup] [LPA_A2]...

But the correct one must be

SELECT DISTINCT [LPA_A2].[AggregationGroupID] AS [AggregationGroupId],

FROM [ePropertyDev].[dbo].[BUIDHierarchy] [LPA_R1]  
INNER JOIN [ePropertyDev].[dbo].[AggregationGroup] [LPA_A2]

Please help me resolve it. Thanks

Of course. Please post your final code snippet, the full exception stack trace and description, and the full generated SQL.

Hi Daelmo,

This is the exception: An exception was caught during the execution of a retrieval query: The multi-part identifier "ePropertyDev.dbo.AggregationGroup.IsTemporaryUsed" could not be bound. The multi-part identifier "ePropertyDev.dbo.AggregationGroup.AggregationGroupNumber" could not be bound. The multi-part identifier "ePropertyDev.dbo.BUIDHierarchy.IsDeleted" could not be bound. The multi-part identifier "ePropertyDev.dbo.AggregationGroup.AggregationGroupID" could not be bound. The multi-part identifier "ePropertyDev.dbo.AggregationGroup.AggregationGroupName" could not be bound. The multi-part identifier "ePropertyDev.dbo.AggregationGroup.AggregationGroupNumber" could not be bound. The multi-part identifier "ePropertyDev.dbo.AggregationGroup.BUIDHierarchyID" could not be bound. The multi-part identifier "ePropertyDev.dbo.AggregationGroup.IsTemporaryUsed" could not be bound. The multi-part identifier "ePropertyDev.dbo.AggregationGroup.Active" could not be bound. The multi-part identifier "ePropertyDev.dbo.AggregationGroup.CreatedDate" could not be bound. The multi-part identifier "ePropertyDev.dbo.AggregationGroup.UpdatedDate" could not be bound.. Check InnerException, QueryEx

This is the generate SQL:

exec sp_executesql N'SELECT COUNT(*) AS NumberOfRows FROM (SELECT DISTINCT [ePropertyDev].[dbo].[AggregationGroup].[AggregationGroupID] AS [AggregationGroupId], [ePropertyDev].[dbo].[AggregationGroup].[AggregationGroupName], [ePropertyDev].[dbo].[AggregationGroup].[AggregationGroupNumber], [ePropertyDev].[dbo].[AggregationGroup].[BUIDHierarchyID] AS [BuidhierarchyId], [ePropertyDev].[dbo].[AggregationGroup].[IsTemporaryUsed], [ePropertyDev].[dbo].[AggregationGroup].[Active], [ePropertyDev].[dbo].[AggregationGroup].[CreatedDate], [ePropertyDev].[dbo].[AggregationGroup].[UpdatedDate] FROM ((((( [ePropertyDev].[dbo].[BUIDHierarchy] [LPA_R1]  INNER JOIN [ePropertyDev].[dbo].[AggregationGroup] [LPA_A2]  ON  [LPA_R1].[BUIDHierarchyID]=[LPA_A2].[BUIDHierarchyID]) LEFT JOIN [ePropertyDev].[dbo].[BUIDParent] [LPA_R3]  ON  [LPA_R1].[BUIDHierarchyID]=[LPA_R3].[BUIDHierarchyID]) LEFT JOIN [ePropertyDev].[dbo].[BUIDHierarchy] [LPA_C4]  ON  [LPA_C4].[BUIDHierarchyID]=[LPA_R3].[ParentBUIDHierarchyID]) LEFT JOIN [ePropertyDev].[dbo].[BUIDParent] [LPA_C5]  ON  [LPA_C4].[BUIDHierarchyID]=[LPA_C5].[BUIDHierarchyID]) LEFT JOIN [ePropertyDev].[dbo].[BUIDHierarchy] [LPA_D6]  ON  [LPA_D6].[BUIDHierarchyID]=[LPA_C5].[ParentBUIDHierarchyID]) WHERE ( ( ( [ePropertyDev].[dbo].[AggregationGroup].[IsTemporaryUsed] = @IsTemporaryUsed1 AND [ePropertyDev].[dbo].[AggregationGroup].[AggregationGroupNumber] LIKE @AggregationGroupNumber2)) AND [ePropertyDev].[dbo].[BUIDHierarchy].[IsDeleted] = @IsDeleted3 AND [LPA_A2].[Active] = @Active4 AND [LPA_A2].[IsTemporaryUsed] = @IsTemporaryUsed5)) TmpResult',N'@IsTemporaryUsed1 bit,@AggregationGroupNumber2 nvarchar(3),@IsDeleted3 bit,@Active4 bit,@IsTemporaryUsed5 bit',@IsTemporaryUsed1=0,@AggregationGroupNumber2=N'%5%',@IsDeleted3=0,@Active4=1,@IsTemporaryUsed5=0

This is the code:

        private static SearchResults GetAGSearchResult(
            int pageNumber, int pageSize,
            RelationPredicateBucket bucket, ISortExpression sorter,
            IPrefetchPath2 prefetchPath)
        {
            EntityCollection AgList = new EntityCollection(new AggregationGroupEntityFactory());
            int totalAg = 0;

            try
            {
                using (DataAccessAdapter adapter = new DataAccessAdapter())
                {
                    totalAg = adapter.GetDbCount(new AggregationGroupEntityFactory().CreateFields(), bucket, null, false);
                    adapter.FetchEntityCollection(AgList, bucket, 0, sorter, prefetchPath, pageNumber, pageSize);
                }
            }
            catch (Exception ex)
            {
                LoggingUtils.WriteLog(ex.Message, ex.StackTrace, LoggingParameter.Priority.High, LoggingParameter.Severity.Error, LoggingParameter.Category.Debug);
                throw ex;
            }

            SearchResults result = new SearchResults();
            result.TotalEntity = totalAg;
            result.EntityCollection = AgList;
            return result;
        }

I meet the problem when put the alias on

                totalAg = adapter.GetDbCount(new AggregationGroupEntityFactory().CreateFields(), bucket, null, false);
                adapter.FetchEntityCollection(AgList, bucket, 0, sorter, prefetchPath, pageNumber, pageSize);

Please help me.

Hi Daelmo,

I know the way to resolve the question before. But now I have another problem that it can not paging on the related field. When I debug, the query to retrieve data when sorting the related column always return all of record(~ 3000)-> it has time out problem.

When retrieve data in the column of table selected, it works well

adapter.FetchEntityCollection(customerList, bucket, 0, sorter, prefetchPath, pageNumber, pageSize);

In the above code, it works well when I sort on the column of customer table. But when I sort on the BuidHierarchy, it always return all the records in the DB

Customer -> AggregationGroupCustomer -> AggregationGroup -> BUIDHierarchy

Below is the SQL Script: When I sort on the Customer name, it only select 25 items

exec sp_executesql N'SELECT DISTINCT TOP 25 [ePropertyDev].[dbo].[Customer].[CustomerID] AS [CustomerId], [ePropertyDev].[dbo].[Customer].[CustomerName], [ePropertyDev].[dbo].[Customer].[CustomerNumber], [ePropertyDev].[dbo].[Customer].[CRS] AS [Crs], [ePropertyDev].[dbo].[Customer].[Type], 
[ePropertyDev].[dbo].[Customer].[Active], [ePropertyDev].[dbo].[Customer].[CreatedDate], [ePropertyDev].[dbo].[Customer].[UpdatedDate], [ePropertyDev].[dbo].[Customer].[BorrowingEntityType] FROM ((((((( [ePropertyDev].[dbo].[Customer]  INNER JOIN [ePropertyDev].[dbo].[AggregationCustomer]  ON  [ePropertyDev].[dbo].[Customer].[CustomerID]=[ePropertyDev].[dbo].[AggregationCustomer].[CustomerID]) INNER JOIN [ePropertyDev].[dbo].[AggregationGroup]  ON  [ePropertyDev].[dbo].[AggregationGroup].[AggregationGroupID]=[ePropertyDev].[dbo].[AggregationCustomer].[AggregationGroupID] AND ( ( [ePropertyDev].[dbo].[AggregationGroup].[Active] = @Active1))) INNER JOIN [ePropertyDev].[dbo].[BUIDHierarchy] [LPA_R1]  ON  [LPA_R1].[BUIDHierarchyID]=[ePropertyDev].[dbo].[AggregationGroup].[BUIDHierarchyID]) LEFT JOIN [ePropertyDev].[dbo].[BUIDParent] [LPA_R2]  ON  [LPA_R1].[BUIDHierarchyID]=[LPA_R2].[BUIDHierarchyID]) LEFT JOIN [ePropertyDev].[dbo].[BUIDHierarchy] [LPA_C3]  ON  [LPA_C3].[BUIDHierarchyID]=[LPA_R2].[ParentBUIDHierarchyID]) LEFT JOIN [ePropertyDev].[dbo].[BUIDParent] [LPA_C4]  ON  [LPA_C3].[BUIDHierarchyID]=[LPA_C4].[BUIDHierarchyID]) LEFT JOIN [ePropertyDev].[dbo].[BUIDHierarchy] [LPA_D5]  ON  [LPA_D5].[BUIDHierarchyID]=[LPA_C4].[ParentBUIDHierarchyID]) WHERE ( ( ( ( ( [ePropertyDev].[dbo].[AggregationGroup].[IsTemporaryUsed] = @IsTemporaryUsed2 OR [ePropertyDev].[dbo].[Customer].[Active] = @Active3) AND [ePropertyDev].[dbo].[Customer].[CustomerNumber] LIKE @CustomerNumber4)) AND [LPA_R1].[IsDeleted] = @IsDeleted5 AND ( [ePropertyDev].[dbo].[AggregationGroup].[IsTemporaryUsed] = @IsTemporaryUsed6 OR [ePropertyDev].[dbo].[Customer].[Active] = @Active7))) ORDER BY [ePropertyDev].[dbo].[Customer].[CustomerName] ASC',N'@Active1 bit,@IsTemporaryUsed2 bit,@Active3 bit,@CustomerNumber4 nvarchar(3),@IsDeleted5 bit,@IsTemporaryUsed6 bit,@Active7 bit',@Active1=1,@IsTemporaryUsed2=0,@Active3=1,@CustomerNumber4=N'%5%',@IsDeleted5=0,@IsTemporaryUsed6=0,@Active7=1

But when I sort on the BUIDHierarchy, it return over 1000 records

exec sp_executesql N'SELECT [ePropertyDev].[dbo].[Customer].[CustomerID] AS [CustomerId], [ePropertyDev].[dbo].[Customer].[CustomerName], [ePropertyDev].[dbo].[Customer].[CustomerNumber], [ePropertyDev].[dbo].[Customer].[CRS] AS [Crs], [ePropertyDev].[dbo].[Customer].[Type], 
[ePropertyDev].[dbo].[Customer].[Active], [ePropertyDev].[dbo].[Customer].[CreatedDate], [ePropertyDev].[dbo].[Customer].[UpdatedDate], [ePropertyDev].[dbo].[Customer].[BorrowingEntityType] FROM ((((((( [ePropertyDev].[dbo].[Customer]  INNER JOIN [ePropertyDev].[dbo].[AggregationCustomer]  ON  [ePropertyDev].[dbo].[Customer].[CustomerID]=[ePropertyDev].[dbo].[AggregationCustomer].[CustomerID]) INNER JOIN [ePropertyDev].[dbo].[AggregationGroup]  ON  [ePropertyDev].[dbo].[AggregationGroup].[AggregationGroupID]=[ePropertyDev].[dbo].[AggregationCustomer].[AggregationGroupID] AND ( ( [ePropertyDev].[dbo].[AggregationGroup].[Active] = @Active1))) INNER JOIN [ePropertyDev].[dbo].[BUIDHierarchy] [LPA_R1]  ON  [LPA_R1].[BUIDHierarchyID]=[ePropertyDev].[dbo].[AggregationGroup].[BUIDHierarchyID]) LEFT JOIN [ePropertyDev].[dbo].[BUIDParent] [LPA_R2]  ON  [LPA_R1].[BUIDHierarchyID]=[LPA_R2].[BUIDHierarchyID]) LEFT JOIN [ePropertyDev].[dbo].[BUIDHierarchy] [LPA_C3]  ON  [LPA_C3].[BUIDHierarchyID]=[LPA_R2].[ParentBUIDHierarchyID]) LEFT JOIN [ePropertyDev].[dbo].[BUIDParent] [LPA_C4]  ON  [LPA_C3].[BUIDHierarchyID]=[LPA_C4].[BUIDHierarchyID]) LEFT JOIN [ePropertyDev].[dbo].[BUIDHierarchy] [LPA_D5]  ON  [LPA_D5].[BUIDHierarchyID]=[LPA_C4].[ParentBUIDHierarchyID]) WHERE ( ( ( ( ( [ePropertyDev].[dbo].[AggregationGroup].[IsTemporaryUsed] = @IsTemporaryUsed2 OR [ePropertyDev].[dbo].[Customer].[Active] = @Active3) AND [ePropertyDev].[dbo].[Customer].[CustomerNumber] LIKE @CustomerNumber4)) AND [LPA_R1].[IsDeleted] = @IsDeleted5 AND ( [ePropertyDev].[dbo].[AggregationGroup].[IsTemporaryUsed] = @IsTemporaryUsed6 OR [ePropertyDev].[dbo].[Customer].[Active] = @Active7))) ORDER BY [LPA_R1].[Name] ASC',N'@Active1 bit,@IsTemporaryUsed2 bit,@Active3 bit,@CustomerNumber4 nvarchar(3),@IsDeleted5 bit,@IsTemporaryUsed6 bit,@Active7 bit',@Active1=1,@IsTemporaryUsed2=0,@Active3=1,@CustomerNumber4=N'%5%',@IsDeleted5=0,@IsTemporaryUsed6=0,@Active7=1

This is the relation

                bucket.Relations.Add(CustomerEntity.Relations.AggregationCustomerEntityUsingCustomerId);

                IEntityRelation customerAGRelation = bucket.Relations.Add(AggregationCustomerEntity.Relations.AggregationGroupEntityUsingAggregationGroupId);
                customerAGRelation.CustomFilter = customFilter;

                bucket.Relations.Add(AggregationGroupEntity.Relations.BUIDHierarchyEntityUsingBuidhierarchyId, string.Empty, TABLE_ALIAS_RELATIONSHIP_MANAGER, JoinHint.Inner);
                bucket.Relations.Add(BUIDHierarchyEntity.Relations.BUIDParentEntityUsingBUIDHierarchyID, TABLE_ALIAS_RELATIONSHIP_MANAGER, TABLE_ALIAS_RELATIONSHIP_MANAGER_PARENT, JoinHint.Left);
                bucket.Relations.Add(BUIDParentEntity.Relations.BUIDHierarchyEntityUsingParentBUIDHierarchyID, TABLE_ALIAS_RELATIONSHIP_MANAGER_PARENT, TABLE_ALIAS_CREDIT_RISK_MANAGER, JoinHint.Left);
                bucket.Relations.Add(BUIDHierarchyEntity.Relations.BUIDParentEntityUsingBUIDHierarchyID, TABLE_ALIAS_CREDIT_RISK_MANAGER, TABLE_ALIAS_CREDIT_RISK_MANAGER_PARENT, JoinHint.Left);
                bucket.Relations.Add(BUIDParentEntity.Relations.BUIDHierarchyEntityUsingParentBUIDHierarchyID, TABLE_ALIAS_CREDIT_RISK_MANAGER_PARENT, TABLE_ALIAS_DISTRICT_CREDIT_RISK_MANAGER, JoinHint.Left);

This is the sort

sorter.Add(BUIDHierarchyFields.Name.SetObjectAlias(TABLE_ALIAS_RELATIONSHIP_MANAGER) | sortOperator);

Please help me

Although the SQL query returns over 1000 records, do you still end up with the correct page size in the client side code? There are times when paging has to be done on the client rather than in the SQL query - I'm not sure there is an easy way round this.

Matt

Hello,

The problem in here is the time-out exception issue. It happens when retrieving 10000 records

So, I think I need to write the relation correctly

Thanks

It would certainly be worth check how your database is indexed as well. Have you looked at the query execution plan to see where the slow performance is coming from ?

Matt

How do you know it returns all rows from the DB? The query might not page, but that doesn't mean it results in all rows from the db: it will page on the client.

It has to do that, as it can't create a set of unique rows due to the sort on a field not in the select list. This sort clause makes it impossible to emit 'DISTINCT' into the output, as it has to do so as it can't know whether the rows returned are unique or not. If it can't know whether rows are unique or not, the paging can't happen on the DB side, because that would mean you might get the wrong page.

The workaround is not to sort on a field in a related element, which isn't in the resultset.

Also, make sure your temp db is big enough and not very small / fragmented.