Delete Cascade not working

Question

I have two tables in my database, one for recipes and one for their ingredients. When a particular recipe is deleted, I want all its ingredients gone as well. I have declared a one to many relationship with cascade attribute set but when I delete some recipe it does not delete the relevant ingredients.

Here are my tables:

    public class Recipe_Model
    {

        [PrimaryKey AutoIncrement]
        public int RecipeID { get; set; }
        public string RecipeName { get; set; }
        public double RecipeCost { get; set; }
        public double ServingsNo { get; set; }
        public double CostPercent { get; set; }
        public double SellingPrice { get; set; }
        public double CostPerServing { get; set; }

        [OneToMany(CascadeOperations = CascadeOperation.All)]      // One to many relationship with Ingredients
        public ObservableCollection<Ingredients_Model> Ingredients { get; set; }
    }

    public class Ingredients_Model
    {
        [PrimaryKey AutoIncrement]
        public int IngredientID { get; set; }

        [ForeignKey(typeof(Recipe_Model))]
        public int RecipeID { get; set; }

        public string IngredientName { get; set; }
        public string UsedUnit { get; set; }
        public string PurchasedUnit { get; set; }
        public double QuantityUsed { get; set; }
        public double QuantityPurchased { get; set; }
        public double PurchasePrice { get; set; }
        public double IngredientCost { get; set; }
    }

This is my delete operation:

    public void DeleteRecipe()
    {
        using (SQLiteConnection database = DependencyService.Get<ISQLite>().GetConnection())
        {
            var recipe = database.Get<Recipe_Model>(RecipeID);
            database.Delete(recipe, true);
        }
    }

What am I doing wrong?

Answer 1

Cascade operations only work for objects in memory. In your specific scenario, you are obtaining a single object from database via Get method and the cascade operations will delete all in-memory relationships, which is currently nothing because Ingredients property is null.

If you don't already have the objects in memory, it doesn't make sense to load them just to get the identifiers to delete them, which is exactly what cascade deletion do:

// This would work as it loads children to memory, but it's inefficient
var recipe = database.GetWithChildren<Recipe_Model>(RecipeID);
database.Delete(recipe, true);

Instead, I'd recommend you to delete them manually:

database.Execute("DELETE FROM [Ingredients_Model] WHERE [RecipeID] == ?", recipe.Id);
database.Delete(recipe);