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

Question

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.

Answer 1

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)
             )
}