Answered by:

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:

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!

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.

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.

<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.