Hello,
I'm using dynamic lists based on Entities in Selfserving mode, and i don't know why I have pb with the sql generated.
This is my c# code :
ResultsetFields _fields = new ResultsetFields(4);
_fields.DefineField(LearnerFieldIndex.LearnerId, 0, "LearnerId", "Learner");
_fields.DefineField(LearnerFieldIndex.Firstname, 1, "Firstname", "Learner");
_fields.DefineField(LearnerFieldIndex.Lastname, 2, "Lastname", "Learner");
_fields.DefineField(LessonsFieldIndex.LessonId, 3, "LessonCount", "Lessons");
_fields[3].AggregateFunctionToApply = AggregateFunction.Sum;
IRelationCollection _relations = new RelationCollection();
_relations.Add(LessonsEntity.Relations.LearnerEntityUsingLearnerId, "Learner", "Lessons", JoinHint.None);
IPredicateExpression _selectFilter = new PredicateExpression();
_selectFilter.Add(PredicateFactory.CompareValue(LearnerFieldIndex.LearnerId, ComparisonOperator.LessEqual, _SELECT3COUNT));
IGroupByCollection _groupByClause = new GroupByCollection();
_groupByClause.Add(_fields[0]);
_groupByClause.Add(_fields[1]);
_groupByClause.Add(_fields[2]);
System.Data.DataTable dt = new System.Data.DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(_fields, dt, 0, null, _selectFilter, _relations, true, _groupByClause, null, 0, 0);
And now the sql code generated in the output on debug :
SELECT
[Learner].[LearnerId],
[Learner].[Firstname],
[Learner].[Lastname],
COUNT([Lessons].[LessonId]) AS [LessonCount]
FROM ( [dbo].[Learner] [LPA_L2] INNER JOIN [dbo].[Lessons] [LPA_L1] ON [LPA_L2].[LearnerId]=[LPA_L1].[LearnerId])
WHERE ( ( [dbo].[Learner].[LearnerId] <= @LearnerId1))
GROUP BY
[Learner].[LearnerId],
[Learner].[Firstname],
[Learner].[Lastname]
In fact, there's a problem between the aliases used in the tables.
Where the aliases [LPA_L2] and [LPA_L1] come from ?
Have I forgotten to set something in the designer part ?
Or may be a bad c# ?
Can someone help me ?
best regards
mika