Queries with complex filtering LINQ to XML c#

Question

I want to understand the complex filtering with LINQ XML. I have created a simple XML example (DataBaseCities.xml):

<?xml version="1.0" encoding="utf-8"?>
<DataBase>
    <DocumentInfo version="1.0" schemaVersion="1.0"/>
    <ListOfMegaCities>
        <MegaCities city="Moscow" continent="Europe">
            <VariantConstraint>
                <LanguageRef LanguageId="russian">
                    <LanguageDialectsRef DialectsId="north"/>
                </LanguageRef>
            </VariantConstraint>
            <Districts>                
                <CityDistrict District="Arbat"/>
                <CityDistrict District="Basmanny"/>
            </Districts>
        </MegaCities>
        <MegaCities city="New York" continent="North America">
            <VariantConstraint>
                <LanguageRef LanguageId="english">
                    <LanguageDialectsRef DialectsId="west"/>
                </LanguageRef>
                <LanguageRef LanguageId="spanish">
                    <LanguageDialectsRef DialectsId="cental"/>
                </LanguageRef>              
            </VariantConstraint>
            <Districts>                
                <CityDistrict District="Queens"/>
                <CityDistrict District="Bronx"/>
            </Districts>
        </MegaCities>
        <MegaCities city="London" continent="Europe">
            <VariantConstraint>
                <LanguageRef LanguageId="english">
                    <LanguageDialectsRef DialectsId="west"/>
                </LanguageRef>
                <LanguageRef LanguageId="spanish">
                    <LanguageDialectsRef DialectsId="central"/>
                </LanguageRef>  
                <LanguageRef LanguageId="french">
                    <LanguageDialectsRef DialectsId="central"/>
                </LanguageRef>              
            </VariantConstraint>
            <Districts>                
                <CityDistrict District="Greenwich"/>
                <CityDistrict District="Westminster"/>
            </Districts>
        </MegaCities>       
    </ListOfMegaCities>
</DataBase>

And I try to filter like:

        XElement root = XElement.Load(@"DataBaseCities.xml");
        IEnumerable<XElement> ListOfMegaCities =
            from el in root.Descendants("MegaCities")
            where
                (from add in el.Descendants("LanguageRef")
                 where
                      (string)add.Attribute("LanguageId") == "english"
                 select add)
            .Any()
            select el;

        foreach (XElement el in ListOfMegaCities)
        {
            Console.WriteLine((string)el.Attribute("city"));
        }

So the Output is:

New York
London

But I want to filter more then one Attribute.

1. If I try to filter with these lines:

   (string)add.Attribute("LanguageId") == "english" && (string)add.Attribute("LanguageId") == "spanish"

Why it dont work?

2. How I can filter "DialectsId" too?

Example: I want to get "New York" with exactly this filtering:

LanguageId="english"
DialectsId="west"
LanguageId="spanish"
DialectsId="cental"

Maybe someone has a good source for complex filtering? I found this one https://learn.microsoft.com/de-de/dotnet/standard/linq/write-queries-complex-filtering but that helped me only partially...

Answer 1

I have used All instead of Any so that it filters only those cities which satisfy all the conditions. I am used to writing LINQ Expressions instead of LINQ Query.

List<XElement> ListOfMegaCities = root.Descendants("MegaCities")
                                 .Where(lr => lr.Descendants("LanguageRef")
                                 .All(
                                      li => (li.Attribute("LanguageId").Value == "english" 
                                          && li.Element("LanguageDialectsRef").Attribute("DialectsId").Value == "west")
                                          || (li.Attribute("LanguageId").Value == "spanish" 
                                          && li.Element("LanguageDialectsRef").Attribute("DialectsId").Value == "cental")
                                            )                                     
                                 ).ToList();

Answer 2

First, see @zaggler comment. Deserialize and manipulate object will be more easily.

Second, if you want the cities where only english and spanish is spoken, see @AkshayGaonkar answer. If you want cities where english and spanish (and maybe other languages) is spoken, see my answer.

---

To write complex Linq query, I begin to write simulacrum SQL :

-- I want all cities
select * from MegaCities as el
where
    -- that speak english with west dialect
    exists (
        select * from el.LanguageRef as add
        where add.LanguageId = 'english' and
            add.LanguageDialectsRef.DialectsId = 'west'
    )
    -- and also speak spanish with central dialect
    and exists (
        select * from el.LanguageRef as add
        where add.LanguageId == 'spanish' and
            add.LanguageDialectsRef.DialectsId = 'cental'
    )

Then I can transpose to Linq :

IEnumerable<XElement> ListOfMegaCities =
    from el in root.Descendants("MegaCities")
    where
        (from add in el.Descendants("LanguageRef")
         where add.Attribute("LanguageId").Value == "english" &&
            add.Element("LanguageDialectsRef").Attribute("DialectsId").Value == "west"
         select add)
        .Any()
        &&
        (from add in el.Descendants("LanguageRef")
         where add.Attribute("LanguageId").Value == "spanish" &&
            add.Element("LanguageDialectsRef").Attribute("DialectsId").Value == "cental"
         select add)
        .Any()
    select el;

Maybe not the best trick, but can help sometimes.