The C# code below, using LINQ to Entities, aims to merge all Class
entities which have the same SubjectId
and the same Students
.
var sb = new StringBuilder();
var counter = 0;
using (var ctx = new Ctx())
{
var allClasses = ctx.Classes.Where(o => o.SubjectId > 0).OrderBy(o => o.Id).ToList();
foreach (var c in allClasses)
ctx.LoadProperty(c, o => o.Students);
// For each class...
for (var i = 0; i < allClasses.Count; i++)
{
var c = allClasses[i];
var duplicates = allClasses
.Where(o => o.SubjectId == c.SubjectId && o.Id != c.Id &&
o.Students.OrderBy(s => s.Id).Select(s => s.Id)
.SequenceEqual(c.Students.OrderBy(s => s.Id)
.Select(s => s.Id)))
.ToList();
// Does it have any duplicates?
if (duplicates.Count == 0)
continue;
ctx.LoadProperty(c, o => o.PeriodsTimetable);
// For each duplicate...
for (var j = 0; j < duplicates.Count; j++)
{
var d = duplicates[j];
// If the duplicate class has a timetable slot that's not also allocated to
// the class we are keeping, then allocate it (otherwise MySql will delete it).
ctx.LoadProperty(d, o => o.PeriodsTimetable);
var dt = d.PeriodsTimetable.ToList();
for (var k = 0; k < dt.Count; k++)
{
var dtp = dt[k];
var tpIsDuplicate = false;
foreach (var ctp in c.PeriodsTimetable)
{
if (dtp.TeacherId == ctp.TeacherId &&
dtp.PeriodTime == ctp.PeriodTime)
{
tpIsDuplicate = true;
}
}
if (!tpIsDuplicate)
dtp.ClassId = c.Id;
}
// Now update all other entities which reference the duplicate to instead
// reference the class we are keeping.
var dPeriods = ctx.Periods.Where(o => o.ClassId == d.Id).ToList();
for (var k = 0; k < dPeriods.Count; k++)
dPeriods[k].ClassId = c.Id;
var dAssessments = ctx.Assessments.Where(o => o.ClassId == d.Id).ToList();
for (var k = 0; k < dAssessments.Count; k++)
dAssessments[k].ClassId = c.Id;
var dSeatingPlans = ctx.SeatingPlans.Where(o => o.ClassId == d.Id).ToList();
for (var k = 0; k < dSeatingPlans.Count; k++)
dSeatingPlans[k].ClassId = c.Id;
var dMerits = ctx.Merits.Where(o => o.ClassId == d.Id).ToList();
for (var k = 0; k < dMerits.Count; k++)
dMerits[k].ClassId = c.Id;
sb.Append(d.LongName).Append(" merged with ").Append(c.LongName).Append(".<br />");
// Finally, delete the duplicate
ctx.Classes.DeleteObject(d);
allClasses.Remove(d);
counter++;
}
}
ctx.SaveChanges();
lblOutput.Text = "<b>Merged " + counter + " duplicate classes.</b><br /><br />" + sb.ToString();
}
For an idea of scale, there are about 1600 classes with 30 students each, and counter
comes out at just over 300.
I'd really appreciate any suggestions.