- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
SortExpression with DerivedTableDefinition and DynamicRelation
Joined: 27-Jan-2016
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