SQLite extension for xamarin.forms : delete on cascade not working, but the the code compiles with no errors

Question

I'm using SQLiteNetExtensions to create a foreign key from one table to another so I can delete on cascade. I followed the documentation on: https://bitbucket.org/twincoders/sqlite-net-extensions,

but when I delete a record on my Missions Table, the records on my Locations Table that reference that primary key don't get deleted,

am I missing something?

These are my tables:

 using SQLiteNetExtensions.Attributes;

 public class Locations 
{
    [PrimaryKey, AutoIncrement]
    public int locationId { get; set; }

    [ForeignKey(typeof(Missions))]
    public int missionId { get; set; }

}

and :

using SQLiteNetExtensions.Attributes;

public class Missions
{
    [PrimaryKey, AutoIncrement]
    public int missionId { get; set; }

    public String missionName { get; set; }


    [OneToMany(CascadeOperations = CascadeOperation.CascadeDelete)]
    public List<Locations> Locations { get; set; }
}

Also this is the delete method used to delete records from the missions table:

 public Task<int> DeleteMyMissionAsync(Missions myMission)
    {
        return database.DeleteAsync(myMission);
    }

Is there any way to check what this attributes from the SQLite extension are doing? my code compiles and runs, so I don't know where the mistake is.

I tried also adding "using SQLiteNetExtensionsAsync" but this didn't make any difference.

EDIT: Changed my delete method to :

public Task DeleteMyMissionAsync(Models.MyCreatedMissions myMission)
    {

        return database.DeleteAsync(myMission, recursive: true); 

    }

it runs but when I delete a record in the Missions table referenced by my Locations table, it only deletes that record and all the records in the Locations table that reference that record still exist.

any ideas?

I dug this line out the ReadMe for the project: Two different methods are provided for recursive deletion:`DeleteAll` and `Delete`. These methods already exist in vanilly SQLite-Net, just make sure to call the overloaded method with the `recursive` parameter set to `true`. It looks like you may have to use something like `database.Delete(myMission, recursive: true)` — Andrew, Sep 10 '18 at 19:31
you're right I set my method like this: public Task DeleteMyMissionAsync(Missions myMission){ return database.DeleteAsync(myMission, recursive: true); } , but now I'm getting this: "The best overload for 'Delete' does not have a parameter named 'recursive'". I think there might be a problem with my installed packages. — BLM, Sep 11 '18 at 02:21
That sounds like you're calling `Delete()` instead of `DeleteAsync()`. Can you check it? Also, verify that you're installed the Async version of SQLiteNetExtensions — redent84, Sep 11 '18 at 09:20
I'm using SQLiteNetExtensionsAsync.Extensions and calling DeleteAsync(). the code is running but the delete on cascade is not working still... — BLM, Sep 11 '18 at 16:11
Please don't add EDITs, just edit to be the best presentation. (But don't invalidate reasonable answers.) Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS/product & DDL, which includes constraints & indexes & tabular-formatted base table initialization. — philipxy, Sep 21 '19 at 01:46

SreelalIndia · Answer 1 · 2019-09-21T20:46:36.660

It works, make sure the object you pass to SQLiteNetExtensions.Extensions.WriteOperations.DeleteAll(..) function has all the child objects present in it.

Call should be like this

SQLiteNetExtensions.Extensions.WriteOperations.DeleteAll(db, new Missions[] { missionsObj}, true);

In this missionsObj.Locations must not be null and count not zero

Better if you can get the Missions objects like this

SQLiteNetExtensions.Extensions.ReadOperations.GetAllWithChildren<Missions>(db, null, true);

so it will get all its children, so you can pass this mission objects to the above delete method. and It should work

PS: assuming all the attributes for the one to many relationships are set properly

Please read the edit help re code blocks. Please use standard language, spelling & punctuation. — philipxy, Sep 21 '19 at 01:48

score 0 · Answer 2 · answered Mar 14 '20 at 02:14

I was using singleton instance of SQLiteAsyncConnection in Xamarin.Forms, but anyways had to call "PRAGMA foreign_keys=ON;" before each DeleteAsync call. Calling it once after opening the connection didn't work for me. No need for SQLiteNetExtensions library to overcome CASCADE DELETION "issue"

SQLite extension for xamarin.forms : delete on cascade not working, but the the code compiles with no errors

2 Answers2