Importing XML to a database using LINQ to Entities

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.

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
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);

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

Student thisStudent = null;
Class thisClass = null;

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

case XmlNodeType.Text:
// We're reading text (what's between the <nodeName> and </nodeName> tags)
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:

// 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 {
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.

• What's taking 13 seconds? The parsing or the whole method? How big is your existing database? I suspect a lot of that time is coming from the fact that you're preprocessing the Classes and Students tables twice essentially before you even start reading. Mar 18, 2012 at 17:58

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

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