How to put a self join with LINQ on XDocument?

Question

I have XDocument randomly containing few records in this format :

<Course>
  <CourseId>21</CourseId>
  <CourseName>leaf</CourseName>
  <CourseDesc>This course</CourseDesc>
  <Event>
    <EventName>Dallas, US - August 19, 2013</EventName>
    <EventStart>2013-08-19T00:00:00-04:00</EventStart>
    <EventEnd>2013-08-23T00:00:00-04:00</EventEnd>
  </Event>
  <Event>
    <EventName>Texas, US - August 19, 2013</EventName>
    <EventStart>2013-08-19T00:00:00-04:00</EventStart>
    <EventEnd>2013-08-23T00:00:00-04:00</EventEnd>
  </Event>
  <Event>
    <EventName>NY, US - August 19, 2013</EventName>
    <EventStart>2013-08-19T00:00:00-04:00</EventStart>
    <EventEnd>2013-08-23T00:00:00-04:00</EventEnd>
  </Event>
</Course>

(Generally <Event> node occurs just once)

I need to extract/Select/Filter them as :

<Course>
      <CourseId>21</CourseId>
      <CourseName>leaf</CourseName>
      <CourseDesc>This course</CourseDesc>
      <Event>
        <EventName>Dallas, US - August 19, 2013</EventName>
        <EventStart>2013-08-19T00:00:00-04:00</EventStart>
        <EventEnd>2013-08-23T00:00:00-04:00</EventEnd>
      </Event>
</Course>
 <Course>
      <CourseId>21</CourseId>
      <CourseName>leaf</CourseName>
      <CourseDesc>This course</CourseDesc>
      <Event>
        <EventName>Texas, US - August 19, 2013</EventName>
        <EventStart>2013-08-19T00:00:00-04:00</EventStart>
        <EventEnd>2013-08-23T00:00:00-04:00</EventEnd>
      </Event>
</Course>
 <Course>
      <CourseId>21</CourseId>
      <CourseName>leaf</CourseName>
      <CourseDesc>This course</CourseDesc>
      <Event>
        <EventName>NY, US - August 19, 2013</EventName>
        <EventStart>2013-08-19T00:00:00-04:00</EventStart>
        <EventEnd>2013-08-23T00:00:00-04:00</EventEnd>
      </Event>
</Course>

@user3632828 : Anything will work, not sure what to use to achieve it. — foo-baar, Jul 22 '14 at 11:26

har07 · Answer 1 · 2014-07-22T13:42:45.467

You can do this way, worked fine for me :

var doc = XDocument.Load("path_to_xml_file.xml");
var result = from e in doc.Root.Elements("Event")
             let id = doc.Root.Element("CourseId")
             let name = doc.Root.Element("CourseName")
             let desc = doc.Root.Element("CourseDesc")
             select new XElement("Course",
                                 id,
                                 name,
                                 desc,
                                 e);
foreach (XElement r in result)
{
    Console.WriteLine(r.ToString());
}

UPDATE (modified with more general approach as pointed out by @Enigmativiy) :

var result = from e in doc.Descendants("Event")
             let id = e.Parent.Element("CourseId")
             let name = e.Parent.Element("CourseName")
             let desc = e.Parent.Element("CourseDesc")
             select new XElement("Course",
                                 id,
                                 name,
                                 desc,
                                 e);

You know that this doesn't work? The OP said that the snippit XML in the question appears throughout the actual XML file - it's not the entire thing. This answer doesn't handle the general case. — Enigmativity, Jul 22 '14 at 12:37

score 1 · Accepted Answer · answered Jul 22 '14 at 12:05

1

Try this:

var query =
    doc
        .Root
        .Descendants("Event")
        .Select(e =>
            new XElement(
                "Course",
                e.Parent.Element("CourseId"),
                e.Parent.Element("CourseName"),
                e.Parent.Element("CourseDesc"),
                e));

It's simple and it worked for me.

answered Jul 22 '14 at 12:05

Enigmativity

113,464
11
89
172

This is so beautiful @Enigmativity, I couldn't explain well but the above mentioned xml is not the complete data but a record among the other 4K that are there, but not all have multiple occurrence of node. – foo-baar Jul 22 '14 at 17:04
But I believe the above code will work on the all records right ?? – foo-baar Jul 22 '14 at 17:06
Enigmativity : any suggestion on a resource to learning LINQ in depth ? – foo-baar Jul 22 '14 at 17:21
1

@VishalSachdeva - Go download [LINQPad](http://www.linqpad.net) and use that. It has a good number of sample LINQ queries to play with and it is a very good scratch pad for coding. It also lets you query databases. It's worth paying for the pro version too. – Enigmativity Jul 22 '14 at 23:42

score 0 · Answer 3 · answered Jul 22 '14 at 11:31

I would do it like this:

var xdoc = XDocumnet.Load(path);

var courses = xdoc.Descendants("Course").SelectMany(course => {
  var id = Getnode("CourseId");
  var name = Getnode("CourseName");
  var desc = Getnode("CourseDesc");
  var events = course.Descendants("Event");

return events.Select(e => new Course{
  Name = name,
  Id = id,
  Desc = desc,
  Event = e
});

});

I didn't check this code, but logicaly it should work. U need to implement Getnode, and then write it to the file. Good luck.

score 0 · Answer 4 · answered Jul 23 '14 at 09:48

Try this approach:

namespace XMLChildNodesSelection {

public class Course
{
    public int CourseId { get; set; }
    public string CourseName { get; set; }
    public string CourseDesc { get; set; }
    public List<Event> Events { get; set; }

    public Course(int _courseId, string _courseName, string _courseDesc, List<Event> _events)
    {
        CourseId = _courseId;
        CourseName = _courseName;
        CourseDesc = _courseDesc;
        Events = _events;
    }
}

public class Event
{
    public string EventName { get; set; }
    public string EventStart { get; set; }
    public string EventEnd { get; set; }

    public Event(string _eventName, string _eventStart, string _eventEnd)
    {
        EventName = _eventName;
        EventStart = _eventStart;
        EventEnd = _eventEnd;
    }
}

class Program
{
    static void Main(string[] args)
    {
        var xdoc = XDocument.Load(@"F:\Test\XMLChildNodesSelection\XMLFile1.xml");

        var course = xdoc.Descendants("Course")
           .Select(x => new Course((int)x.Element("CourseId"),
                                 (string)x.Element("CourseName"),
                                 (string)x.Element("CourseDesc"),
                                 (List<Event>)x.Descendants("Event")
                                     .Select(y => new Event(
                                         (string)y.Element("EventName"),
                                         (string)y.Element("EventStart"),
                                         (string)y.Element("EventEnd")
                                         )).ToList()
                                   )
                   );


        var table = from c in course
                    from e in c.Events
                    select new
                    {
                        CourseId = c.CourseId,
                        CourseName = c.CourseName,
                        CourseDesc = c.CourseDesc,
                        EventName = e.EventName,
                        EventStart = e.EventStart,
                        EventEnd = e.EventEnd
                    };
    }
}

}

How to put a self join with LINQ on XDocument?

4 Answers4