This is a migrated thread and some comments may be shown as answers.

GroupBy with Conditional Count

1 Answer 489 Views
LINQ (LINQ specific questions)
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Oleg
Top achievements
Rank 1
Oleg asked on 28 Sep 2009, 08:22 AM
"GroupBy with Conditional Count" doesn't work.
We can see it in LINQ Project Sample Query Explorer:

public object LinqToOpenAccessGroupBy07()
{
    var q = from p in scope.Extent<Product>()
            group p by p.Category.CategoryID into g
            select new
            {
                g.Key,
                NumProducts = g.Count(p => p.Discontinued)
            };

    return q.ToList();
}

1: SELECT b.[CategoryID] AS COL1, COUNT(a.[Discontinued]) AS  EXPR2 FROM [Products] a LEFT JOIN [Categories] AS b ON (a.[CategoryID] = b.[CategoryID]) GROUP BY b.[CategoryID]

p => p.Discontinued condition transforms simply to a.[Discontinued] inside COUNT function in output query. Furthermore, if we have more complicate condition, f.e. dummy p => p.Discontinued && p.Discontinued, the output sql would contain invalid clause COUNT(a.[Discontinued] AND a.[Discontinued])

Does exist any workarround?

1 Answer, 1 is accepted

Sort by
0
Alexander
Telerik team
answered on 29 Sep 2009, 01:04 PM
Hello Oleg Voinov,

I am afraid that there is no workaround except doing the grouping in memory. In this case the code would look like this:
public object LinqToOpenAccessGroupBy07() 
    var q = from p in scope.Extent<Product>().ToList() 
            group p by p.Category.CategoryID into g 
            select new 
            { 
                g.Key, 
                NumProducts = g.Count(p => p.Discontinued) 
            }; 
 
    return q.ToList(); 

Best wishes,
Alexander
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
Tags
LINQ (LINQ specific questions)
Asked by
Oleg
Top achievements
Rank 1
Answers by
Alexander
Telerik team
Share this question
or