Filter data from Xml according to date in C#

Question

I need help filtering xml file according to dates , with this part of the code that i have it only prints out all average information according to user name and place

        Runner run = new Runner();
       string filePath = "runners.xml"; //path
        XDocument xDoc = XDocument.Load(filePath);
        string userSelect = name;



        var averageAddDistancequery = xDoc.Descendants("User").Where(w => (string)w.Element("Name") == user).Select(s => new
        {
            add = s.Elements("Attempts").Where(w => (string)w.Element("Place").Value == "Paris").Select(t => t.Element("Distance").Value)
        }).ToList();

        if (averageAddDistancequery[0].add.Count() > 0) 
        {
            var aa = averageAddDistancequery[0].add.Average(a => float.Parse(a));
            run.averageDistance = aa.ToString();        
        }
        else
        {
           // nothing
        }

        var averageAdd2Distancequery = xDoc.Descendants("User").Where(w => (string)w.Element("Name") == userSelector).Select(s => new
        {
            add = s.Elements("Attempts").Where(w => (string)w.Element("Place").Value == "Madrid").Select(t => t.Element("Distance").Value)
        }).ToList();

        if (averageAdd2Distancequery[0].add.Count() > 0)
        {
            var aa = averageAdd2DistanceSubquery[0].add.Average(a => float.Parse(a));
            run.averageDistance2 = aa.ToString();
        }
        else
        {
           // nothing
        }



        xmlDoc.DocumentElement.SetAttribute("searching", user);
        XmlNodeList tests = xmlDoc.SelectNodes("//User[Name =/*/@searching]/Attempts");
        listBox1.Items.Clear();
        listBox1.Items.Add("Runners Name: " + user);
        listBox1.Items.Add("Overall  Distance in Paris: " + run.averageAdd);
        listBox1.Items.Add("Overall Distance in Madrid: " + run.averageSub);

For example if my xml file looks like this

    Users>
    <User>
     <Name>David</Name>
      <Attempts>
       <Place>Paris</Place>
       <Date>3/29/2012</Date>
       <Distance>100</Distance>
     </Attempts>
     <Attempts>
      <Place>Madrid</Place>
      <Date>7/28/2012</Date>
      <Distance>100</Distance>
     </Attempts>
     <Attempts>
      <Place>Paris</Place>
      <Date>8/19/2012</Date>
      <Distance>60</Distance>
     </Attempts>
     <Attempts>
      <Place>Madrid</Place>
      <Date>9/29/2012</Date>
      <Distance>200</Distance>
    </Attempts>  
   </User>
   <User>
    <Name>Lenny</Name>
     <Attempts>
      <Place>Paris</Place>
      <Date>9/29/2012</Date>
      <Distance>130</Distance>
     </Attempts>
  </User>
 </Users>

If i run the code for david it will print out something like this

User:David

Average Distance in Paris:// data

Average Distance in Madrid: // data

This is not what i want, what i want is to select any two dates lets from a textbox and display only the information between those two dates For example if i chose david, from date 3/29/2012 to 8/29/2012

I would want and output something like this:

User: David

Average Distance in Paris from 3/29/2012 to 8/29/2012: //data

Average Distance in Madrid from 3/29/2012 to 8/29/2012: //data

Ive been trying for hours, i need help implementing this

Answer 1

You can use this Xml library that will enable to read the dates and sort by them.

XElement root = XElement.Load(file);
XElement david = root.XPathElement("//User[Name={0}]", "David");
XElement[] attempts = david
         .XPath("Attempts[Date>={0} and Date<={1}]",
                new DateTime(2012, 3, 29), new DateTime(2012, 8, 29))
         .ToArray();

Answer 2

You can do, what you need, with Linq to Xml:

XElement x = XElement.Load("In.xml");
IFormatProvider f = new System.Globalization.CultureInfo("en-US");

DateTime bdate = DateTime.Parse("3/29/2012", f);
DateTime edate = DateTime.Parse("8/29/2012", f);
string username = "David";

var info = x.Elements("User")
            .Where(u => u.Element("Name").Value == username)
            .Select(u => new
{
  Name = u.Element("Name").Value,                   //user name
  AverageAttempts = u.Elements("Attempts")          //select user's attempts 
                     .Where(a =>                    //filter by dates
                     {
                        DateTime d = DateTime.Parse(a.Element("Date").Value, f);
                        return d >= bdate && d <= edate;
                     })
                     .GroupBy(a => a.Element("Place").Value) //group by place
                     .Select(g => new         // create summary info by place
                     {
                        Place = g.Key,              //place
                        BeginDate = g.Elements("Date") 
                                     .Select(d => DateTime.Parse(d.Value, f))
                                     .Min(),   //min date, i.e. first attempt
                        EndDate = g.Elements("Date")   
                                   .Select(d => DateTime.Parse(d.Value, f))
                                   .Max(),   //max date, i.e. last attempt
                        Distance = g.Elements("Distance")//average distance
                                    .Average(d => decimal.Parse(d.Value))
                     })
})
.FirstOrDefault();

if(info!=null)
{
   Console.WriteLine(info.Name);
   foreach (var aa in info.AverageAttempts)
   {
       Console.WriteLine(string.Format("{0} [{1} - {2}]:\t{3}",
                                       aa.Place,
                                       aa.BeginDate,
                                       aa.EndDate,
                                       aa.Distance));
   }
}

The output contains not the dates used to filter, but the actual min and max dates of the user attempts.

This code, of course, doesn't contain any validity checks on whether all the necessary xml tags present in the file, or whether values are valid dates and decimals...It's up to you to modify it under your certain needs.

Answer 3

Use LINQ to XML, assume in here two dates you select from TextBox:

var userElement = xDox.Descendants("User")
                .SingleOrDefault(u => u.Element("Name").Value == "David");

if (userElement != null)
{
    var result = userElement.Descendants("Attempts")
        .Select(a => new
            {
                Place = a.Element("Place").Value,
                Date = DateTime.Parse(a.Element("Date").Value),
                Distance = int.Parse(a.Element("Distance").Value)
            })

        .Where(a => a.Date >= DateTime.Parse("3/29/2012")
                    && a.Date <= DateTime.Parse("8/29/2012"))

        .GroupBy(a => a.Place)
        .Select(g => new {Place = g.Key, Avg = g.Average(x => x.Distance)});
}