Parsing data from XML in C#, filtering, categorizing in LINQ, displaying on Chart control

Question

This is my sample XML file:

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

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<Receipt> receipts = new List<Receipt>();

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<ProductLine> products = new List<ProductLine>();

    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 &&
                 receipt.Date.Month == DateTime.Now.Month
                 select receipt.Products as List<ProductLine>;

// 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<string, double> categoriesTotals = new Dictionary<string, double>();

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

Answer 1

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 => IsWithinSameMonthAndYear(r.Date, dateOfInterest))
        // select all products from all filtered receipts as a flat list
        .SelectMany(r => r.Products)
        // group the products by their category
        .GroupBy(p => p.Category)
        // turn into a dictionary keyed of the category key and the value is the weighted sum of the product quantities
        .ToDictionary(g => g.key.Key, g => g.Sum(p => 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 && first.Month == second.Month;
}