MSSQL: GetRelationsForTableAsync ignores CASCADE actions

[florianendrich]

If the source database is using eg. ON DELETE CASCADE in a foreign key constraint, it is completely ignored by the sync process.

Error Encountered
Working with the client database does not trigger delete cascade actions, thus resulting in FK exceptions

Problem Description
This code in GetRelationsForTableAsync:

`

        var commandRelations = @"
            SELECT f.name AS ForeignKey,
                constraint_column_id as ForeignKeyOrder,
                SCHEMA_NAME (f.schema_id)  AS SchemaName,
                OBJECT_NAME(f.parent_object_id) AS TableName,
                COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
                SCHEMA_NAME (reft.schema_id) AS ReferenceSchemaName,
                OBJECT_NAME (f.referenced_object_id)  AS ReferenceTableName,
                COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
            FROM sys.foreign_keys AS f
            INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
            INNER JOIN sys.tables reft on reft.object_id =  f.referenced_object_id
            WHERE OBJECT_NAME(f.parent_object_id) = @tableName AND SCHEMA_NAME(f.schema_id) = @schemaName";

`

needs to be fixed as it ignores all delete_referential_action_desc and update_referential_action_desc .

Suggested Fix
I made some quick and dirty diffs:

diff --git a/Projects/Dotmim.Sync.Core/Manager/DbRelationDefinition.cs b/Projects/Dotmim.Sync.Core/Manager/DbRelationDefinition.cs
index 1b5c35da..123ef00a 100644
--- a/Projects/Dotmim.Sync.Core/Manager/DbRelationDefinition.cs
+++ b/Projects/Dotmim.Sync.Core/Manager/DbRelationDefinition.cs
@@ -37,6 +37,16 @@ namespace Dotmim.Sync.Manager
         /// Gets or Sets the reference schema name.
         /// </summary>
         public string ReferenceSchemaName { get; set; }
+
+        /// <summary>
+        /// Gets or Sets the OnDeleteAction.
+        /// </summary>
+        public string OnDeleteAction { get; set; }
+
+        /// <summary>
+        /// Gets or Sets the OnUpdateAction.
+        /// </summary>
+        public string OnUpdateAction { get; set; }
     }

     /// <summary>

diff --git a/Projects/Dotmim.Sync.Core/Orchestrators/BaseOrchestrator.Schema.cs b/Projects/Dotmim.Sync.Core/Orchestrators/BaseOrchestrator.Schema.cs
index 68a78cee..9f7cd5da 100644
--- a/Projects/Dotmim.Sync.Core/Orchestrators/BaseOrchestrator.Schema.cs
+++ b/Projects/Dotmim.Sync.Core/Orchestrators/BaseOrchestrator.Schema.cs
@@ -460,7 +460,7 @@ namespace Dotmim.Sync
                     if (foreignColumns == null || foreignColumns.Count == 0)
                         continue;

-                    var schemaRelation = new SyncRelation(r.ForeignKey, schemaColumns, foreignColumns);
+                    var schemaRelation = new SyncRelation(r.ForeignKey, schemaColumns, foreignColumns, r.OnUpdateAction, r.OnDeleteAction);

                     schema.Relations.Add(schemaRelation);
                 }

diff --git a/Projects/Dotmim.Sync.Core/Set/SyncRelation.cs b/Projects/Dotmim.Sync.Core/Set/SyncRelation.cs
index b044de08..829456bf 100644
--- a/Projects/Dotmim.Sync.Core/Set/SyncRelation.cs
+++ b/Projects/Dotmim.Sync.Core/Set/SyncRelation.cs
@@ -30,6 +30,20 @@ namespace Dotmim.Sync
         [DataMember(Name = "cks", IsRequired = true, Order = 3)]
         public IList<SyncColumnIdentifier> Keys { get; set; } = [];

+
+        /// <summary>
+        /// Gets or Sets the relation name.
+        /// </summary>
+        [DataMember(Name = "da", IsRequired = true, Order = 4)]
+        public string OnDeleteAction { get; set; }
+
+        /// <summary>
+        /// Gets or Sets the relation name.
+        /// </summary>
+        [DataMember(Name = "ua", IsRequired = true, Order = 5)]
+        public string OnUpdateAction { get; set; }
+
+
         /// <summary>
         /// Gets or sets the ShemaFilter's SyncSchema.
         /// </summary>
@@ -46,6 +60,16 @@ namespace Dotmim.Sync
             this.Schema = schema;
         }

