In version 8.21.X of Oracle.EntityFrameworkCore this LINQ query generates correctly
List<PendingApprovals> approval = await DbContext.InvReadyForApprovalsVw
.Select(t => new PendingApprovals()
{
ApprovalId = t.ApprovalId,
TransferId = t.TransferId,
ApprovalDescription = t.ApprovalDescription == "Please acknowledge receipt for the item(s) assigned to you" ? "Inventory Item(s) Transfer From IT.Transfer to "+t.ReceivingEmployeeName : t.ApprovalDescription,
ApprovalRequestSentDate = t.ApprovalRequestSentDate,
ApprovalUserName = t.ApprovalUserName == "Position Not Assigned. Please Cancel/Resubmit" ? t.ApprovalUserName : "Pending Approval By " + t.ApprovalUserName,
})
.Concat(DbContext.InvRejectedApprovalRequestsVw
.Select(t => new PendingApprovals()
{
ApprovalId = t.ApprovalId,
TransferId = t.TransferId,
ApprovalDescription = t.ApprovalDescription,
ApprovalRequestSentDate = t.ApprovalRequestSentDate,
ApprovalUserName = "Rejected By " + t.ApprovalUserName,
})).OrderByDescending(x=> x.ApprovalRequestSentDate).ToListAsync();
SQL Generated
SELECT "t"."ApprovalId", "t"."TransferId", "t"."ApprovalDescription", "t"."ApprovalRequestSentDate", "t"."ApprovalUserName"
FROM (
( SELECT "i"."APPROVAL_ID" "ApprovalId", "i"."TRANSFER_ID" "TransferId", CASE
WHEN "i"."APPROVAL_DESCRIPTION" = 'Please acknowledge receipt for the item(s) assigned to you' THEN 'Inventory Item(s) Transfer From IT.Transfer to ' || COALESCE("i"."RECEIVING_EMPLOYEE_NAME", NULL)
ELSE "i"."APPROVAL_DESCRIPTION"
END "ApprovalDescription", "i"."APPROVAL_REQUEST_SENT_DATE" "ApprovalRequestSentDate", CASE
WHEN "i"."APPROVAL_USER_NAME" = 'Position Not Assigned. Please Cancel/Resubmit' THEN "i"."APPROVAL_USER_NAME"
ELSE 'Pending Approval By ' || COALESCE("i"."APPROVAL_USER_NAME", NULL)
END "ApprovalUserName"
FROM "INV_READY_FOR_APPROVALS_VW" "i" )
UNION ALL
( SELECT "i0"."APPROVAL_ID" "ApprovalId", "i0"."TRANSFER_ID" "TransferId", "i0"."APPROVAL_DESCRIPTION" "ApprovalDescription", "i0"."APPROVAL_REQUEST_SENT_DATE" "ApprovalRequestSentDate", 'Rejected By ' || COALESCE("i0"."APPROVAL_USER_NAME", NULL) "ApprovalUserName"
FROM "INV_REJECTED_APPROVAL_REQUESTS_VW" "i0" )
) "t"
ORDER BY "t"."ApprovalRequestSentDate" DESC
After Upgrading to 8.23.X it's now adding “N” to the string literals causing ORA-12704: character set mismatch error
SELECT "t"."ApprovalId", "t"."TransferId", "t"."ApprovalDescription", "t"."ApprovalRequestSentDate", "t"."ApprovalUserName"
FROM (
( SELECT "i"."APPROVAL_ID" "ApprovalId", "i"."TRANSFER_ID" "TransferId", CASE
WHEN "i"."APPROVAL_DESCRIPTION" = 'Please acknowledge receipt for the item(s) assigned to you' THEN N'Inventory Item(s) Transfer From IT.Transfer to ' || COALESCE("i"."RECEIVING_EMPLOYEE_NAME", NULL)
ELSE "i"."APPROVAL_DESCRIPTION"
END "ApprovalDescription", "i"."APPROVAL_REQUEST_SENT_DATE" "ApprovalRequestSentDate", CASE
WHEN "i"."APPROVAL_USER_NAME" = 'Position Not Assigned. Please Cancel/Resubmit' THEN "i"."APPROVAL_USER_NAME"
ELSE N'Pending Approval By ' || COALESCE("i"."APPROVAL_USER_NAME", NULL)
END "ApprovalUserName"
FROM "INV_READY_FOR_APPROVALS_VW" "i" )
UNION ALL
( SELECT "i0"."APPROVAL_ID" "ApprovalId", "i0"."TRANSFER_ID" "TransferId", "i0"."APPROVAL_DESCRIPTION" "ApprovalDescription", "i0"."APPROVAL_REQUEST_SENT_DATE" "ApprovalRequestSentDate", N'Rejected By ' || COALESCE("i0"."APPROVAL_USER_NAME", NULL) "ApprovalUserName"
FROM "INV_REJECTED_APPROVAL_REQUESTS_VW" "i0" )
) "t"
ORDER BY "t"."ApprovalRequestSentDate" DESC