Delete recursive: true with a ManyToMany relationship doesn't delete intermediate records

[Dustin Friel]

I have a Students and Incidents defined like

public class Incident {
    [PrimaryKey]
    [AutoIncrement]
    public int Id { get; set; }

    [ManyToMany(typeof(StudentIncident))]
    public List<Student> Students { get; set; }

}

public class Student {
    [PrimaryKey]
    [AutoIncrement]
    public int Id { get; set; }

    [ManyToMany(typeof(StudentIncident))]
    public List<Incident> Incidents { get; set; }

}

public class StudentIncident
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    [ForeignKey(typeof(Student))]
    public int StudentId { get; set; }

    [ForeignKey(typeof(Incident))]
    public int IncidentId { get; set; }

}

When I call InsertWithChildren (item, recursive: true); of an Incident with Students all the data is inserted into the three tables correctly correctly.

When I call Delete (item, recursive: true); of an Incident or Student the applicable row in the intermediate table (StudentIncident) isn't removed. Note I don't want to the associated Incidents or Students deleted. But I was expecting the associated rows in the intermediate table to be removed.

Is that table something that needs to be handled manually on Delete? A mistake in how I'm defining the ManyToMany? Or a defect?

[Guillermo Gutiérrez]

Yes, it's a known issue. It's harmless because the ID won't be used by a new entity and they intermediate elements will be deleted as soon as the relationship is updated, but it's something I could take a look in depth.

[Jurabek]

I have same the issue how I can delete fields from StudentIncident??? Please

[Guillermo Gutiérrez]

Issue #111 was marked as a duplicate of this issue.

[Marcel van der Heide]

So, for everyone facing this issue, I created a small snippet.
Obviously the code needs minor adjustments to integrate in other projects.

public virtual async Task DeleteComplexAsync(T model)
{

    var con = await _database.GetDatabaseConnectionAsync<T>().ConfigureAwait(false);

    // DeleteAsync with recursive set to true, works correctly for simple relations,
    // but does not delete intermediate tables. Also, other entities are deleted without calling there 
    // repositories, which could bring problems in the future.
    // Therefore we delete all child with the following steps:
    //
    // 1. Fetch all child entities (which would also be necessary for SQLite extensions, so no loss here)
    // 2. Loop over all properties and look for relations, which are not null
    // 2.1. Check if property is set for cascade deletion
    // 2.2. Fetch the entity type
    // 2.3. With that, get the repo for that type
    // 2.4. Get the DeleteComplexMethod
    // 2.5. Invoke the DeleteComplexMethod for that child (OneToOne, ManyToOne) or children (ManyToMany, OneToMany)
    // 2.6. Set the property to null
    // 3. With properties set to null, we need to update the entity so that intermediate tables will be deleted
    // 4. Finally, delete the current entity

    var hadChildren = false;

    // Fetch data
    await GetChildrenAsync(model);

    //1. Loop over all properties and look for relations
    foreach (var prop in model.GetType().GetTypeInfo().DeclaredProperties)
    {
        // Get all relationship attributes in a single call to prevent multiple iterations
        var attribute = prop.GetCustomAttribute<SQLiteNetExtensions.Attributes.RelationshipAttribute>();

        if (attribute == null 
            || !string.IsNullOrWhiteSpace(attribute.InverseProperty) 
            || !string.IsNullOrWhiteSpace(attribute.InverseForeignKey))
            continue; // Not an relation

        var curValue = prop.GetValue(model);
        if (curValue == null)
            continue; // Does not have a value

        // Check if children should be deleted
        if (attribute.IsCascadeDelete)
        {
            var entityType = prop.GetEntityType(out var enclosedType);

            // Build the target repo type and fetch the repo
            var repoType = typeof(IRepository<>).MakeGenericType(entityType);
            var repo = Locator.Current.GetService(repoType);

            // Get and call the DeleteComplexAsync method from the repo
            // Reflection is needed here because of variance
            var delComplexMethod = typeof(GenericRepository<>).MakeGenericType(entityType).GetMethod(nameof(DeleteComplexAsync))!;
            if (enclosedType == EnclosedType.None)
            {
                var delTask = (Task) delComplexMethod.Invoke(repo, new[] { curValue });
                await delTask.ConfigureAwait(false);
            }
            else
            {
                foreach (var entry in (IEnumerable)curValue)
                {
                    var delTask = (Task) delComplexMethod.Invoke(repo, new[] { entry });
                    await delTask.ConfigureAwait(false);
                }
            }
        }

        // Set field value to null, this should also clear intermediate tables -> https://www.mobilemotion.eu/?p=2224
        if (prop.GetCustomAttribute<NotNullAttribute>() == null)
            prop.SetValue(model, null);

        hadChildren = true;
    }

    if (hadChildren)
        await UpdateComplexAsync(model);

    // Delete the current entity
    await con.DeleteAsync(model, true).ConfigureAwait(false);
}

‌