+        /// <inheritdoc cref="SyncRelation"/>
+        public SyncRelation(string relationName, IList<SyncColumnIdentifier> columns, IList<SyncColumnIdentifier> parentColumns, string OnUpdateAction = null, string OnDeleteAction = null)
+        {
+            this.RelationName = relationName;
+            this.ParentKeys = parentColumns;
+            this.Keys = columns;
+            this.OnDeleteAction = OnDeleteAction;
+            this.OnUpdateAction = OnUpdateAction;
+        }
+
         /// <inheritdoc cref="SyncRelation"/>
         public SyncRelation(string relationName, IList<SyncColumnIdentifier> columns, IList<SyncColumnIdentifier> parentColumns, SyncSet schema = null)
         {

diff --git a/Projects/Dotmim.Sync.SqlServer/Builders/SqlBuilderTable.cs b/Projects/Dotmim.Sync.SqlServer/Builders/SqlBuilderTable.cs
index 9a9067ba..f813f754 100644
--- a/Projects/Dotmim.Sync.SqlServer/Builders/SqlBuilderTable.cs
+++ b/Projects/Dotmim.Sync.SqlServer/Builders/SqlBuilderTable.cs
@@ -357,8 +357,21 @@ namespace Dotmim.Sync.SqlServer.Builders
                     stringBuilder.Append($"{empty} {parentColumnName}");
                     empty = ", ";
                 }
-
                 stringBuilder.Append(" ) ");
+
+                if (constraint.OnDeleteAction != null && constraint.OnDeleteAction != "NO_ACTION")
+                {
+                    stringBuilder.AppendLine("ON DELETE ");
+                    stringBuilder.Append(constraint.OnDeleteAction.Replace("_", " "));
+                    stringBuilder.Append(" ");
+                }
+
+                if (constraint.OnUpdateAction != null && constraint.OnUpdateAction != "NO_ACTION")
+                {
+                    stringBuilder.AppendLine("ON UPDATE ");
+                    stringBuilder.Append(constraint.OnUpdateAction.Replace("_", " "));
+                    stringBuilder.Append(" ");
+                }
             }

             string createTableCommandString = stringBuilder.ToString();
@@ -407,6 +420,8 @@ namespace Dotmim.Sync.SqlServer.Builders
                     SchemaName = (string)row["SchemaName"] == "dbo" ? string.Empty : (string)row["SchemaName"],
                     ReferenceTableName = (string)row["ReferenceTableName"],
                     ReferenceSchemaName = (string)row["ReferenceSchemaName"] == "dbo" ? string.Empty : (string)row["ReferenceSchemaName"],
+                    OnDeleteAction = (string)row["OnDeleteAction"],
+                    OnUpdateAction = (string)row["OnUpdateAction"]
                 }))
                 {
                     var relationDefinition = new DbRelationDefinition()
@@ -416,6 +431,8 @@ namespace Dotmim.Sync.SqlServer.Builders
                         SchemaName = fk.Key.SchemaName,
                         ReferenceTableName = fk.Key.ReferenceTableName,
                         ReferenceSchemaName = fk.Key.ReferenceSchemaName,
+                        OnDeleteAction = fk.Key.OnDeleteAction,
+                        OnUpdateAction = fk.Key.OnUpdateAction
                     };

                     relationDefinition.Columns.AddRange(fk.Select(dmRow =>

diff --git a/Projects/Dotmim.Sync.SqlServer/SqlManagementUtils.cs b/Projects/Dotmim.Sync.SqlServer/SqlManagementUtils.cs
index 40ce852c..b004faad 100644
--- a/Projects/Dotmim.Sync.SqlServer/SqlManagementUtils.cs
+++ b/Projects/Dotmim.Sync.SqlServer/SqlManagementUtils.cs
@@ -321,7 +321,9 @@ namespace Dotmim.Sync.SqlServer
                     COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
                     SCHEMA_NAME (reft.schema_id) AS ReferenceSchemaName,
                     OBJECT_NAME (f.referenced_object_id)  AS ReferenceTableName,
-                    COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
+                    COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName,
+                    f.delete_referential_action_desc AS OnDeleteAction,
+                    f.update_referential_action_desc AS OnUpdateAction
                 FROM sys.foreign_keys AS f
                 INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
                 INNER JOIN sys.tables reft on reft.object_id =  f.referenced_object_id

which are basically adding the needed sql columns to the query, adding necessary properties to the needed classes and adding a new constructor for SyncRelation .

those properties are then used in table creation on client side.

with those changes i successfully synced all my tables with cascade actions.

[florianendrich]

Just saw a discussion about that here: #1219
So i think this will not be "fixed"?