LINQ to XML query

Question

I have two xml files.

First company.xml:

<company active="1">
    <name>name1</name>
    <location>first floor</location>
    <room>25</room>
    <category>power,gas,water</category>
</company>

<company active="1">
    <name>name2</name>
    <location>second floor</location>
    <room>23</room>
    <category>water,gas</category>
</company>

Second bills.xml:

<bill>
    <name>bill1</name>
    <category>power</category>
    <total>5432</total>
</bill>

<bill>
    <name>bill2</name>
    <category>power</category>
    <total>1200</total>
</bill>

<bill>
    <name>bill2</name>
    <category>gas</category>
    <total>3464</total>
</bill>

Now i have this query where i'm grouping xml by company name element, and summing total value from bills

XDocument fDoc = XDocument.Load("company.xml");
XDocument rDoc = XDocument.Load("bills.xml");


var query = from f in fDoc.Elements("company")
where ((string)f.Attribute("active")).Equals("1")
orderby f.Element("name").Value
from r in racuniRoot.Elements("bill")
where (f.Element("category").Value).Split(',').Contains(r.Element("category").Value)
group new
{
BillTotal = Convert.ToInt32(r.Element("total").Value)
}
by f.Element("name").Value into g
select new
{
Name = g.Key,
Total = g.Sum(rec =>rec.BillTotal)
};

foreach (var k in query)
{
    litList.Text += k.Name + k.Total;
}

So the result with this query is:

name1 6632

name2 3464

And that is ok, but how do i select other company elements (location and room) in this query?

This is what i want for final result:

name1 6632 first floor 25

name2 3464 second floor 23

How can i do this?

Thanks!

Answer 1

do a group by on name, location and room

var query = from f in fDoc.Descendants("company")
                    where ((string)f.Attribute("active")).Equals("1")
                    orderby f.Element("name").Value
                    from r in rDoc.Descendants("bill")
                    where (f.Element("category").Value).Split(',').Contains(r.Element("category").Value)
                    group new
                    {
                        BillTotal = Convert.ToInt32(r.Element("total").Value)
                    }
                    by f.Element("name").Value + f.Element("location").Value + f.Element("room").Value into g
                    select new
                    {
                        Name = g.Key,
                        Total = g.Sum(rec => rec.BillTotal),
                    };