none
group by a case expression (select ... group by case when ...)

    Question

  • I am trying to figure out the LINQ syntax that equates to a "group by case" in TSQL.

    An example TSQL version might look like this:

    select   case
                when t.col1 = 1 then 'one' else 'not one' 
             end as grouping_column,
             count(*) as member_count
    from     sometable t
    group by case
                when t.col1 = 1 then 'one' else 'not one' 
             end       

    Here's what I had so far. I am attempting to use "let" to create the grouping expression, then grouping by the result of the let clause. The idea seems good but when I try to get "status" out of the group intellisense does not show it as available. If I remove the select on status and just get the count then the program compiles but fails at runtime with the error "the method 'join' is not supported:

                var resultsequence =
                    from h in somecontext.SomeHeader
                    join l in somecontext.Detaillines on h.id = l.id
                    let status =
                        DateTime.Now.Subtract((DateTime)l.SomeDate).Days > 5 ? "more than 5 days to go"
                        : DateTime.Now.Subtract((DateTime)l.SomeDate).Days < 0 ? "finished"
                        : "5 or fewer days to go"                
                    group l by status into lg
                    select new
                    {
                        groupname = lg.status // lg.status is not available here
                        c = lg.Count()
                    };     
    I've included my actual linq query (object names changed to protect the innocent) which includes a join, but an example of how to do the simplified query (without the join) shown in the TSQL at the top would be sufficient!





    • Edited by allmhuran Monday, September 23, 2013 6:53 AM
    Monday, September 23, 2013 6:49 AM

Answers

  • Hello,

    Thanks for posting your question to this forum.

    With your code, I made a sample and please see it below:

    var resultsequence = (from o in db.Orders
    
                                          join od in db.OrderDetails.DefaultIfEmpty() on o.OrderID equals od.OrderID
    
                                          group od by od.DataTime > new DateTime(2013, 09, 09) ? "over 2013-09-09" : "not over 2013-09-09" into ood
    
                                          select new { name = ood.Key, count = ood.Count() }
    
                                          ).ToList();
    

    The result:

    over 2013-09-09            2
    
    not over 2013-09-09        1
    

    And actually linq does support ‘=’, we should replace it with ‘equals’.

    If we want to show the group by filed, we use the lg.Key.

    If this does not work for you, please let me know.

    Thanks &Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by allmhuran Tuesday, September 24, 2013 7:03 AM
    Tuesday, September 24, 2013 3:25 AM
    Moderator

All replies

  • Hello,

    Thanks for posting your question to this forum.

    With your code, I made a sample and please see it below:

    var resultsequence = (from o in db.Orders
    
                                          join od in db.OrderDetails.DefaultIfEmpty() on o.OrderID equals od.OrderID
    
                                          group od by od.DataTime > new DateTime(2013, 09, 09) ? "over 2013-09-09" : "not over 2013-09-09" into ood
    
                                          select new { name = ood.Key, count = ood.Count() }
    
                                          ).ToList();
    

    The result:

    over 2013-09-09            2
    
    not over 2013-09-09        1
    

    And actually linq does support ‘=’, we should replace it with ‘equals’.

    If we want to show the group by filed, we use the lg.Key.

    If this does not work for you, please let me know.

    Thanks &Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by allmhuran Tuesday, September 24, 2013 7:03 AM
    Tuesday, September 24, 2013 3:25 AM
    Moderator
  • Oops, I must have switched "equals" to "=" when rewriting for the forum. A hard habit to break when you've been writing SQL for 15 years! This is a good solution, cheers.

    Out of curiosity, can it be done with a "let" clause as well?

    Tuesday, September 24, 2013 7:03 AM
  • Of course, it is possible.

    Just Like:

    var resultsequence = (from o in db.Orders
                                          join od in db.OrderDetails.DefaultIfEmpty() on o.OrderID equals od.OrderID
                                          let status = od.DataTime > new DateTime(2013, 09, 09) ? "over 2013-09-09" : "not over 2013-09-09"
                                          group od by status into ood
                                          select new { name = ood.Key, count = ood.Count() }
                                          ).ToList();
    Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Tuesday, September 24, 2013 7:54 AM
    Moderator