GroupBy with Conditional Count

2 posts, 0 answers
  1. Oleg
    Oleg avatar
    8 posts
    Member since:
    Sep 2009

    Posted 28 Sep 2009 Link to this post

    "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?
  2. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 29 Sep 2009 Link to this post

    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.
  3. DevCraft banner
Back to Top