You can do section ranking in SQL, how to do it in LINQ to xml

Question

I have an XML file with the following structure:

<Entities> <Request> <ID> A1 </ID> <!-- Can be multiple records with same values --> <Finance> <StartDate> Some Date </StartDate> <!-- Unique per ID --> </Finance> <Request> <Request> ... </Request> </Entities> 

There may be several queries with the same identifier, but in such cases, the StartDate should be different for each query.

I need to print the last two dates on an identifier.

If it was an SQL table with ID and StartDate columns, I would use the following query, which works fine:

 SELECT ID, StartDate FROM ( SELECT ID, StartDate, RANK() OVER (PARTITION BY ID ORDER BY StartDate DESC) rank FROM Request ) WHERE rank IN ('1','2') 

But I have the data in XML format, and the best I could come up with was to arrange the data according to ID, StartDate. I still need to dig up the last two dates for each identifier.

var cafrequests =

 from request in xdoc.Descendants("Request") orderby (int)request.Element("ID"), (DateTime)request.Element("Finance").Element("StartDate") ascending select new { ID = request.Element("ID"), StartDate = request.Element("Finance").Element("StartDate"), }; 

Using Take (2), you will only get the top two rows of data, not the top 2 by identifier.

So can anyone tell me what is equivalent to the above SQL statement in LINQ? I don't want to parse and manipulate XML with loops and conditional expressions in C #, and I'm pretty new to LINQ (read about it yesterday and started using it), and I'm still looking at the documentation.

Answer 1

This works, I tested it:

  XDocument doc = XDocument.Load(@"Data.xml"); var result2 = doc.Element("Entities") .Elements("Request") .GroupBy(key => key.Element("ID").Value, el => DateTime.Parse(el.Element("Finance").Element("StartDate").Value)) .Select(el => new { id = el.Key, max2 = el.OrderByDescending(date => date).Take(2) }); 

• doc.Element("Entities") - gets an element called Entity, which is the root element of the document, another way to get this element is doc.Root
• Elements("Request") - gets the elements named Request, which are child elements of Entity
• GroupBy is a method somewhat similar to GROUP BY in SQL, the first parameter is the element that will be used for grouping, this is the child of the Request element, the second parameter is the element selector function that parses the DateTime from the StartDate element (which is a child of Finance which is a child of the Request element)
• .Select(el => new...) - a method that creates an anonymous type that contains an identifier and 2 records with the highest dates for ID

Here is a query that gets the same result, but written in the query syntax (the example above is the method syntax):

  var result = from el in doc.Root.Elements("Request") group DateTime.Parse(el.Element("Finance").Element("StartDate").Value) by el.Element("ID").Value into grouped select new { id = grouped.Key, max2 = (from el in grouped orderby el descending select el) .Take(2) }; 

Answer 2

Can we group by identifier and select the latest dates? Something like that. If you get a list of identifiers and corresponding dates in the result set (cafrequests)

 var result = cafrequests.GroupBy(x=>x.Id).Select(x=>new{ x.Key,Dates = x.OrderByDescending(y => y.StartDate).Select(y=>y.StartDate).Take(2)}).ToDictionary(x=>x.Key); 

This will give you two top dates for each identifier.