LINQ to XML with join and GroupBy(). Is this correct?

Question

I am learning LINQ and this seems like a fairly simple problem. I have a semi-working solution but I am sure it could be cleaned up.

The propertyAuto node represents a car with id = 606. This node needs to have at least two child propertyValue nodes, one that references a vehicleValuation element with attribute of "Book" and one of "Auto". (My code doesnt check this yet, they could both be Auto or Book now)

Finally, I need to get the Book value and Auto Value for the same car.

Note that I will never know any IDs or idRef's beforehand and in the future there will be multiple cars.

Here is my code now (ready to copy into LINQPad!)

var data = XElement.Parse (@"
<MyXML>
  <propertyAuto id='606'>
    <Values>
      <propertyValue idRef='f95d5dce-8152-4e9e-889e-7433d32664d6' />
      <propertyValue idRef='cd1a83a7-dd04-41f9-b31c-5408a38ac777' />
    </Values>
  </propertyAuto>
  <Valuations>
    <vehicleValuation id='cd1a83a7-dd04-41f9-b31c-5408a38ac777'
    valuationType='Auto' estimatedValue='8350.00' />
    <vehicleValuation id='f95d5dce-8152-4e9e-889e-7433d32664d6'
    valuationType='Book' estimatedValue='12475.00' />
  </Valuations>
</MyXML>");

var valuations = from property in data.Descendants("propertyValue")
                 join value in data.Descendants("vehicleValuation")
                 on
                    (string)property.Attribute("idRef")
                 equals
                    (string)value.Attribute("id")
                 where property.Parent.Descendants("propertyValue").Count() > 1
                 && ((string)value.Attribute("valuationType") == "Auto" || (string)value.Attribute("valuationType") == "Book")               
                 select new { Value = value.Attribute("estimatedValue").Value, Type = value.Attribute("valuationType").Value, PropertyID = property.Parent.Parent.Attribute("id").Value };

valuations.Dump();

var values = valuations.GroupBy(x=> x.PropertyID).FirstOrDefault();
string auto = values.Where(x => x.Type == "Auto").Select(x=>x.Value).First().ToString();
string book = values.Where(x => x.Type == "Book").Select(x=>x.Value).First().ToString();

auto.Dump();
book.Dump();

Is this workable or should I move to xpath, etc?

Answer 1

 var valuations = el.Element("Valuations").Elements("vehicleValuation");
 var carNodes = el.Elements("propertyAuto");
 var cars = carNodes.Select(x => 
                new {
                     id = x.Attribute("id").Value,
                     PropertyValues = x.Element("Values").Elements("propertyValue").
                                        SelectMany(
                                                y => valuations.Where( 
                                                        val => val.Attribute("id").Value == y.Attribute("idRef").Value
                                                                      ).Select( valuation => 
                                                                          new { 
                                                                                Type = valuation.Attribute("valuationType"),
                                                                                EstVal = valuation.Attribute("estimatedValue")
                                                                              }
                                                                              )
                                               )
                     });

A bit complicated, but this will create an anonymous type that has the cars ID matched to Property Values.

If you want the cars Auto and Book value directly you can do something like this.

var cars = carNodes.Select(car => 
                new {
                     id = car.Attribute("id").Value,
                     Auto = car.Element("Values").Elements("propertyValue").
                                        SelectMany(
                                                y => valuations.Where( 
                                                        val => val.Attribute("id").Value == y.Attribute("idRef").Value &&
                                                               val.Attribute("valuationType").Value == "Auto"
                                                                      )
                                                                .Select( valuation => valuation.Attribute("estimatedValue"))
                                               ).SingleOrDefault(),
                     Book = car.Element("Values").Elements("propertyValue").
                                          SelectMany(
                                                  y => valuations.Where(
                                                          val => val.Attribute("id").Value == y.Attribute("idRef").Value &&
                                                                 val.Attribute("valuationType").Value == "Book"
                                                                        )
                                                                  .Select(valuation => valuation.Attribute("estimatedValue"))
                                                 ).SingleOrDefault(),
                     });

Answer 2

Clearly your solution is workable because it works! I would also take it a little further to get the results as a list of cars with properties that you can easily inspect:

var carProperties = from propertyAuto in data.Descendants("propertyAuto")
                    select new
                    {
                        Id = propertyAuto.Attributes("id").First().Value,
                        Properties = from property in propertyAuto.Descendants("propertyValue")
                                     join value in data.Descendants("vehicleValuation")
                                     on property.Attribute("idRef").Value
                                     equals value.Attribute("id").Value
                                     select new
                                     {
                                         Value = value.Attribute("estimatedValue").Value,
                                         Type = value.Attribute("valuationType").Value,
                                         PropertyID = property.Parent.Parent.Attribute("id").Value
                                     }
                    };

var cars = from car in carProperties
           select new
           {
               Id = car.Id,
               Auto = car.Properties.Where(x => x.Type == "Auto").Select(x => x.Value).First(),
               Book = car.Properties.Where(x => x.Type == "Book").Select(x => x.Value).First()
           };

Notice that the GroupBy is no longer needed.