Cascading Drop Down Lists with Lambda Expression in LINQ

Question

I have a series of drop down lists that cascade, but instead of the list options being excluded from the list according to the user's selection, the unrelated options in the list are disabled. They are still present in the drop down list, but they are greyed out, the user cannot select them and they will not be included in the submit.

I accomplish this with a left join between the list options and a subset of the list options that are applicable to the user's current selection. If a member of the main list is missing from the subset, that will return null and I can case that to provide the boolean that indicates whether not that option will be disabled.

Suppose I have three cascaded drop down lists tied to the following three tables: OptionA, OptionB and OptionC. vwOptionIndex is a view that indexes all the combinations of OptionA, OptionB and OptionC that are currently in use, thus providing the applicable subset for the cascading.

If the user selects two values in OptionB (123, 456) and one value in OptionC (789), then to return the list for OptionA I would use the following SQL Server query.

SELECT
    a.ID,
    a.Label,
    CAST(
        CASE
            WHEN SUM(
                CASE
                    WHEN x.OptionA_ID IS NULL
                    THEN 0
                    ELSE 1
                END
            ) > 0
            THEN 0
            ELSE 1
        END AS BIT
    ) AS [Disabled]
FROM dbo.OptionA AS a
LEFT OUTER JOIN dbo.vwOptionIndex AS x
ON a.ID = x.OptionA_ID
AND OptionB_ID IN (123, 456)
AND OptionC_ID IN (789)
GROUP BY a.ID, a.Label

I am trying to represent this query in LINQ. I've figured out the join, but i haven't been able to figure out how to represent the portion of the query that returns the bit that tells whether or not that list option should be disabled.

from t1 in OptionAs
join t2 in VwOptionIndex
on new { t1.ID} equals new { t2.OptionA_ID } into j1
from j2 in j1.DefaultIfEmpty()
group j2 by new { t1.ID, t1.Label } into g
select new { g.Key.ID, g.Key.Label, Disabled = <???> }

In other words, I need the following bit of logic in the LINQ query expression above.

CAST( CASE WHEN SUM( CASE WHEN x.OptionA_ID IS NULL THEN 0 ELSE 1 END ) > 0 THEN 0 ELSE 1 END AS BIT ) AS [Disabled]

Any help would be greatly appreciated.

Thanks!

Answer 1

You don't actually need the Sum, you only need to check whether there are any values in the group that are not null.

from t1 in OptionAs
join t2 in VwOptionIndex
on new { t1.ID} equals new { t2.OptionA_ID } into t3
from t2 in t3.DefaultIfEmpty()
group t1 by new { t1.ID, t1.Label } into g
select new 
{ 
   g.Key.ID, 
   g.Key.Label, 
   Disabled = g.Any (x => x.OptionA_ID.HasValue)
}