Translate a SQL CASE Statement into LINQ

Question

I am looking for assistance translating a SQL CASE Statement into LINQ code. Thing is, I need to implement it into code that already exists.

Basically, I need to transform memberIssues.IsClosed into the Alert Status CASE Statement below, but I've been having some difficulties.

List<IssueRowDTO> issuesList = new List<IssueRowDTO>();

            if (userRoleInfo.CanViewAllIssues)
            {
                #region View All Issues LINQ
                // If user can view all issues, then User ID check is removed.
                issuesList = (from memberIssues in dbContext.MemberIssues
                              from issueSteps in dbContext.IssueSteps.Where(issueSteps => issueSteps.ID == memberIssues.CurrentStepID).DefaultIfEmpty()
                              from issueDefs in dbContext.IssueDefs.Where(issueDefs => issueDefs.ID == memberIssues.IssueDefsID).DefaultIfEmpty()
                              from member in dbContext.Member.Where(member => member.ID == memberIssues.MemberID).DefaultIfEmpty()
                              from memberTeam in dbContext.MemberTeam.Where(memberTeam => memberTeam.MemberID == memberIssues.MemberID
                                                                                               && memberTeam.RoleID == issueSteps.RoleID).DefaultIfEmpty()
                              from userRoles in dbContext.UserRoles.Where(userRoles => userRoles.RoleID == memberTeam.RoleID
                                                                                            && userRoles.UserID == memberTeam.UserID).DefaultIfEmpty()
                              from users in dbContext.Users.Where(users => users.ID == userRoles.UserID).DefaultIfEmpty()
                              from plans in dbContext.Plans.Where(plans => plans.ID == member.PlanID).DefaultIfEmpty()
                              where memberIssues.CreatedDate > searchFromDate
                              select new IssueRowDTO
                              {
                                  IssueID = memberIssues.ID,
                                  MemberID = memberIssues.MemberID,
                                  ActionRequiredBy = users.FirstName + " " + users.LastName,
                                  MemberName = member.FirstName + " " + member.LastName,
                                  MLTC = plans.PlanName,
                                  Issue = issueDefs.IssueName,
                                  CreatedDate = memberIssues.CreatedDate,
                                  LastUpdate = memberIssues.LastUpdate ?? memberIssues.CreatedDate,
                                  NextDeadline = memberIssues.NextDeadline ?? 
                                  memberIssues.CreatedDate,
                                  IsClosed = memberIssues.IsClosed,
                                  ClosedOffService = memberIssues.ClosedOffService ?? "",
                                  SubIssueDefsID = memberIssues.SubIssueDefsID ?? 0,
                                  CurrentStepNum = issueSteps.StepNum
                              })
                              .ToList();

SQL

                CASE
                    WHEN CurrentStepNum > 1 THEN 'Positive'
                    WHEN CurrentStepNum = 1 AND memberIssues.IsClosed <> 1 THEN 'Pending'
                    WHEN CurrentStepNum = 1 THEN 'False Positive'
                END AS 'Alert Status'

The ternary operator is your answer. See my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786). — NetMage, Dec 09 '19 at 22:12

score 0 · Answer 1 · answered Dec 09 '19 at 22:17

I cheated (a little). Assuming StepNum is always >= 1, you could remove the == 1 test altogether as well.

AlertStatus = (issueSteps.StepNum > 1  ? "Positive" :
               issueSteps.StepNum == 1 ? (!memberIssues.IsClosed ? "Pending" : "False Positive"))

A more literal translation would be:

AlertStatus = (issueSteps.StepNum > 1  ? "Positive" :
               issueSteps.StepNum == 1 && !memberIssues.IsClosed ? "Pending" :
               issueSteps.StepNum == 1 ? "False Positive" : "Bad StepNum!")

Translate a SQL CASE Statement into LINQ

1 Answers1