Mango SQL CE: DeleteRule="Cascade" not working

Question

I'm trying to setup a FK relationship between two columns that will delete all children in the Db when a parent row is deleted. My definitions look like:

    [Table]
    public class Parent
    {
        [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
        public int Id { get; set; }

        [Column]
        public string Dummy 
        {
            get { return "dummy"; }
            set { } 
        }

        private EntitySet<Child> _children;

        [Association(Name = "FK_Parent_Child", DeleteRule = "CASCADE", OtherKey = "ParentId", ThisKey="Id", Storage="_children")]
        public EntitySet<Child> Children 
        { 
            get 
            { 
                return _children; 
            } 
            set 
            {
                _children.Assign(value);
            } 
        }

        public Parent()
        {
            _children = new EntitySet<Child>(
                item => item.Parent = this,
                item => item.Parent = null);
        }
    }

    [Table]
public class Child
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
    public int Id { get; set; }

    [Column]
    public int? ParentId { get; set; }

    private EntityRef<Parent> _parent;
    [Association(Name="FK_Child_Parent", ThisKey = "ParentId", Storage = "_parent", OtherKey = "Id", IsForeignKey = true, DeleteRule="CASCADE")]
    public Parent Parent
    {
        get
        {
            return _parent.Entity;
        }
        set
        {
            var previousValue = _parent.Entity;
            if (previousValue != value || !this._parent.HasLoadedOrAssignedValue)
            {
                if (previousValue != null)
                    _parent.Entity = null;

                _parent.Entity = value;
                if (value != null)
                    ParentId = value.Id;
                else
                    ParentId = null;
            }
        }
    }

}

From what I can tell this seems implementation of FKs seems to work. Adding a parent row to the Db will automatically add child rows; selecting a parent row properly fills in the Children property with all related children.

I would also like to be able to delete a parent row in the database and have that delete also remove all related children. With this setup, when I delete a parent I get the error "The primary key value cannot be deleted because references to this key still exist. [ Foreign key constraint name = FK_Child_Parent ]".

It appears the DeleteRule="Cascade" isn't being honored, but I'm not sure why.

Extract the database file from the emulator, and script the constraints to check if the CASCADE rule has been created — ErikEJ, Mar 17 '12 at 10:20
I ran into a similar problem. After attaching a log listener to DataContext.Log i saw that there was no ON CASCADE DELETE constraint created. After playing with different combinations of the attribute properties, i gave up. With Linq2SQL for Windows Phone DeleteRule="CASCADE" is NOT creating the constraint inside the DB! :/ — rObiwahn, Sep 06 '12 at 09:48

balbelias · Answer 1 · 2014-02-28T16:57:40.493

I know it's very late, but I have had the same problem and this was the first post I found. All I want to say is that everything works.

You should probably not capitalize rule name. And set DeleteRule on parent entity.

Here is my working code.

Parent entity field.

    private EntitySet<ExerciseDataContext> _exercises = new EntitySet<ExerciseDataContext>();

    [Association(Name = Constants.ForeignKeysNames.KF_GROUP_EXERCISE, Storage = "_exercises", OtherKey = "GroupID", ThisKey = "ID", DeleteRule = "Cascade")]
    public ICollection<ExerciseDataContext> Exercises
    {
        get { return _exercises; }
        set { _exercises.Assign(value); }
    }

And child entity field.

private EntityRef<GroupDataContext> _group = new EntityRef<GroupDataContext>();

    [Association(Name = Constants.ForeignKeysNames.KF_GROUP_EXERCISE, IsForeignKey = true, Storage = "_group", ThisKey = "GroupID")]
    public GroupDataContext Group
    {
        get { return _group.Entity; }
        set { _group.Entity = value; }
    }

Hope it will help someone.

Mango SQL CE: DeleteRule="Cascade" not working

1 Answers1