Retrieving multiple XML elements with LINQ by attributes

Given this XML:

<?xml version="1.0" encoding="utf-8" ?>
<queryableData>
  <table displayName="Shipments" dbName="Quotes">
    <foreignKey column="CustomerId" references="CustomerRegistration"/>
    <foreignKey column="QuoteStatusId" references="开发者_开发问答QuoteStatus"/>
    <fields>
      <field displayName="Quote Charge" dbColumn="QuoteCharge" type="Number"/>
      <field displayName="Total Weight" dbColumn="TotalWeight" type="Number"/>
    </fields>
  </table>
  <table displayName="Customers" dbName="CustomerRegistration">
    <fields>
      <field displayName="First Name" dbColumn="FirstName" type="Text" />
      <field displayName="Last Name" dbColumn="LastName" type="Text"/>
    </fields>
  </table>
</queryableData>

I want to get a list of an anonymous object type containing the DisplayName, DbColumn and Type fields for the "Quotes" table only.

I am able to get a list of both tables with the following LINQ to XML code:

    var xml = XElement.Load(@"C:\Sandbox\queryable.xml");

    // Tables
    var tables = from el in xml.Elements("table")
                        select new 
                        {
                            Text = el.Attribute("displayName").Value,
                            Value = el.Attribute("dbName").Value
                        };

But I am not sure how to get the fields value where the dbName attribute of the table element is "Quotes". The closest I've gotten is a list containing a list of values, instead of a list of anonymous objects with one item having the dbColumn/displayName pair:

var columns = from el in xml.Elements("table")
            where el.Attribute("dbName").Value.Equals("Quotes")
            select new LookupData { 
                Text = el.Elements("fields").Elements("field").Attributes().Where(x => x.Name == "displayName").Select(x => x.Value),
                Value = el.Elements("fields").Elements("field").Attributes().Where(x => x.Name == "dbColumn").Select(x => x.Value)
            };

so I expect this in a list:

LookupData #1

Text: "Quote Charge"

Value: "QuoteCharge"

LookupData #2

Text: "Total Weight"

Value: "TotalWeight"

and I'm getting this instead:

LookupData #1

Text: List containing two strings: [0] = "Quote Charge", [1] = "Total Weight"

Value: List containing two strings: [0] = "QuoteCharge", [1] = "TotalWeight"

---

use this:

var result = xml.Elements("table").
    Where(el => el.Attribute("dbName").Value.Equals("Quotes")). // 1
    SelectMany(el => el.Elements("fields").Elements("field")).  // 2
    Select(f => new 
                {
                    Text = f.Attributes().Where(x => x.Name == "displayName").
                        Select(x => x.Value).FirstOrDefault(),
                    Value = f.Attributes().Where(x => x.Name == "dbColumn").
                        Select(x => x.Value).FirstOrDefault()
                }
           ) // 3
;

Explanation:

1. We select the elements where dbName == "Quotes"
2. Of everyone of those elements, we select all field elements and flatten the result with the usage of SelectMany. The result will be an IEnumerable<XElement>.
3. For every field, we select an anonymous type with the Text property set to the value of first attribute named "displayName" and the Value property set to the value of the first attribute named "dbColumn"

The problem with your approach was basically, that you put step 2 inside the creation of the anonymous type. Or in other words: You created one anonymous type instance per "Quotes" element, not per "field" element.