Hey folks,
I'm trying to group by the following field:
fields.DefineField(new EntityField2("Name", (UserFields.FirstName + " " + UserFields.Surname)), 0);
...
groupBy.Add(fields[0]);
But I get an error telling me firstname and surname can't be in the select list as they are not part of group by blah blah.
Now i'm pretty sure SQL supports grouping by an expression...
Looking at the generated SQL:
exec sp_executesql N'SELECT DISTINCT ([RedAnts].[dbo].[User].[FirstName] + @LO255914b1) + [RedAnts].[dbo].[User].[Surname] AS [Name], COUNT([RedAnts].[dbo].[Job].[JobId]) AS [JobId] FROM (((( [RedAnts].[dbo].[Team] INNER JOIN [RedAnts].[dbo].[Job] ON
[RedAnts].[dbo].[Team].[TeamId]=[RedAnts].[dbo].[Job].[TeamId]) INNER JOIN [RedAnts].[dbo].[JobAssignment] ON [RedAnts].[dbo].[Job].[JobId]=[RedAnts].[dbo].[JobAssignment].[JobId]) INNER JOIN [RedAnts].[dbo].[TeamMember] ON
[RedAnts].[dbo].[TeamMember].[TeamMemberId]=[RedAnts].[dbo].[JobAssignment].[TeamMemberId]) INNER JOIN [RedAnts].[dbo].[User] ON [RedAnts].[dbo].[User].[UserId]=[RedAnts].[dbo].[TeamMember].[UserId]) WHERE ( ( [RedAnts].[dbo].[Job].[BookedStartDate] >=
@BookedStartDate2 AND [RedAnts].[dbo].[Team].[TeamLeaderUserId] = @TeamLeaderUserId3)) GROUP BY ([RedAnts].[dbo].[User].[FirstName] + @LO255914b4) + [RedAnts].[dbo].[User].[Surname]',N'@LO255914b1 nvarchar(1),@BookedStartDate2 smalldatetime,@TeamLeaderUserId3
int,@LO255914b4 nvarchar(1)',@LO255914b1=N' ',@BookedStartDate2=''2008-03-05 00:00:00:000'',@TeamLeaderUserId3=27,@LO255914b4=N' '
I can see that my space char is put in a variable.. for some reason it gets put into two variables, one is used in the select list and the other in the group by.
I don't know how SQL decides if a select expression matches a group by expression but my guess is even though the two variables have the same value, the fact that they are two different variables is causing problems..
Any ideas?
Also, it'd be awesome if we could define read only fields derived from an expression for an entity in the designer. Creating a Name property that concatenates FirstName and Surname is a common task on all my projects.
The work around i have at the moment is to include the user id in the select list and group by that then firstname, surname:
ResultsetFields fields = new ResultsetFields(5);
fields.DefineField(UserFields.UserId, 0);
fields.DefineField(new EntityField2("Name", (UserFields.FirstName + " " + UserFields.Surname)), 1);
fields.DefineField(UserFields.FirstName, 2);
fields.DefineField(UserFields.Surname, 3);
fields.DefineField(JobFields.JobId, 4, AggregateFunction.CountDistinct);
...
IGroupByCollection groupBy = new GroupByCollection();
groupBy.Add(fields[0]);
groupBy.Add(fields[2]);
groupBy.Add(fields[3]);
