Importing XML to a database using LINQ to Entities

Question

I am importing XML data to a MySql database using LINQ to Entities. The data represents which students are in which classes, and looks like...

<studentClassesData>
  <studentClassEntry>
    <upn>W432980573452</upn>
    <className>8bcDn4</className>
  </studentClassEntry>
  <studentClassEntry>
    <upn>W234525211334</upn>
    <className>8bcPe1</className>
  </studentClassEntry>
  <!-- etc... -->
</studentClassesData>

Each entry represents one student in one class. I know it would be better if the format were hierarchical (so each entry gives us all the students in a class), but the XML is generated externally so we have to use it as is.

Receiving this information are two entities, Class and Student, which are linked in a many-to-many relationship (e.g. Class.Students). All the students and classes have already been imported to the database.

Note that Student.Upn and Class.LongName are unique keys which correspond to the values given in the XML. But the primary keys are Student.Id and Class.Id.

Here's my current code, with verbose comments added for explanation.

public static string ProcessStudentClasses(string path) {
  var sb = new StringBuilder();

  using (var ctx = new Ctx()) {

    // Create a dictionary of all classes, to be accessed by their LongName
    // (attach the classes, but don't bother loading their properties)
    var classes = ctx.Classes.Select(o => new { o.LongName, o.Id })
          .ToDictionary(o => o.LongName, o => new Class { Id = o.Id });
    foreach (var cls in classes.Values) ctx.Classes.Attach(cls);

    // Similarly for students
    var students = ctx.Students.Select(o => new { o.Upn, o.Id })
          .ToDictionary(o => o.Upn, o => new Student { Id = o.Id });
    foreach (var stu in students.Values) ctx.Students.Attach(stu);

    // Create a lookup of which classes already have which students
    var studentsInClasses =
          ctx.Classes.SelectMany(c => c.Students,
                                 (c, s) => new { ClassId = c.Id, StudentId = s.Id }
                                ).ToLookup(o => o.ClassId, o => o.StudentId);

    using (var reader = XmlReader.Create(path)) {

      var nodeName = String.Empty;
      var upn = String.Empty;
      var className = String.Empty;

      Student thisStudent = null;
      Class thisClass = null;

      while (reader.Read()) {
        switch (reader.NodeType) {

          case XmlNodeType.Element:
            // We're reading an XML element; note which element it is
            nodeName = reader.Name;
            break;

          case XmlNodeType.Text:
            // We're reading text (what's between the <nodeName> and </nodeName> tags)
            var trimmed = reader.Value.Trim();
            if (trimmed.Length == 0) continue;

            switch (nodeName) {
              case "upn":
                upn = trimmed;
                students.TryGetValue(upn, out thisStudent); // set 'thisStudent'
                break;
              case "className":
                className = trimmed;
                classes.TryGetValue(className, out thisClass); // set 'thisClass'
                break;
            }
            break;

          case XmlNodeType.EndElement:
            if (reader.Name != "studentClassEntry") continue;

            // We've got to the end of a studentClassEntry

            // If the student wasn't set...
            if (thisStudent == null) {
              sb.AppendFormat("Skipped: {0} - {1} (unknown UPN)<br />", upn, className);

            // If the class wasn't set...
            } else if (thisClass == null) {
              sb.AppendFormat("Skipped: {0} - {1} (unknown class)<br />", upn, className);

            // If the student's already in the class...
            // (Note, this only checks against the original list of students in classes;
            // an error will rightly be thrown if the imported XML contains duplicates)
            } else if (studentsInClasses[thisClass.Id].Contains(thisStudent.Id)) {
              sb.AppendFormat("Skipped: {0} - {1} (already exists)<br />", upn, className);

            // Otherwise (we're good to add the student to the class)...
            } else {
              thisClass.Students.Add(thisStudent);
              sb.AppendFormat("Imported: {0} - {1}<br />", upn, className);
            }

            // Reset everything, ready for the next entry
            thisStudent = null;
            thisClass = null;
            upn = String.Empty;
            className = String.Empty;
            break;
        }
      }
    }
    ctx.SaveChanges();
  }
  return sb.ToString();
}

It takes 13.1 sec to process just over 21,000 student-class entries.

I'd very much appreciate any suggestions.

Answer 1

Hard to say what's causing the extra load here. I had a similiar task which initially took a very long time, but with some adjustments and the use of HashTables, i got it to run within a second (3000 entities created from a 80mb large xml-file).

I did however use XDocument (LINQ-TO-XML) for the task. Have a look at my question regarding performance: https://stackoverflow.com/questions/1629596/xpathselectelement-vs-descendants

Also note that you can take advantage of pre-compilated queries and other useful performance stuff when it comes to LINQ-TO-XML.

http://blog.dreamlabsolutions.com/post/2008/12/04/LINQ-to-XML-and-LINQ-to-XML-with-XPath-performance-review.aspx

Good luck! Mattias

Answer 2

If this were me I'd try commenting out the database insert element of the code, and time just the reading of the XML file.

When you've got performance issues and databases are involved, it is often the database element that is causing it, and in this case I'd suspect the same. Possibly the table has indexes and each insert takes a while to process.

The code looks good - one point I'd note is you try to avoid duplicates with the studentsInClasses lookup, but if you insert a new database value in your code, you should really update the studentsInClasses to reflect this, or you may get duplicate values.

LINQ to XML

I'd certainly say give LINQ to XML a try: it will reduce the amount of code, even if it might not solve your performance issues!

This is all the XML reading bit translated for you. You'll need a "using System.Xml.Linq;" at the top.

        // CHANGED: try LINQ to XML
        var doc = XDocument.Load(path);

        // find child elements
        foreach (XElement e in doc.Descendants("studentClassEntry"))
        {
            // get values
            string upn = e.Element("upn").Value;
            string className = e.Element("className").Value;

I would echo the warning about large files. See this article by Mike Taulty. He also has an excellent video tutorial here.