How to Revert Associated Records using LINQ to SQL

Question

I have a database for an enrollment system and in it there are two tables, Person and Class, which have a many-to-many relationship. Between them, there is a join table named Enrollment. The following code shows the aforementioned Person and Enrollment classes:

[Table]
public class Person
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int Id { get; set; }

    [Column()]
    public string FirstName;

    [Column()]
    public string LastName;

    private EntitySet<Enrollment> _enrollment = new EntitySet<Enrollment>();

    [System.Data.Linq.Mapping.Association(Name = "FK_Enrollment_Person", Storage = "_enrollment", OtherKey = "StudentId", ThisKey = "Id")]
    public IList<Enrollment> Enrollment
    {
        get { return _enrollment; }
        set { _enrollment.Assign(value); }
    }
}

[Table]
public class Enrollment
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int Id { get; set; }

    [Column()]
    public int StudentId;

    [Column()]
    public int ClassId;

    [Column()]
    public DateTime EnrollDate;

}

Also there is a subclass of DataContext to represent the database:

[Database]
public class Database : DataContext
{
    public Database()
        : base(ConfigurationManager.ConnectionStrings["AppConnection"].ConnectionString)
    {
    }
}

So I can retrieve the records of Person and their associated Enrollment records as follows:

    Database db = new Database();
    var people = db.GetTable<Person>();

What I need is to have a way to revert those records back to their original states after modification. I tried DataContext.Refresh method and it did work on Person but did not work on the associated recrords of Enrollment.

In the following example I modify the EnrollDate property of the Enrollment records but they do not rollback after I call DataContext.Refresh.

        Database db = new Database();
        var people = db.GetTable<Person>();

        var enrolledPeople = people.Where(o => o.Enrollment.Count > 0);
        foreach(var person in enrolledPeople)
        {
            Console.WriteLine("Original Enroll Date:");
            foreach (var enrollment in person.Enrollment)
            {
                Console.WriteLine(enrollment.EnrollDate);
                enrollment.EnrollDate = DateTime.Now;
            }

            person.Enrollment.Add(new Enrollment() );

            db.Refresh(RefreshMode.OverwriteCurrentValues, person);

            Console.WriteLine("Enroll Date After being refreshed:");
            foreach (var enrollment in person.Enrollment)
            {
                Console.WriteLine(enrollment.EnrollDate);
            }                
        } 

Does anyone know an approach to revert associated records using LINQ to SQL?

Answer 1

Based on RBT's answer, I also added a piece of code to discard new-added records:

    Database db = new Database();
    var people = db.GetTable<Person>();
    var enrollments = db.GetTable<Enrollment>();

    var enrolledPeople = people.Where(o => o.Enrollment.Count > 0);
    foreach(var person in enrolledPeople)
    {
        Console.WriteLine("Original Enroll Date:");
        foreach (var enrollment in person.Enrollment)
        {
            Console.WriteLine(enrollment.EnrollDate);
            enrollment.EnrollDate = DateTime.Now;
        }

        person.Enrollment.Add(new Enrollment() );

// beginning of rollback
        db.Refresh(RefreshMode.OverwriteCurrentValues, person);
        db.Refresh(RefreshMode.OverwriteCurrentValues, enrollments);

        for (int i= person.Enrollment.Count-1; i>=0; i--)
        {
            if (person.Enrollment[i].Id == 0)
                person.Enrollment.RemoveAt(i);
        }
// end of rollback   

        Console.WriteLine("Enroll Date After being refreshed:");
        foreach (var enrollment in person.Enrollment)
        {
            Console.WriteLine(enrollment.EnrollDate);
        }                
    }    

Because the IDs always start from one in my case, records whose IDs are zero must not have been submitted to the database.

Answer 2

You will have to refresh the records of Enrollment table in your dataContext as shown below. There are two lines which need to be added in the code. I've marked them in comment for your reference.

   Database db = new Database();
    var people = db.GetTable<Person>();
    //Modified line # 1
    var enrollments = db.GetTable<Enrollment>();


    var enrolledPeople = people.Where(o => o.Enrollment.Count > 0);
    foreach(var person in enrolledPeople)
    {
        Console.WriteLine("Original Enroll Date:");
        foreach (var enrollment in person.Enrollment)
        {
            Console.WriteLine(enrollment.EnrollDate);
            enrollment.EnrollDate = DateTime.Now;
        }

        person.Enrollment.Add(new Enrollment() );

        db.Refresh(RefreshMode.OverwriteCurrentValues, person);
        //Modified line # 2
        db.Refresh(RefreshMode.OverwriteCurrentValues, enrollments);

        Console.WriteLine("Enroll Date After being refreshed:");
        foreach (var enrollment in person.Enrollment)
        {
            Console.WriteLine(enrollment.EnrollDate);
        }                
    } 

Note: Only caveat to this is that it will not discard the new record you have added into the enrollments. It will only refresh the records of the Enrollment table with the latest values for the rows which are already present in the database.

Answer 3

You need to refresh the enrollment objects. Enrollements are not "owned" by a person. They are not part of it. They are independent entities. Refresh works on the one entity you pass in.

Imagine Refresh refreshed an entire object graph. Databases tend to be cyclically connected. A single Refresh call could end up affecting all objects and change it's behavior randomly depending on what is loaded and mapped.