How can I used nested "CASE WHEN THEN END" in LINQ-CodePudding

I have a fairly simple nested "case-when-then-end" query in PostgreSQL which I have been trying to convert to LINQ without any success. Basically I am checking to see if Case 1: an ID in the node table has a Type1 relationship AND a Type2 relationship with product_ids in the license table OR Case 2: if it is related to a Type3 product_id in the license table. In the below query it returns Status1 if it finds a row with a Type1 license and another row with a Type2 license. It will return Status2 if it finds one row with a Type3 license. I hope that's a clear enough explanation. Quite simple in PostgreSQL but I'm a newbie to LINQ and in need of some help which I will be most grateful for.

SELECT n.id,
       (CASE WHEN EXISTS (SELECT 1
                          FROM admin.license l
                          WHERE l.product_id = 'Type1' and l.node_id = n.id
                         )
             THEN (CASE WHEN EXISTS (SELECT 1
                          FROM admin.license l
                          WHERE l.product_id = 'Type2' and l.node_id = n.id
                         )
             THEN 'Status1'  end ) 
        else 
               (CASE WHEN EXISTS (SELECT 1
                          FROM admin.license l
                          WHERE l.product_id = 'Type3' and l.node_id = n.id
                         )
             THEN 'Status2'
         END) 
        END) as Status
FROM admin.node n
join admin.bundle_node bn on n.id = bn.node_id where bn.bundle_id = 11
order by n.id asc;

My attempt in LINQ so far looks like this:

var result = (from l in Licenses
join n in Nodes on l.NodeId equals n.Id
join bn in BundleNodes on n.Id equals bn.NodeId
join b in Bundles on bn.BundleId equals b.Id
where b.Id == id select new
{
Id = bn.NodeId,
Status = **Hardware or Software depending on Case statement**
});

This code works as an if else if statement but I wasn't able to nest it:

Text = 
        (
            n == 1 ? "One" :
            n == 2 ? "Two" :
            n == 3 ? "Three" : "Unknown"
        )

I can't figure out how to nest this, assuming it has the same effect as nesting the CASE WHEN THEN END statements.

CodePudding user response：

You can use the ternary operator nested, just when translating from CASE WHEN...END you must realize the default result is NULL:

from n in Nodes
join bn in BundleNodes on n.Id equals bn.BundleId
where bn.BundleId == id
select new {
    n.Id,
    Status = (Licenses.Where(l => l.ProductId == "Type1" && l.NodeId == n.Id).Any()
                ? (Licenses.Where(l => l.ProductId == "Type2" && l.NodeId == n.Id).Any()
                    ? "Status1"
                    : null)
                : (Licenses.Where(l => l.ProductId == "Type3" && l.NodeId == n.Id).Any()
                    ? "Status2"
                    : null)
             )
}