/// <summary> /// Method to delete association between two entities. /// </summary> /// <param name="definition">Entity manager delete dependency definition.</param> /// <param name="entityPK">The id of the entity.</param> /// <param name="depdencyPKs">The list of id of another entity.</param> /// <param name="search">The mode to search = or IN depend on value type.</param> /// <returns>Asynchronous task int query result. The result is the number of affected rows.</returns> public async Task <int> DeleteDependencyAsync(EntityManagerDeleteDependency definition, int entityPK, IEnumerable <int> depdencyPKs, string search = "IN") { int result = 0; string query = ""; string depTableName = definition.keyList.Replace("Id", "s"); log.Debug("---------------------------------------------------------------------------------"); log.Warn($"{GetType().Name}.{MethodBase.GetCurrentMethod().Name} : Deleting dependencies => {search} {definition?.Name}.{definition?.key}"); log.Debug($"{GetType().Name}.{MethodBase.GetCurrentMethod().Name} : Entity Primary Key => {entityPK}"); log.Debug($"{GetType().Name}.{MethodBase.GetCurrentMethod().Name} : Dependency Primary Keys => {depdencyPKs}"); log.Debug($"{GetType().Name}.{MethodBase.GetCurrentMethod().Name} : Dependency Table => {depTableName}"); try { // Becareful : SQLiteException maybe occurs whithout '. query = $"DELETE FROM {QN(definition.Name)}" + $" WHERE ({QN(definition.Name)}.{QN(definition.key)} = {Q(entityPK.ToString())}" // Delete all dependencies link if no safe list of primary keys is past. + ((depdencyPKs == null || depdencyPKs.Count() == 0) ? ")" : $" AND {QN(definition.Name)}.{QN(definition.keyList)} {search} ({string.Join(",", depdencyPKs)}))") // Delete all dependencies link that not match to a table row. + $" OR {QN(definition.Name)}.{QN(definition.keyList)} IN (" + $"SELECT {QN(definition.Name)}.{QN(definition.keyList)} FROM {QN(definition.Name)}" + $" LEFT JOIN {QN(depTableName)} ON ({QN(depTableName)}.{QN(definition.keyList)} = {QN(definition.Name)}.{QN(definition.keyList)})" + $" WHERE {QN(depTableName)}.{QN(definition.keyList)} IS NULL)"; log.Debug(query); #pragma warning disable EF1000 // Query is already generated by Linq. Becareful ! Linq on Linq cause SQLiteException. result = await(Connector as DbContext).Database.ExecuteSqlCommandAsync(query); #pragma warning restore EF1000 // Query is already generated by Linq. Becareful ! Linq on Linq cause SQLiteException. await SaveAsync(); } catch (SQLiteException se) { log.Error(query); log.Error(se.Output(), se); return(0); } catch (Exception se) { log.Fatal(query); log.Fatal(se.Output(), se); return(0); } log.Info($"{GetType().Name}.{MethodBase.GetCurrentMethod().Name} : Delete Affected Rows => {result}"); log.Debug("---------------------------------------------------------------------------------"); return(result); }
/// <summary> /// Method to clean association between two entities asynchronously. /// </summary> /// <param name="definition">Entity manager delete dependency definition.</param> /// <param name="entityPK">The id of the entity.</param> /// <param name="depdencyPKs">The list of id of another entity.</param> /// <returns>Asynchronous task int query result. The result is the number of affected rows.</returns> public async Task <int> CleanDependencyAsync(EntityManagerDeleteDependency definition, int entityPK, IEnumerable <int> depdencyPKs) { return(await DeleteDependencyAsync(definition, entityPK, depdencyPKs, "NOT IN")); }