Specified method is not supported. when using .Count() on group in LINQ

Hi all,

So many posts from me lately! Sorry about that, no worries I enjoy Lightspeed a lot and realize these are harder corner cases. Still I want to report them.

Linq query:

            var groupedByExpats = from a in assignmentYears
                                  group a by a.Assignment.Employee into employeeAssignments
                                  where employeeAssignments.Count() > 0
                                  select new { employeeAssignments.Key, AssignmentYears = employeeAssignments };

Note that assignmentYears is already a LINQ statement, with a where clause.

Exception:

[NotSupportedException: Specified method is not supported.]
Mindscape.LightSpeed.Linq.Sqo.CountBase.ApplyCriteria(LinqQueryPlanExpression plan, QueryExpression applyTo, Expression[] arguments) +206
Mindscape.LightSpeed.Linq.Sqo.ScalarCriteriaSqo.GetCriteria(LinqQueryPlanExpression plan, IEnumerable1 arguments) +155
Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractMethodCriteria(LinqQueryPlanExpression plan, MethodCallExpression expression) +277
Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ConvertToQueryExpression(LinqQueryPlanExpression plan, Expression expression) +883
Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractBinaryCriteria(LinqQueryPlanExpression plan, BinaryExpression expression) +106
Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(LinqQueryPlanExpression plan, Expression expression) +585
Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(LinqQueryPlanExpression plan, Expression expression) +96
Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(LinqQueryPlanExpression plan, Expression expression) +285
Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(LinqQueryPlanExpression plan, Expression expression) +96
Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(LinqQueryPlanExpression plan, Expression expression) +518
Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(LinqQueryPlanExpression plan, Expression expression) +96
Mindscape.LightSpeed.Linq.Sqo.Where.ExtractWhereCriteria(MethodCallExpression expression, LinqQueryPlanExpression plan, GroupResultsPlan groupPlan) +120 Mindscape.LightSpeed.Linq.Sqo.Where.Evaluate(ExpressionVisitor visitor, MethodCallExpression expression) +1789
Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCallExpression exp) +1482
Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit(Expression exp) +132 Mindscape.LightSpeed.Linq.Sqo.Select.Evaluate(ExpressionVisitor visitor, MethodCallExpression expression) +547
Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCallExpression exp) +1376
Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit(Expression exp) +132 Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Build(Expression translation, LinqQueryProvider provider) +64
Mindscape.LightSpeed.Linq.LinqQueryProvider.GetExecutionPlan(Expression expression) +242
Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression) +121
Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression) +37
Mindscape.LightSpeed.Linq.LinqQuery1.GetEnumerator() +40

Extra information:

What does work, is leaving out the Count on the group, I suspect that the method is simply not implemented as supported for translation to SQL. I think it should add a HAVING statement to the SQL query:

SELECT g1.EmployeeId FROM ( SELECT [Assignments].[EmployeeId] FROM [AssignmentYears] [AssignmentYears] INNER JOIN [Assignments] ON [AssignmentYears].[AssignmentId] = [Assignments].[Id] WHERE [AssignmentYears].[Year] IN (2012) GROUP BY [Assignments].[EmployeeId] HAVING COUNT(*) > 0 ) [g1]

When I remove the where on the group (employeeAssignments) it will work just fine, although not filtering out empty groups. The SQL query generated is the same as the one above, with the HAVING excluded.

Thanks again!

Cheers, Dennis

PS. I am back to 4.0 release version, instead of nighties!

Hi Dennis,

Yes, this is not currently supported. We do have a reasonably old backlog item to look at implementing this (it requires associated support in the core querying engine) which I will dust off and have a look at over the next few days or so. Ill post back once I have had a look into this and let you know if we can get this added in.

Thanks!
Jeremy

Just an update to let you know that I have added in support for specifying Having clauses against the Grouping objects on the core querying and also added in some support for translating this within the LINQ provider. This is now available in the nightly builds so let me know how you get on.

Jeremy