SQLiteExtensions UpdateWithChildrenAsync(..) not updating items with constraint

Question

I have an App in which I use SQLiteExtensions and following model:

[Table("SavedMeals")]
public class SavedMeal
{
    [PrimaryKey, AutoIncrement]     // Primary Key already indexed in Table
    public int Id { get; set; }

    [Unique]                        // Primary Key combined with ID
    public string Name { get; set; }

    [OneToMany(CascadeOperations = CascadeOperation.All)]
    public List<FoodSelection> SelectedFoods { get; set; } = new List<FoodSelection>();

    public DateTime DateLastSaved { get; set; } = DateTime.Now;
}

[Table("Foods")]
public class Food
{
    [PrimaryKey, AutoIncrement]     // Primary Key already indexed in Table
    public int Id { get; set; }

    [Unique]                        // Primary Key combined with ID
    public string Name { get; set; }

    public string Brand { get; set; }
}

[Table("FoodSelections")]
public class FoodSelection : Food
{
    [PrimaryKey, AutoIncrement]     // Primary Key already indexed in Table
    public new int Id { get; set; }

    [ForeignKey(typeof(SavedMeal))]                             // Specify the foreign key
    public int SavedMealId { get; set; }

    [ManyToOne(CascadeOperations = CascadeOperation.All)]      // Many to one relationship with SavedMeal
    public SavedMeal SavedMeal { get; set; }

    public FoodSelection() : base()
    {

    }

    public FoodSelection(Food foodItem) : base()
    {
        Id = foodItem.Id;
        Name = foodItem.Name;
        Brand = foodItem.Brand;
    }

    public bool IsSelected { get; set; } = false;

    private int _min = 0;
    public int Min
    {
        get => _min;
        set
        {
            _min = value;
            //NotifyPropertyChanged();
            NotifyPropertyChanged(nameof(IsValid));
        }
    }

    private int _max = 0;
    public int Max
    {
        get => _max;
        set
        {
            _max = value;
            //NotifyPropertyChanged();
            NotifyPropertyChanged(nameof(IsValid));
        }
    }

    public bool IsValid { get => Max >= Min; }
}

In my DatabaseService I have an Init-Method and an UpdateMeal(...)-Method:

static async Task Init()
{
    if (Database is not null)
    {
        return;
    }

    Database = new SQLiteAsyncConnection(Constants.DatabasePath, Constants.Flags);

    await Database.CreateTableAsync<FoodSelection>();
    await Database.CreateTableAsync<SavedMeal>();            
    await Database.CreateTableAsync<Food>();
}

public static async Task UpdateMeal(SavedMeal mealItem)
{
    await Init();

    await Database.UpdateWithChildrenAsync(mealItem);
}

A SavedMeal with a Name like "Lunch" consists of a List which is basically what the corresponding Lunch consists of (e.g. Chicken, Soup, Salad). Just as a background.

When I update a saved meal, I also want to update the food selection in the Database. I hope the relations and constraints are correct like this.

Anyway, when I do my UpdateWithAllChildrenAsync(...) with a meal my constraints are not updated and I can't see a change in table FoodSelections. I checked that the SavedMeal has the right Id (Primary Key) that is used for the Update and also the List has the new List I want to save.

I also tried with await Database.InsertOrReplaceWithChildrenAsync(mealItem, true); also without success.

Is there anything wrong in my Code that prevents the tables from being updated correctly?

Take a look at the "Cascade operations" section of the docs: https://bitbucket.org/twincoders/sqlite-net-extensions — redent84, Apr 28 '23 at 19:02
When I interpret it correctly, I would not even need to declare the Cascade Operation. The entire section reads as follows for me, just define your relationships and the rest is done for you. I don’t see my problem right now. SavedMeal should be OneToMany, as one SavedMeal has many FoodSelection-Objects. One FoodSelection Object can be in many Meals, but not with the same Parameters. One FoodSelection belongs to exactly one SavedMeal. So for me it is actually not the same Object and therefore I wanted it as ManyToOne from this perspective — OXO, Apr 29 '23 at 04:18
A few things that could go wrong: `InsertOrReplace` cannot be used with `AutoIncrement` keys. `FoodSelection` identifier doesn't look like it should be `AutoIncrement` as you're assigning it manually. Updating the `SavedMeal` won't automatically insert the `FoodSelection` items, and it won't update them unless the `recursive` parameter is set to `true`. — redent84, Apr 29 '23 at 13:29
I updated it a little bit, but as it is under a different subject, opened a new question under https://stackoverflow.com/questions/76137240/sqliteextensions-db-design-suggestions-with-attributes-and-unreferenced-elements — OXO, Apr 29 '23 at 16:23

SQLiteExtensions UpdateWithChildrenAsync(..) not updating items with constraint

0 Answers0