How to convert this sql query into linq query-CodePudding

My SQL query looks like this, But when I try to convert LINQ. I can't do that.

select case when Status = 0 then  'Pending'
when Status = 1 then  'Approved'
when Status = 2 then  'Denied' else '' end Status,
count(1) totalCount
from Client_BurnOuts group by Status

Here I have an enum that contains 3 values, Pending, Approved, and Denied. SQL output is well, But I can't convert it to SQL.

CodePudding user response：

Suppose, your current value is 0 which means status would be pending so the corresponding linq would be as following:

var statusCaseLinq = new List<Status>()
            {
                new Status(){ StatusName = "Accepted",StatusId =2},
            };

            var caseToLinq =
            (
                from n in statusCaseLinq
                where n != null
                select new
                {
                    CaseId = n,
                    CaseSatus =
                    (
                        n.StatusId == 0 ? "Pending" :
                        n.StatusId == 1 ? "Accepted" :
                        n.StatusId == 2 ? "Denied" : "Unknown"
                    )
                }
            );



            var getCaseStstusFromId = caseToLinq.FirstOrDefault().CaseSatus;

Output:

CodePudding user response：

You can convert a case statement to a switch expression.

from o in Client_BurnOuts
group o by o.Status into g
select new
{
    Status = g.Key switch
    {
        0 => "Pending",
        1 => "Approved",
        2 => "Denied",
        _ => ""
    },
    totalCount = g.Count()
};