Cant sync deleted record on server

[Actii]

Hi there,
I have the following issue, using a bidirectional scope when I delete a record on my client (SQLite) and sync the databases, the record is not removed in the server(MSSQLS). How can I solve this issue?

edit: I'm currently using the latest version.

[Mimetis]

Are you using a CustomWhere clause ?
IF yes, you have probably forgot to add a specific clause to your where argument
See more here : https://dotmimsync.readthedocs.io/Filters.html#how-to-get-deleted-rows-in-your-where-clause

[Actii]

I'm not using a CustomWhere, but a Where. The Orchestrator that I'm using is the WebRemoteOrchestrator.

This this the scenario that I have:

Imagine that I have 3 records on a table.

1. First I sync A and B with the Server. Everything is equal a this point. So A and B as 3 records and the Server 3.
2. Then I remove a record from A(2 records) and sync it with the server(3). After the sync is complete A as 2 recs, B 3 recs (since I haven't synced it yet) and the Server 2. All is good.
3. So B is the one that will be synced now in order to stay up to date with everyone else. What I expect with this sync is to have the record that was deleted also be deleted in B. But this does not happen, after I sync B with the Server this record is not deleted. In the end I have.

Client A: 2 records
Client B: 3 records (It should have 2)
Server: 2 records

[Mimetis]

Can you share the code you are using, and a simple script to create your table on the server
I need to do the same test as you

[Actii]

Our scenario is a little complex, but we found out that this problem occurs even if we reduce it to a simple delete directly on SQL Server, behind the web orchestrator.
This is what happens: The records deleted directly (delete from tablename where oid = '879C607B-AFA4-46CA-8E3C-E3A6AA5D0BF6') on the SQL Server are not synced (deleted) on the SQL Lite client database.

[Mimetis]

I need a sample Github repository where you are able to reproduce the error, using the simplest setup as possible.
Otherwise, I can't help you

[Mimetis]

Here is my sample, if you want to use it as a template

I'm using this script https://github.com/Mimetis/Dotmim.Sync/blob/master/CreateAdventureWorks.sql as server database
I'm syncing one table (ProductCategory) in this scenario

 // Using the Progress pattern to handle progression during the synchronization
 var progress = new SynchronousProgress<ProgressArgs>(s =>
     Console.WriteLine($"{s.ProgressPercentage:p}:  " +
     $"\t[{s?.Source[..Math.Min(4, s.Source.Length)]}] " +
     $"{s.TypeName}: {s.Message}"));

 // Server provider
 var serverProvider = new SqlSyncProvider(DBHelper.GetDatabaseConnectionString(serverDbName));
 
 // Clients 1 & 2 providers
 var clientProvider1 = new SqliteSyncProvider(
     Path.GetRandomFileName().Replace(".", "").ToLowerInvariant() + ".db");
 var clientProvider2 = new SqliteSyncProvider(
     Path.GetRandomFileName().Replace(".", "").ToLowerInvariant() + ".db");

 var options = new SyncOptions { DisableConstraintsOnApplyChanges = true };

 var setup = new SyncSetup("ProductCategory");

 try
 {
     var agent1 = new SyncAgent(clientProvider1, serverProvider, options);
     var agent2 = new SyncAgent(clientProvider2, serverProvider, options);

     // Sync client 1 to create table and gell all product categories
     var result1 = await agent1.SynchronizeAsync(setup, progress: progress);
     Console.WriteLine(result1);
     // Total changes  uploaded: 0
     // Total changes  downloaded: 42
     // Total changes  applied on client: 42

     // Sync client 2 to create table and get all product categories
     var result2 = await agent2.SynchronizeAsync(setup, progress: progress);
     Console.WriteLine(result2);
     // Total changes  uploaded: 0
     // Total changes  downloaded: 42
     // Total changes  applied on client: 42

     // Add a product category on server
     var productCategoryId = await DBHelper.AddProductCategoryRowAsync(serverProvider);

     // Sync client 1 to get this new created server product category on client 1
     result1 = await agent1.SynchronizeAsync(setup, progress: progress);
     Console.WriteLine(result1);
     // Total changes  uploaded: 0
     // Total changes  downloaded: 1
     // Total changes  applied on client: 1

     // Sync client 2 to get this new created server product category on client 2
     result2 = await agent2.SynchronizeAsync(setup, progress: progress);
     Console.WriteLine(result2);
     // Total changes  uploaded: 0
     // Total changes  downloaded: 1
     // Total changes  applied on client: 1

     // Now delete server product category
     await DBHelper.DeleteProductCategoryRowAsync(serverProvider, productCategoryId);

     // Sync client 1 to sync the deleted product category from server
     result1 = await agent1.SynchronizeAsync(setup, progress: progress);
     Console.WriteLine(result1);
     // Total changes  uploaded: 0
     // Total changes  downloaded: 1
     // Total changes  applied on client: 1

     // Sync client 1 to sync the deleted product category from server
     result2 = await agent2.SynchronizeAsync(setup, progress: progress);
     Console.WriteLine(result2);
     // Total changes  uploaded: 0
     // Total changes  downloaded: 1
     // Total changes  applied on client: 1
 }
 catch (Exception e)
 {
     Console.WriteLine(e.Message);
 }
 Console.WriteLine("Sync Ended. Press a key to start again, or Escapte to end");

[Actii]

The problem was on SyncFilter. Just a simple change from "Join.Inner" to "Join.Left" and it syncs deleted records.
Thanks for your time.