c# - 如何使用 LINQ to SQL 还原关联记录

Question

我有一个注册系统的数据库，其中有两个表，Person 和 Class，它们具有多对多关系。在它们之间，有一个名为 Enrollment 的连接表。以下代码显示了上述 Person 和 Enrollment 类:

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

}

还有一个DataContext的子类来表示数据库:

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

因此我可以检索 Person 的记录及其关联的 Enrollment 记录，如下所示:

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

我需要的是有一种方法可以在修改后将这些记录恢复到原始状态。我尝试了 DataContext.Refresh 方法，它对 Person 有效，但对 Enrollment 的相关记录无效。

在下面的示例中，我修改了 Enrollment 记录的 EnrollDate 属性，但在我调用 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);
            }                
        } 

有谁知道使用 LINQ to SQL 恢复关联记录的方法吗？

Answer 1

您必须刷新 dataContext 中 Enrollment 表的记录，如下所示。代码中需要添加两行。我已在评论中标记它们供您引用。

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

注意:唯一需要注意的是，它不会丢弃您添加到注册中的新记录。它只会使用数据库中已存在的行的最新值刷新 Enrollment 表的记录。