LINQ to SQL多对多关系插入和删除问题

Question

我正在尝试在 Windows Phone 7.1 上使用 LINQ to SQL 建模以下关系：

create table x (id INTEGER PRIMARY KEY AUTOINCREMENT, text STRING);
create table y (id INTEGER PRIMARY KEY AUTOINCREMENT, text STRING);
create table z (id INTEGER PRIMARY KEY AUTOINCREMENT, 
                x_id INTEGER NOT NULL,
                y_id INTEGER NOT NULL, 
                FOREIGN KEY(x_id) REFERENCES x(id) ON DELETE CASCADE, 
                FOREIGN KEY(y_id) REFERENCES y(id) ON DELETE CASCADE);

因此，如果我在 z 表中有一条记录，则每当我删除 id 为 x_id 的 x 记录或 id 为 y_id 的 y 记录时，它都应该被删除。

我有以下 C# 代码（基于 http:// /msdn.microsoft.com/en-us/library/hh286405(v=vs.92).aspx 示例）：

namespace linq2sql.test.program
{
    public class MyDatabase : DataContext
    {
        public MyDatabase(string connectionString)
            : base(connectionString)
        { }

        public Table<X> Xs;
        public Table<Y> Ys;
        public Table<Z> Zs;
    }

    public class BaseTable : INotifyPropertyChanged, INotifyPropertyChanging
    {
        // boring interface implementation omitted for brevity
    }

    [Table]
    public class X : BaseTable
    {
        private int _id;
        [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
        public int Id
        {
            get { return _id; }
            set
            {
                if (_id != value)
                {
                    NotifyPropertyChanging("Id");
                    _id = value;
                    NotifyPropertyChanged("Id");
                }
            }
        }

        private EntitySet<Z> _zs;
        [Association(Storage = "_zs", OtherKey = "_xId", ThisKey = "Id", DeleteRule="CASCADE")]
        public EntitySet<Z> Zs
        {
            get { return this._zs; }
            set { this._zs.Assign(value); }
        }

        public X()
        {
            _zs = new EntitySet<Z>(
                new Action<Z>(this.attach_z),
                new Action<Z>(this.detach_z)
                );
        }

        private void attach_z(Z z)
        {
            NotifyPropertyChanging("Z");
            z.X = this;
        }

        private void detach_z(Z z)
        {
            NotifyPropertyChanging("Z");
            z.X = null;
        }
    }

    [Table]
    public class Y : BaseTable
    {
        // exactly the same as table X
    }

    [Table]
    public class Z : BaseTable
    {
        private int _id;
        [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
        public int Id
        {
            get { return _id; }
            set
            {
                if (_id != value)
                {
                    NotifyPropertyChanging("Id");
                    _id = value;
                    NotifyPropertyChanged("Id");
                }
            }
        }

        [Column]
        internal int _xId;

        private EntityRef<X> _x;
        [Association(Storage = "_x", ThisKey = "_xId", OtherKey = "Id", IsForeignKey = true)]
        public X X
        {
            get { return _x.Entity; }
            set
            {
                if (_x.Entity != value)
                {
                    NotifyPropertyChanging("X");
                    _x.Entity = value;

                    if (value != null)
                    {
                        _xId = value.Id;
                    }

                    NotifyPropertyChanged("X");
                }
            }
        }

        [Column]
        internal int _yId;

        private EntityRef<Y> _y;
        [Association(Storage = "_y", ThisKey = "_yId", OtherKey = "Id", IsForeignKey = true)]
        public Y Y
        {
            get { return _y.Entity; }
            set
            {
                if (_y.Entity != value)
                {
                    NotifyPropertyChanging("Y");
                    _y.Entity = value;

                    if (value != null)
                    {
                        _yId = value.Id;
                    }

                    NotifyPropertyChanged("Y");
                }
            }
        }
    }
}

我开始测试它并遇到两个可能相关的问题。以下插入代码有效：

using (MyDatabase db = new MyDatabase(KDBConnectionString))
{
    if (!db.DatabaseExists())
    {
        db.CreateDatabase();
    }

    X x = new X();
    db.Xs.InsertOnSubmit(x);

    Y y = new Y();
    db.Ys.InsertOnSubmit(y);

    Z z = new Z();
    x.Zs.Add(z);
    y.Zs.Add(z);
    db.SubmitChanges();

    Assert.IsTrue(db.Xs.Count() == 1, "no x");
    Assert.IsTrue(db.Ys.Count() == 1, "no y");
    Assert.IsTrue(db.Zs.Count() == 1, "no z");

    Assert.IsTrue(db.Xs.First().Zs.Count() == 1, "no z in x");
    Assert.IsTrue(db.Ys.First().Zs.Count() == 1, "no z in y");
}

但是我期望等效的这段代码却不起作用：

using (MyDatabase db = new MyDatabase(KDBConnectionString))
{
    if (!db.DatabaseExists())
    {
        db.CreateDatabase();
    }

    X x = new X();
    db.Xs.InsertOnSubmit(x);

    Y y = new Y();
    db.Ys.InsertOnSubmit(y);

    Z z = new Z() { X = x, Y = y };
    db.Zs.InsertOnSubmit(z);
    db.SubmitChanges();

    Assert.IsTrue(db.Xs.Count() == 1, "no x");
    Assert.IsTrue(db.Ys.Count() == 1, "no y");
    Assert.IsTrue(db.Zs.Count() == 1, "no z");

    Assert.IsTrue(db.Xs.First().Zs.Count() == 1, "no z in x"); // fails here
    Assert.IsTrue(db.Ys.First().Zs.Count() == 1, "no z in y");
}

级联删除不能按我的预期工作。这可行：

using (MyDatabase db = new MyDatabase(KDBConnectionString))
{
    if (!db.DatabaseExists())
    {
        db.CreateDatabase();
    }

    X x = new X();
    db.Xs.InsertOnSubmit(x);

    Y y = new Y();
    db.Ys.InsertOnSubmit(y);

    Z z = new Z();
    x.Zs.Add(z);
    y.Zs.Add(z);
    db.SubmitChanges();

    db.Zs.DeleteOnSubmit(z);
    db.SubmitChanges();

    Assert.IsTrue(db.Zs.Count() == 0, "z not deleted");
    Assert.IsTrue(x.Zs.Count() == 0, "z not removed from x");
    Assert.IsTrue(y.Zs.Count() == 0, "z not removed from y");
}

这不行：

using (MyDatabase db = new MyDatabase(KDBConnectionString))
{
    if (!db.DatabaseExists())
    {
        db.CreateDatabase();
    }

    X x = new X();
    db.Xs.InsertOnSubmit(x);

    Y y = new Y();
    db.Ys.InsertOnSubmit(y);

    Z z = new Z();
    x.Zs.Add(z);
    y.Zs.Add(z);
    db.SubmitChanges();

    db.Xs.DeleteOnSubmit(x);
    db.SubmitChanges();

    Assert.IsTrue(db.Zs.Count() == 0, "z not deleted"); // ...this works, so "CASCADE" parameter has some effect
    Assert.IsTrue(y.Zs.Count() == 0, "z not removed from y"); // ... but fails here, so something is off
}

我想我在外键定义中犯了一些简单的错误，但我自己找不到它，而且我的 Google-Fu 今天很弱。感谢所有帮助。

Answer 1

我已经解决了插入问题，可以在这里找到带有测试的示例代码：

https://gist.github。 com/1682648

显然不以我想要的方式支持删除。我已经尝试过使用 DeleteOnNull 属性，如问题 的答案中所建议的那样LINQ-To-SQL 和多对多关系删除，但它就是不起作用。