Multiple selects within query

Question

This query is building commodity symbols from an xml file.

The file structure is

<Commodities>
  <Grains>
    <Commodity title="Corn" value="0" name="corn">
      <Specs>
        //other elements
        <SymbolRoot>
          <Platform value="ZC" name="globex"/>
          <Platform value="C " name="bloomberg"/>
        </SymbolRoot>
      </Specs>
      <ContractMonths firstnoticedaterule="" lasttradedaterule="The business day prior to the 15th calendar day of the contract month">
        <Month value="Mar">
         <Year value="2018" firstnoticedate="02/28/18" lasttradedate="03/14/18" dateformat="mm/dd/yy"/>
          <Year value="2019" firstnoticedate="02/28/19" lasttradedate="03/14/19" dateformat="mm/dd/yy"/>
          <Year value="2020" firstnoticedate="02/28/20" lasttradedate="03/13/20" dateformat="mm/dd/yy"/>
        </Month>
      </ContractMonths>
    </Commodity>
  </Grains>
<Commodities>

For right now, I can get the contract months as needed, but I also need the symbol root for the platform passed in. Right now it is hardcoded as "C "

private List<string> GetAllSymbolsForContractMonths(CommodityList commodity, string platform)
{
    var symCode = string.Empty;
    var yrLastDigit = DateTime.Now.Year % 10;
    //get the contract month symbol codes
    var query = _doc.Descendants("Commodity")
            .Where(c => c.Attribute("name")?.Value == commodity.ToString().ToLower())
            .Descendants("ContractMonths").Elements("Month")
            .Select(v => "C " + SymbolHelpers.GetSymbolContractMonthLetter(v.Attribute("value")?.Value) + yrLastDigit + " Comdty")
            .ToList();

    return query;
}

I know there is some way to do a select on the value attribute of the Platform element and set that value to the symCode variable but I cant seem to get it right. Then I can just replace the hardcoding with the variable.

Answer 1

Just for kicks, there are some cool sites to convert your XML to C# classes (e.g. Xml2CSharp.com). Using the tool, you can create a set of classes to deserialize to and then leverage LINQ to Objects.

Commodities commodities = null;
using (var stream = new StringReader(xmlString))
{
    XmlSerializer serializer = new XmlSerializer(typeof(Commodities));
    commodities = (Commodities) serializer.Deserialize(stream);
}

var commodityName = "corn";
var platformName = "bloomberg";
var year = "2018";
var commodity = commodities.Grains.Commodity.Single(c => c.Name.Equals(commodityName, StringComparison.InvariantCultureIgnoreCase));
var symbol = commodity.Specs.SymbolRoot.Platform.Single(p => p.Name.Equals(platformName, StringComparison.InvariantCultureIgnoreCase));
var months = commodity.ContractMonths.Month.Year.Where(y => y.Value.Equals(year, StringComparison.InvariantCultureIgnoreCase));

Here are the classes I generated using the tool. It's obviously a lot more code but I like have concrete classes to work with. Hope it's helpful.

[XmlRoot(ElementName = "Platform")]
public class Platform
{
    [XmlAttribute(AttributeName = "value")]
    public string Value { get; set; }
    [XmlAttribute(AttributeName = "name")]
    public string Name { get; set; }
}

[XmlRoot(ElementName = "SymbolRoot")]
public class SymbolRoot
{
    [XmlElement(ElementName = "Platform")]
    public List<Platform> Platform { get; set; }
}

[XmlRoot(ElementName = "Specs")]
public class Specs
{
    [XmlElement(ElementName = "SymbolRoot")]
    public SymbolRoot SymbolRoot { get; set; }
}

[XmlRoot(ElementName = "Year")]
public class Year
{
    [XmlAttribute(AttributeName = "value")]
    public string Value { get; set; }
    [XmlAttribute(AttributeName = "firstnoticedate")]
    public string Firstnoticedate { get; set; }
    [XmlAttribute(AttributeName = "lasttradedate")]
    public string Lasttradedate { get; set; }
    [XmlAttribute(AttributeName = "dateformat")]
    public string Dateformat { get; set; }
}

[XmlRoot(ElementName = "Month")]
public class Month
{
    [XmlElement(ElementName = "Year")]
    public List<Year> Year { get; set; }
    [XmlAttribute(AttributeName = "value")]
    public string Value { get; set; }
}

[XmlRoot(ElementName = "ContractMonths")]
public class ContractMonths
{
    [XmlElement(ElementName = "Month")]
    public Month Month { get; set; }
    [XmlAttribute(AttributeName = "firstnoticedaterule")]
    public string Firstnoticedaterule { get; set; }
    [XmlAttribute(AttributeName = "lasttradedaterule")]
    public string Lasttradedaterule { get; set; }
}

[XmlRoot(ElementName = "Commodity")]
public class Commodity
{
    [XmlElement(ElementName = "Specs")]
    public Specs Specs { get; set; }
    [XmlElement(ElementName = "ContractMonths")]
    public ContractMonths ContractMonths { get; set; }
    [XmlAttribute(AttributeName = "title")]
    public string Title { get; set; }
    [XmlAttribute(AttributeName = "value")]
    public string Value { get; set; }
    [XmlAttribute(AttributeName = "name")]
    public string Name { get; set; }
}

[XmlRoot(ElementName = "Grains")]
public class Grains
{
    [XmlElement(ElementName = "Commodity")]
    public List<Commodity> Commodity { get; set; }
}

[XmlRoot(ElementName = "Commodities")]
public class Commodities
{
    [XmlElement(ElementName = "Grains")]
    public Grains Grains { get; set; }
}

Answer 2

Split your query in two. First, find the commodity element.

From that element, find the platform symbol. Then, build the list.

private List<string> GetTypeFromVariable(CommodityList commodity, string platform)
{
    var yrLastDigit = DateTime.Now.Year % 10;

    var commodityElement = _doc.Descendants("Commodity")
        .Where(x => x.Attribute("name")?.Value.Equals(commodity.ToString(), StringComparison.InvariantCultureIgnoreCase) ?? false)
        .Single();

    var symbol = commodityElement.Descendants("Platform")
        .Where(x => x.Attribute("name")?.Value.Equals(platform, StringComparison.InvariantCultureIgnoreCase) ?? false)
        .Single()
        .Attribute("value").Value;

    return commodityElement
        .Descendants("ContractMonths").Elements("Month")
            .Select(v => symbol + " " + SymbolHelpers.GetSymbolContractMonthLetter(v.Attribute("value")?.Value) + yrLastDigit + " Comdty")
            .ToList();
}