Delete recursive: true with a ManyToMany relationship doesn't delete intermediate records
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?
Comments (4)
-
-
Account Deactivated I have same the issue how I can delete fields from StudentIncident??? Please
-
Issue
#111was marked as a duplicate of this issue. -
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); }
- Log in to comment
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.