We can use LINQ to write a complex query like join and group by operations. The below sample shows a simple join query and the group by command in 2 XML files. I use the 2 xml files firstxml.xml and secondxml.xml

FirstXml.Xml

<dataroot>
  <Names>
    <NAME_ID>1121</NAME_ID>
    <PARENTS_NAME_ID>Selvan</PARENTS_NAME_ID>
  </Names>
  <Names>
    <NAME_ID>1122</NAME_ID>
    <PARENTS_NAME_ID>Sen</PARENTS_NAME_ID>
  </Names>
</dataroot>

SecondXml.Xml

<dataroot>
  <Info>
    <INFO_NAME_ID>1121</INFO_NAME_ID>
    <INFO_COMMENTS>Comment one</INFO_COMMENTS>
  </Info>
  <Info>
    <INFO_NAME_ID>1122</INFO_NAME_ID>
    <INFO_COMMENTS>Comment 1</INFO_COMMENTS>
  </Info>
  <Info>
    <INFO_NAME_ID>1122</INFO_NAME_ID>
    <INFO_COMMENTS>Comment 4</INFO_COMMENTS>
  </Info>
  <Info>
    <INFO_NAME_ID>1122</INFO_NAME_ID>
    <INFO_COMMENTS>Comment 7</INFO_COMMENTS>
  </Info>
  <Info>
    <INFO_NAME_ID>1122</INFO_NAME_ID>
    <INFO_COMMENTS>Comment 2</INFO_COMMENTS>
  </Info>
  <Info>
    <INFO_NAME_ID>1122</INFO_NAME_ID>
    <INFO_COMMENTS>Comment 2</INFO_COMMENTS>
  </Info>
  <Info>
    <INFO_NAME_ID>1122</INFO_NAME_ID>
    <INFO_COMMENTS>Comment 2</INFO_COMMENTS>
  </Info>
</dataroot>

Sample Code using LINQ

                XDocument first = XDocument.Load(“FirstXml.xml”);
                XDocument second = XDocument.Load(“SecondXml.xml”);

                XElement felement = first.Element(“dataroot”);
                XElement selement = second.Element(“dataroot”);

                var syns1 = from f in felement.Elements(“Names”)
                            join s in selement.Elements(“Info”) on f.Element(“NAME_ID”).Value equals
                            s.Element(“INFO_NAME_ID”).Value
                            where f.Element(“PARENTS_NAME_ID”).Value == “Sen”
                            orderby (string)s.Element(“INFO_COMMENTS”) descending
                            group s by s.Element(“INFO_COMMENTS”).Value into g
                            select g;

The above query returns the comments from second xml file matches the ID and group by the comment and order by comments descending