SortExpression with DerivedTableDefinition and DynamicRelation

Posts   
 
    
AK
User
Posts: 5
Joined: 27-Jan-2016
# Posted on: 14-Feb-2017 17:23:26   

Hi, We have llblgen pro library (ver 4.2) and we are using Template group: Adapter.

I have a problem with sorting when using DerivedTableDefinition and DynamicRelation (select very similar to https://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/gencode_derivedtabledynamicrelation.htm)

Below an explanation. Thanks in advance for help!

I'm using:

SortExpression orderBy = new SortExpression(); orderBy.Add(Contacts1Fields.Email | SortOperator.Ascending);

But I get an Exception: _**An exception was caught during the execution of a retrieval query: The multi-part identifier "sample.contacts_1.email" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

InnerException: The multi-part identifier "sample.contacts_1.email" could not be bound.**_

My code:

            ResultsetFields dtFields = new ResultsetFields(1);
            dtFields.DefineField(ContactsFields1Fields.ContactId, 0);


            IPredicateExpression finalPredicateExpression = new PredicateExpression();

            IPredicateExpression predicateExpression1 = new PredicateExpression();
            predicateExpression1.Add(ContactsFields1Fields.FieldName == "s1");
            predicateExpression1.AddWithAnd(ContactsFields1Fields.StringValue == "biały");

            IPredicateExpression predicateExpression2 = new PredicateExpression();
            predicateExpression2.Add(ContactsFields1Fields.FieldName == "s2");
            predicateExpression2.AddWithAnd(ContactsFields1Fields.StringValue == "hipopotam");

            finalPredicateExpression.Add(predicateExpression1);
            finalPredicateExpression.AddWithOr(predicateExpression2);


            GroupByCollection groupBy = new GroupByCollection();
            groupBy.Add(ContactsFields1Fields.ContactId);
            groupBy.HavingClause = new PredicateExpression((ContactsFields1Fields.ContactId.SetAggregateFunction(AggregateFunction.Count) == 2));
            

            DerivedTableDefinition dtDefinition = new DerivedTableDefinition(dtFields, "c", finalPredicateExpression, groupBy);


            // specify the relation which is a dynamic relation. 
            DynamicRelation relation = new DynamicRelation(dtDefinition, JoinHint.Inner, EntityType.Contacts1Entity, "o",
                                           (new EntityField2(ContactsFields1FieldIndex.ContactId.ToString(), "c", typeof(int)) == Contacts1Fields.Id.SetObjectAlias("o")));

            RelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.Relations.Add(relation);
            bucket.SelectListAlias = "o";  // entities should target the aliased order table. 

            SortExpression orderBy = new SortExpression();
            orderBy.Add(Contacts1Fields.Email | SortOperator.Ascending);
            
            // fetch the data
            EntityCollection<Contacts1Entity> contacts = new EntityCollection<Contacts1Entity>();

            try
            {               
                m_adapter.FetchEntityCollection(contacts, bucket, 0, orderBy, 2, 10);
            }
            catch (Exception exc)
            {
                //An exception was caught during the execution of a retrieval query: The multi-part identifier "sample.contacts_1.email" could not be bound.. 
                //Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
                //The multi-part identifier "sample.contacts_1.email" could not be bound.
            }

With Sql Server Profiler I logged a raw SQL query under this fetch:

exec sp_executesql N'WITH __actualSet AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS __rowcnt FROM (SELECT DISTINCT TOP 20 [LPA_o2].[id] AS [Id], [LPA_o2].[external_id] AS [ExternalId], [LPA_o2].[create_time] AS [CreateTime], [LPA_o2].[email] AS [Email], [LPA_o2].[mobile_phone] AS [MobilePhone], [LPA_o2].[home_phone] AS [HomePhone], [LPA_o2].[fax] AS [Fax], [LPA_o2].[first_name] AS [FirstName], [LPA_o2].[last_name] AS [LastName], [LPA_o2].[company_name] AS [CompanyName], [LPA_o2].[in_archive] AS [InArchive], [LPA_o2].[archive_change_time] AS [ArchiveChangeTime], [LPA_o2].[archive_reason] AS [ArchiveReason] FROM ( (SELECT [sample].[contacts_fields_1].[contact_id] AS [ContactId] FROM [sample].[contacts_fields_1] WHERE ( ( [sample].[contacts_fields_1].[field_name] = @p1 AND [sample].[contacts_fields_1].[string_value] = @p2) OR ( [sample].[contacts_fields_1].[field_name] = @p3 AND [sample].[contacts_fields_1].[string_value] = @p4)) GROUP BY [sample].[contacts_fields_1].[contact_id] HAVING ( COUNT([sample].[contacts_fields_1].[contact_id]) = @p6)) [LPA_c1] INNER JOIN [sample].[contacts_1] [LPA_o2] ON [LPA_c1].[ContactId] = [LPA_o2].[id])) AS _tmpSet) SELECT [Id], [ExternalId], [CreateTime], [Email], [MobilePhone], [HomePhone], [Fax], [FirstName], [LastName], [CompanyName], [InArchive], [ArchiveChangeTime], [ArchiveReason] FROM __actualSet WHERE [__rowcnt] > @p7 AND [__rowcnt] <= @p8 ORDER BY [__rowcnt] ASC',N'@p1 nvarchar(50),@p2 nvarchar(450),@p3 nvarchar(50),@p4 nvarchar(450),@p6 int,@p7 int,@p8 int',@p1=N's1',@p2=N'biały',@p3=N's2',@p4=N'hipopotam',@p6=2,@p7=10,@p8=20

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 14-Feb-2017 17:46:27   

As you are using aliases, you need to set the object(entity) alias for the Sort Field as well. Or use the field on the ResultSet in the sort method, because it already has the alias set.