Parsing data from XML in C#, filtering, categorizing in LINQ, displaying on Chart control This is my sample XML file:



&lt;?xml version="1.0" encoding="utf-8"?&gt;
&lt;receipts&gt;
  &lt;receipt id="F66BF89E-1070-4E02-9B98-B934A66D0E16" year="2014" month="9" day="17" place="Place#1"&gt;
    &lt;purchase name="Coca-Cola" quantity="1" price="3.99" category="Drinks" /&gt;
    &lt;purchase name="Watermelon" quantity="1" price="1.98" /&gt;
    &lt;purchase name="Water" quantity="1" price="1.49" category="Drinks" /&gt;
  &lt;/receipt&gt;
  &lt;receipt id="13ECD7F9-F639-4E65-9C80-9E1A8C2E305B" year="2014" month="9" day="17" place="Place#1"&gt;
    &lt;purchase name="Sweets" quantity="1" price="3.98" category="Sweets" /&gt;
    &lt;purchase name="Water" quantity="1" price="1.99" category="Drinks" /&gt;
    &lt;purchase name="Flour" quantity="1" price="1.99" /&gt;
    &lt;purchase name="Bread" quantity="1" price="1.19" /&gt;
    &lt;purchase name="Milk" quantity="1" price="1.98" category="Drinks"/&gt;
    &lt;purchase name="Potatoes" quantity="0.345" price="1.49" /&gt;
  &lt;/receipt&gt;
&lt;/receipts&gt;


And this is my approach to parsing, filtering, categorizing and displaying it:



// load XML
XElement xmlReceipts = XElement.Load(@".\receipts.xml");

// create objects from XML
List&lt;Receipt&gt; receipts = new List&lt;Receipt&gt;();

foreach (var xmlReceipt in xmlReceipts.Elements("receipt"))
{
    Guid id = new Guid(xmlReceipt.Attribute("id").Value);
    string place = xmlReceipt.Attribute("place").Value;
    int year = Int32.Parse(xmlReceipt.Attribute("year").Value);
    int month = Int32.Parse(xmlReceipt.Attribute("month").Value);
    int day = Int32.Parse(xmlReceipt.Attribute("day").Value);

    List&lt;ProductLine&gt; products = new List&lt;ProductLine&gt;();

    foreach (var xmlProduct in xmlReceipt.Elements("purchase"))
    {
      string name = xmlProduct.Attribute("name").Value;
      string quantity = xmlProduct.Attribute("quantity").Value;
      string price = xmlProduct.Attribute("price").Value;
      string category = xmlProduct.Attribute("category") == null ? "(no category)" : xmlProduct.Attribute("category").Value;
      products.Add(new ProductLine(name, quantity, price, category));
    }
    receipts.Add(new Receipt(id, place, year, month, day, products));
}

// filter to this month's receipts
var receiptsFiltered = from receipt in receipts
                 where receipt.Date.Year == DateTime.Now.Year &amp;&amp;
                 receipt.Date.Month == DateTime.Now.Month
                 select receipt.Products as List&lt;ProductLine&gt;;

// flatten lists of receipts and products
var productsFiltered = from products in receiptsFiltered
                 from product in products
                 select new { Name = product.Name, Price = product.Price, Quantity = product.Quantity, Category = product.Category };

// group products by category
var productsByCategory = from product in productsFiltered
                 group product by product.Category;


// calculate totals per category
Dictionary&lt;string, double&gt; categoriesTotals = new Dictionary&lt;string, double&gt;();

foreach (var category in productsByCategory)
{
    double totalSum = 0;

    foreach (var product in category)
    {
      totalSum += product.Quantity * (double)product.Price;
    }

    categoriesTotals.Add(category.Key, totalSum);
}

// show data on chart
chart1.Series[0].Points.DataBindXY(categoriesTotals.Keys, categoriesTotals.Values);


Is there a way to simplify it? I tried to use XML to LINQ or linking XML directly to Chart but I couldn't manage grouping before flattening the lists.

Sources:


https://stackoverflow.com/a/6429081/1813219
https://stackoverflow.com/a/8135087/1813219
https://stackoverflow.com/a/7325306/1813219
For the LINQ query at the end SelectMany is your friend.

You should be able to achieve your desired results in a single query:

var dateOfInterest = DateTime.Now; // just put in whatever date interests you

var categoryTotals = receipts
         // filter receipts by the desired month
        .Where(r =&gt; IsWithinSameMonthAndYear(r.Date, dateOfInterest))
        // select all products from all filtered receipts as a flat list
        .SelectMany(r =&gt; r.Products)
        // group the products by their category
        .GroupBy(p =&gt; p.Category)
        // turn into a dictionary keyed of the category key and the value is the weighted sum of the product quantities
        .ToDictionary(g =&gt; g.key.Key, g =&gt; g.Sum(p =&gt; p.Quantity * p.Price));


Little helper method:

// returns true if first is within the same month and year as second
private bool IsWithinSameMonthAndYear(DateTime first, DateTime second)
{
    return first.Year == second.Year &amp;&amp; first.Month == second.Month;
}