LINQ to XML - Selecting elements based on values that exist in other elements

Question

I guess I'm pushing the boundaries of whats capable with LINQ to XML but I'd like to select a group of elements that contain values that match a value contained in another element node.

In the XML below I want to select only the "Option" elements that contain values that are also contained in the AvailableOptions" element for a specific product ID.

Something like the following pseudo-code:

Select All Options where Option Name is in (Select AvailableOptions Where ProductID = "xxx")

<Agents>
<Agent ID="1">
    <Login>111</Login>
    <Password>pass</Password>
    <Products>
        <Product ID="xxx">
            <AvaiableOptions>aaa,bbb</AvaiableOptions>
        </Product>
    </Products>
    <Products>
        <Product ID="yyy">
            <AvaiableOptions>bbb,ccc</AvaiableOptions>
        </Product>
    </Products>
    <Products>
        <Product ID="zzz">
            <AvaiableOptions>aaa,ccc</AvaiableOptions>
        </Product>
    </Products>
    <Options>
        <Option>
            <Name>aaa</Name>
            <Value>10</Value>
        </Option>
        <Option>
            <Name>bbb</Name>
            <Value>20</Value>
        </Option>
        <Option>
            <Name>ccc</Name>
            <Value>30</Value>
        </Option>
    </Options>
</Agent>

Answer 1

This is my attempt, a little lengthy though. Hope it helps.

var query = from agent in agents.Descendants("Agent")
            from products in agent.Descendants("Products")
            from product in products.Descendants("Product")
            where product.Attribute("ID").Value == "xxx"
            from options in agent.Descendants("Options")
            from option in options.Descendants("Option")
            where product.Element("AvaiableOptions").Value.Contains(option.Element("Name").Value)
            select option;