Database not syncing

[sisaacks]

I followed the tutorial for the asp.net core web application. The process runs but data is not synced....I get zero for the output.
I am wondering if it is because I have enums. SQLite seeems to reference my enum type....where sql server has an int. I am not sure if this is my problem or not, was curious if you could give some assistance.

[sisaacks]

I should clarify....data was synced to my local database but data was not synced up to my remote database. When the app starts I had 1 record locally and 1 record remotely. The remote data was added to my local but the local was not added to my remote.
I have yet to find out why or find any errors

[VagueGit]

@sisaacks please include your sync configuration (use markdown) as this might make it easier for others to help.

[sisaacks]

@VagueGit is this what you mean? As I said, its brings data from the server but does not send data to the server

/* CLIENT SIDE CONFIGURATION */
 var serverOrchestrator = new WebClientOrchestrator("https://mywebapi.azurewebsites.net/api/sync");

  var clientProvider = new SqliteSyncProvider(connection.DatabasePath);

  var agent = new SyncAgent(clientProvider, serverOrchestrator, new string[] { "TableOne" });

  // Launch the sync process
  var s1 = await agent.SynchronizeAsync();
  // Write results
  Console.WriteLine(s1);

/* SEVER SIDE CONFIGURATION */
services.AddControllers();

// [Required]: To be able to handle multiple sessions
services.AddMemoryCache();

var connectionString = "Server = tcp:nfosqlserver.database.windows.net,1433; Initial Catalog =MyDb; Persist Security Info = False; User ID = MyName; Password = MyPassword; MultipleActiveResultSets = False; Encrypt = True; TrustServerCertificate = False; Connection Timeout = 30;";

var tables = new string[] { "TableOne" };

services.AddSyncServer<SqlSyncProvider>(connectionString, tables);

[sisaacks]

@VagueGit Essentially I had one record client side, and one record server side. I ran the sync and the record server side was added to the client but the client record was not added to server side.

The difference between me and the tutorial I am not using the change tracking. But I am perplexed as to why the record from the client did not get translated to the server. I was curious if it may have to do with enumerations, but why would the sync go one way but not the other.

[VagueGit]

That's the sort of thing @sisaacks however you should markdown your code to make it easier to read. Select your code with you mouse then click the angle-brackets on the toolbar above your comment

[sisaacks]

@VagueGit is that what you mean? Any assistance you can be with this would be great. I am not sure why its not syncing both directions

[sisaacks]

@VagueGit also my sql db is hosted on azure. I can read/write/update from MSSQL management studio.

[Mimetis]

On the local client, is the database already existing ? If Yes, did you have the record before creating the sync ? Just before going further, is it your situation ?

[sisaacks]

@Mimetis Yes the record was inserted prior to calling the sync. I am also tried SyncType.Reinitialize and it still did not upload

[Mimetis]

Initial rows on clients are not synced. If you want to sync them anyway, here is a workaround you can test;

// Make a first sync to get the schema from server, provision all tracking tables , 
// stored procedures, triggers and so on...
var s = await agent.SynchronizeAsync();

// Here, we should not have any uploaded rows, since it's the first sync.
// But, eventually we have all the rows from server.
Console.WriteLine(s);

// Get local schema
var schema = await agent.LocalOrchestrator.GetSchemaAsync();

// Update all rows on the client side that does not have any tracking rows
await agent.LocalOrchestrator.UpdateUntrackedRowsAsync(schema);

// Make as second sync.
// All local rows that were existing before first sync happened, will be uploaded correctly
s = await agent.SynchronizeAsync();

[sisaacks]

@Mimetis Is that something I would have to do every time a client logs in then? For example I could add many records on the mobile device, then decide to login, I would need to sync all the records I previously added on the mobile device.

[sisaacks]

@Mimetis also on this section

// Here, we should not have any uploaded rows, since it's the first sync.
// But, eventually we have all the rows from server.
Console.WriteLine(s);

wouldnt that pull the records from the server? That is what is currently happening. Unless I am misunderstanding your comment.

[sisaacks]

@Mimetis I tried the code you said above. The line below results in NULL because System.Exception: this type name varchar is not supported. I am not sure what is going on with this. I do not have any varchar in my remote. If my local sqlite wouldn't have varchar....so I am completely confused

// Get local schema var schema = await agent.LocalOrchestrator.GetSchemaAsync();

[Mimetis]

Your problem is that you have rows on the client before the first sync has been done. All the code I'm going to explain here is not necessary if you start a sync with an empty client database (or even with no database at all if you are on SQLite)

The requirement here is : I have a client database that needs to sync with a server, where I have existing rows, before making any sync.

You need to understand that DMS needs to track all the rows on the client to know if it needs to sync them or not.

If you have existing rows on the client, before the first sync, where DMS will create all the required stuff to be able to track new rows, the existing rows will not be tracked. So far during this first sync, the existing rows are not uploaded since they are not yet tracked.

// Make a first sync to get the schema from server, provision all tracking tables , 
// stored procedures, triggers and so on...
var s = await agent.SynchronizeAsync();

At least once this first sync is done, all the required stuff to track client rows is now created (stored proc, triggers and so on...) Indeed, the first Console.Writeline of the results will indicate that DMS has downloaded the rows from the server, but no rows from the client has been uploaded (as we mentioned, they are not yet tracked)

In your scenario you may have 1 row downloaded and 0 row uploaded

// Here, we should not have any uploaded rows, since it's the first sync.
// But, eventually we have downloaded all the rows from server.
Console.WriteLine(s);

Ok now, we have everything ready on the client database, and we are ready to use the DMS apis to track these existing rows, that needs to be uploaded to the server on the next sync:

// Get local schema
var schema = await agent.LocalOrchestrator.GetSchemaAsync();

// Update all rows on the client side that does not have any tracking rows
await agent.LocalOrchestrator.UpdateUntrackedRowsAsync(schema);

Now all the existing rows, not tracked on the client, will be marked as "To Be Uploaded". Then we can make a second sync to upload them:

// Make as second sync.
// All local rows that were existing before first sync happened, will be uploaded correctly
s = await agent.SynchronizeAsync();

Here, you may have : 0 row downloaed, 1 row uploaded.

That being said, this method is an edge situation where you are working with an existing client database that has rows existing before the first sync. Usually, the client database is empty (or even does not exists) before the first sync. When the client database is empty, there is no need to use this tweak, since the database is empty before the first sync.

Obviously, you don't need to use this code once you have made a successful sync and that everything works fine. The UpdateUntrackedRowsAsync should be called only one time, the first time you are making a sync with a client database that have existing rows before first sync.

Make sense ?

[sisaacks]

@Mimetis I must be misunderstanding something. I do have the situation where there is something on the client prior to the sync. This is a use case that can easily happen with my app, because users are not required to create an account...they can just choose to use the app locally. (meaning they do not have to create an account to use the server)

However let me show you in my code what I have done....I did what you said and on the code line "var schema = await agent.LocalOrchestrator.GetSchemaAsync()" above and I get an error: "Dotmim.Sync.SyncException: this type name varchar is not supported ---> System.Exception: this type name varchar is not supported"

Below is a copy paste of my code

var serverOrchestrator = new WebClientOrchestrator("https://webapi.azurewebsites.net/api/sync");              
var clientProvider = new SqliteSyncProvider(connection.DatabasePath);
var tables = new string[] { "Profile" };
var agent = new SyncAgent(clientProvider, serverOrchestrator, tables);

// Launch the sync process
var s1 = await agent.SynchronizeAsync();

// Write results
Console.WriteLine(s1);

// Get local schema for the FIRST client sync 
// THIS CALL RESULTS IN AN EXCEPTION: Dotmim.Sync.SyncException: this type name varchar is not supported ---> //System.Exception: this type name varchar is not supported
**var schema = await agent.LocalOrchestrator.GetSchemaAsync(); ;**

// Update all rows on the client side that does not have any tracking rows
await agent.LocalOrchestrator.UpdateUntrackedRowsAsync(schema);

// Make as second sync.
// All local rows that were existing before first sync happened, will be uploaded correctly
s1 = await agent.SynchronizeAsync();

// Write results
Console.WriteLine(s1);

[Mimetis]

Cn you show me the table definition of Profile ?

[sisaacks]

@Mimetis are you referring to the sql table definition? The sqlite is done under the hood on the mobile app for me.

Here is my class on the mobile side (which sqlite converts under the hood)

public class Profile : BaseModel, ICloneable
    {
        private string _name;
        private string _barrelTwistRate;
        private string _scopeName;
        private string _scopeZero;
        private string _scopeHeight;
        private string _notes;
        private BarrelTwistDirection _barrelTwistDirection;
        private bool _isMasterConfiguration;

        public RifleProfile()
        {
            BarrelTwistDirection = BarrelTwistDirection.Clockwise;
            UserId = App.UserId;
        }

        public Guid UserId { get; set; }
        public Guid? ParentId { get; set; }
        public int SortOrder { get; set; }
        public bool IsMasterConfiguration
        {
            get => _isMasterConfiguration;
            set => SetProperty(ref _isMasterConfiguration, value);
        }

        public string Notes
        {
            get => _notes;
            set => SetProperty(ref _notes, value);
        }

        public string Name
        {
            get => _name;
            set => SetProperty(ref _name, value);
        }

        public string BarrelTwistRate
        {
            get => _barrelTwistRate;
            set => SetProperty(ref _barrelTwistRate, value);
        }

        public string ScopeHeight
        {
            get => _scopeHeight;
            set => SetProperty(ref _scopeHeight, value);
        }

        public string ScopeName
        {
            get => _scopeName;
            set => SetProperty(ref _scopeName, value);
        }

        public string ScopeZero
        {
            get => _scopeZero;
            set => SetProperty(ref _scopeZero, value);
        }

        public BarrelTwistDirection BarrelTwistDirection
        {
            get => _barrelTwistDirection;
            set => SetProperty(ref _barrelTwistDirection, value);
        }

        public object Clone()
        {
            return new RifleProfile
            {
                Id = Id,
                UserId = UserId,
                Name = Name?.Trim(),
                BarrelTwistRate = BarrelTwistRate?.Trim(),
                ScopeHeight = ScopeHeight?.Trim(),
                ScopeName = ScopeName?.Trim(),
                ScopeZero = ScopeZero?.Trim(),
                BarrelTwistDirection = BarrelTwistDirection, 
                Notes = Notes?.Trim(),
                ParentId = ParentId,
                IsMasterConfiguration = IsMasterConfiguration,
            };
        }
    }

Here is the SQL Table

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Profile](
    [Id] [uniqueidentifier] NOT NULL,
    [UserId] [uniqueidentifier] NOT NULL,
    [ParentId] [uniqueidentifier] NULL,
    [Name] [char](35) NOT NULL,
    [IsMasterConfiguration] [bit] NOT NULL,
    [BarrelTwistRate] [char](5) NOT NULL,
    [ScopeHeight] [char](5) NOT NULL,
    [ScopeName] [char](35) NULL,
    [ScopeZero] [char](5) NOT NULL,
    [BarrelTwistDirection] [bit] NOT NULL,
    [Notes] [char](100) NULL,
 CONSTRAINT [PK_RifleProfile] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

[Mimetis]

Who is making the "under the hood" creation of your table on mobile ? Can you extract the schema of this table, from sqlite from your mobile app ?

I think the problem is coming from you sqlite database. In DMS we have this kind of verification

public override DbType ValidateDbType(string typeName, bool isUnsigned, bool isUnicode, long maxLength)
        {
            if (typeName.Contains("("))
                typeName = typeName.Substring(0, typeName.IndexOf("("));

            switch (typeName.ToLowerInvariant())
            {
                case "bit":
                    return DbType.Boolean;
                case "integer":
                case "bigint":
                    return DbType.Int64;
                case "numeric":
                case "real":
                    return DbType.Double;
                case "decimal":
                    return DbType.Decimal;
                case "blob":
                case "image":
                    return DbType.Binary;
                case "datetime":
                    return DbType.DateTime;
                case "text":
                    return DbType.String;

            }
            throw new Exception($"this type name {typeName} is not supported");
        }

If you sqlite table has another type DMS is rejecting it That's probably what is happening here, I guess

[sisaacks]

@Mimetis I have no clue how to view the SQLite data base on the mobile app. The sqlite nuget package I am using is sqlite-net-pcl

public interface IDataService
    {
        SQLiteConnection GetConnection(string databaseName);
        string GetPath(string databaseName);
    }

 IDataService SQLite
        {
            get
            {
                return DependencyService.Get<IDataService>();
            }
        }

DatabaseName = databaseName;
connection = SQLite.GetConnection(DatabaseName);
CreateTable<Profile>();

That above is all I am doing to create the sqlite db

[Mimetis]

You can create it from a console app, I guess ?

[sisaacks]

@Mimetis no...this is just a mobile app...no console....I am using Xamarin Cross Platform App

[Mimetis]

You can create a new console application project, get the relevant code, and generate a database...

[sisaacks]

@Mimetis I am guessing its the enumerations..... if i create a console app how would it be any different?? i mean ho would I access the schema?

[Mimetis]

On your machine; you will be able to open the sqlite database, right ?

[sisaacks]

@Mimetis okay I will give it a try. I will update once I try

[Mimetis]

Here is the code....

        static void Main(string[] args)
        {

            // Get an absolute path to the database file
            var databasePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "MyData.db");

            var db = new SQLiteConnection(databasePath);
            db.CreateTable<Profile>();

            Console.WriteLine("Hello World!");
        }
    }
    public class Profile
    {

        [PrimaryKey]
        public Guid Id { get; set; }
        public Guid UserId { get; set; }
        public Guid? ParentId { get; set; }
        public int SortOrder { get; set; }
        public bool IsMasterConfiguration { get; set; }
        public string Notes { get; set; }
        public string Name { get; set; }
        public string BarrelTwistRate { get; set; }
        public string ScopeHeight { get; set; }
        public string ScopeName { get; set; }
        public string ScopeZero { get; set; }
        public bool BarrelTwistDirection { get; set; }

    }

And once opened with a tool like DB Browser for SQLite, you can see that:

Seems sqlite-net is using some dbtype that are mostly aliases that are not supported by DMS

The source code from sqlite-pcl:

https://github.com/praeclarum/sqlite-net/blob/ff6507e2accd79ab60aa84a1039215884e4118fa/src/SQLite.cs#L2720-L2761

        public static string SqlType (TableMapping.Column p, bool storeDateTimeAsTicks, bool storeTimeSpanAsTicks)
        {
            var clrType = p.ColumnType;
            if (clrType == typeof (Boolean) || clrType == typeof (Byte) || clrType == typeof (UInt16) || clrType == typeof (SByte) || clrType == typeof (Int16) || clrType == typeof (Int32) || clrType == typeof (UInt32) || clrType == typeof (Int64)) {
                return "integer";
            }
            else if (clrType == typeof (Single) || clrType == typeof (Double) || clrType == typeof (Decimal)) {
                return "float";
            }
            else if (clrType == typeof (String) || clrType == typeof (StringBuilder) || clrType == typeof (Uri) || clrType == typeof (UriBuilder)) {
                int? len = p.MaxStringLength;

                if (len.HasValue)
                    return "varchar(" + len.Value + ")";

                return "varchar";
            }
            else if (clrType == typeof (TimeSpan)) {
                return storeTimeSpanAsTicks ? "bigint" : "time";
            }
            else if (clrType == typeof (DateTime)) {
                return storeDateTimeAsTicks ? "bigint" : "datetime";
            }
            else if (clrType == typeof (DateTimeOffset)) {
                return "bigint";
            }
            else if (clrType.GetTypeInfo ().IsEnum) {
                if (p.StoreAsText)
                    return "varchar";
                else
                    return "integer";
            }
            else if (clrType == typeof (byte[])) {
                return "blob";
            }
            else if (clrType == typeof (Guid)) {
                return "varchar(36)";
            }
            else {
                throw new NotSupportedException ("Don't know about " + clrType);
            }
        }

As you can see, sqlite-pcl is generating integer, float, varchar, varchar(length), bigint, time, datetime, and blob.

Today DMS is only allowing sqlite native types : bit, integer, bigint, numeric, real, decimal, blob, image, datetime andtext`.

I will maybe try to be compatible with that types in an upcoming version

[sisaacks]

@Mimetis you beat me too it. I was just about to send you a snap shot of the db. So.....is there a way I can make them compatible?

[sisaacks]

@Mimetis well....I dont think you support REAL....so my only option is to try a different version of sqlite...being that MySQL does not have on onboard mobile DB.

[Mimetis]

Nop

[Mimetis]

I will publish a beta version that will support these SQLITE types I will keep you updated once it's done and available on nuget

[sisaacks]

@Mimetis Wow!! Really?? I am kind of on a timeline....(not rushing you)....just trying to plan....when do you think you will be able to make that happen?

[Mimetis]

Probably in a couple of hours.

Before that, can you try this code, replacing the code that is failing (with the current version you have, no need to wait for the next beta)

        // Get local scope and extract schema
        var scope = await agent.LocalOrchestrator.GetClientScopeAsync();

        if (scope == null)
            throw new Exception("Scope is Null");

        if (scope.Schema == null)
            throw new Exception("Scope Schema is Null");

        // Update all rows on the client side that does not have any tracking rows
        await agent.LocalOrchestrator.UpdateUntrackedRowsAsync(scope.Schema);

And let me know if it's working ?

[Mimetis]

And once you have tested this method, you can even test with this beta version :

• https://www.nuget.org/packages/Dotmim.Sync.Core/0.7.3-beta-0610
• https://www.nuget.org/packages/Dotmim.Sync.Sqlite/0.7.3-beta-0610
• https://www.nuget.org/packages/Dotmim.Sync.SqlServer/0.7.3-beta-0610
• https://www.nuget.org/packages/Dotmim.Sync.MySql/0.7.3-beta-0610

[sisaacks]

@Mimetis can i install those through visual studio nuget package manager?
I am having a new error now that I never had before, its happening before I even get to your new code. Which I do not understand at all: Dotmim.Sync.Web.Client.HttpSyncWebException: Trying to pass a table-valued parameter with 12 column(s) where the corresponding user-defined table type requires 10 column(s)

The sql table has 12 columns and the sqlite does too....so confused

[sisaacks]

@Mimetis whats even more confusing...I put a break point on the sqliteconnection and the table has twelve columns......I am so confused.

[Mimetis]

I dont know, your scenario seems quite complex; and u did not provide any sample repro yet, so can't help you so far

[sisaacks]

@Mimetis I am not entirely sure how its complex, Xamarin and sqlite are quite common. I added your nuget packeges and now my app will not build. I am getting some strange error: Severity Code Description Project File Line Suppression State Error Can not resolve reference: Microsoft.Identity.Client, referenced by Microsoft.Data.SqlClient. Please add a NuGet package or assembly reference for Microsoft.Identity.Client, or remove the reference to Microsoft.Data.SqlClient. NightForceMobileApp.Android

So I removed all the nugets, added the old ones back and added a nuget for Microsoft.Identity.Client to Android. Now the project builds again. I am going to try this cycle one more time.

[sisaacks]

@Mimetis for some reason the nugets you have provided are only installing to Android. I am not sure why.

@Mimetis what kind of sample repro are you wanting? Xamarin Cross Platform? Let me know what you want and how to get it to you and I will get it done

[Mimetis]

If you want me to help you, you need to provide me a sample reproducing the bug you found in DMS Not your whole project, but a really REALLY simple project (preferably a console application) that reproduce the bug.

I've answered all your questions, with a lot of samples; I even create a small sample to reproduce an issue you had with sqlite-pcl-net (that is not a bug from DMS, by the way) where you saw where the problem came from. ( https://github.com/Mimetis/Dotmim.Sync/issues/510#issuecomment-805995438)

Once again, if the bug is coming from DMS I will help you; but only if i'm able to reproduce it, thanks to your explanation and sample repro, otherwise, I will stop trop try to understand based on the poor comments you put here

[sisaacks]

@Mimetis I will gladly make you a console application, I dont know how to get it to you, but the issue is syncing to the server so I will have to provide you the link to my server on azure. I do not understand your comment: "I will stop trop try to understand based on the poor comments you put here"

[Mimetis]

You can create a sample application project (acting as client) + a sample web api project (acting as server)

[sisaacks]

@Mimetis I am in the process of doing this now...I am just going to give you the link to my api...which in turn hits the database. Its just a development one...I can always change it. I am not sure how I would replicate it otherwise. Do I Just drag the project on here??

[sisaacks]

@Mimetis I tried to add the project and it says the file is too big....even in a compressed folder. I had to remove the solution files and such but you have the basic files I used in the attachment

I the console sample application, using the same code, I did not get the error. I do not understand why on the console app I do not get the error but on the mobile app I get the error. I added your new NUGETS to the sample project, but the CLIENT did not update the SERVER.

I was able to add the beta nuget packages to my PCL application. I ran and for some reason, on my mobile application I am getting the same error: Dotmim.Sync.Web.Client.HttpSyncWebException: Trying to pass a table-valued parameter with 12 column(s) where the corresponding user-defined table type requires 10 column(s).

Do you know of anyone that has done this with Xamarin and Azure?

TestingSqliteSchema - Copy.zip

[sisaacks]

@Mimetis I did some more testing. On the client side I changed table name to PeopleTable, which has 14 values and I get the exact same error as I do with ProfileTable which has 12 columns.

var clientProvider = new SqliteSyncProvider(connection.DatabasePath);
var tables = new string[] { "PeopleTable"};
var agent = new SyncAgent(clientProvider, serverOrchestrator, tables);

Error: Dotmim.Sync.Web.Client.HttpSyncWebException: Trying to pass a table-valued parameter with 12 column(s) where the corresponding user-defined table type requires 10 column(s).

In this error what is the client and which is the server? Meaning where is the "user defined table"? I assumed that was the Server?

[Mimetis]

Preamble

I do not understand your comment: "I will stop trop try to understand based on the poor comments you put here"

Well as I said earlier, if you don't take the time to explain your problem and don't take the time to create something I can use to reproduce your issue, I can't help you...

Here is a full post explaining how to resolve your issue. You will see that I share the source code. All the code here is explained in detail to allow you to reproduce everything.

This is the kind of post I need from anyone who needs help.

Yes, it took me almost 2 hours to setup the sample, test it, make a full post with all the comments and formatting it correctly to have something readable.

Yes.. almost 2 hours for you... for free...

Now you know what someone could expect when you ask for help, and please, never do again something like : "I followed the tutorial for the asp.net core web application. The process runs but data is not synced....I get zero for the output."

THAT BEING SAID, we can now see my solution and what I did to make everything working correctly...

Requirements

I guess you have several errors due to too much manipulations in tables / sql statement and so on...

First of all, if I refer to the SQL statement you described, I see that there is one column missing in your SQL : SortOrder I saw this column in your local sqlite database (extract from RifleProfile class), but not in the script you shared earlier.

Then, I think the best you can do is to restart everything, from scratch. When I say "from scratch", I mean you should deprovision everything on your server.

• Create a new database on server without any DMS additional things (like scope tables, stored procedures, triggers and so on...)
• If you can't delete your database server, you can use the method DeprovisionAsync() that will delete everything related to DMS

Once your database server is cleaned, you can do as well with your client database

• Delete the Sqlite database on your mobile
• If you can't, once again, call the DeprovisionAsync() method.

Note: More on this method here : https://dotmimsync.readthedocs.io/Provision.html#provision-deprovision

That being said, here is the solution I've implemented and that is working correctly:

Server side

From Sql Server, I've created a simple database containing your RifleProfile (with the additional column SortOrder that was missing from your script) And then, from this empty table, I've inserted 1 row.

USE [master]
GO 

if (exists (select * from sys.databases where name = 'ProfileDb'))
Begin
    ALTER DATABASE [ProfileDb] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [ProfileDb]
End
Create database [ProfileDb]
GO
Use [ProfileDb]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[RifleProfile](
    [Id] [uniqueidentifier] NOT NULL,
    [UserId] [uniqueidentifier] NOT NULL,
    [ParentId] [uniqueidentifier] NULL,
    [Name] [char](35) NOT NULL,
    [IsMasterConfiguration] [bit] NOT NULL,
    [BarrelTwistRate] [char](5) NOT NULL,
    [ScopeHeight] [char](5) NOT NULL,
    [ScopeName] [char](35) NULL,
    [ScopeZero] [char](5) NOT NULL,
    [BarrelTwistDirection] [bit] NOT NULL,
    [Notes] [char](100) NULL,
    [SortOrder] [int] NULL,
 CONSTRAINT [PK_RifleProfile] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
))
GO

Insert Into [RifleProfile] (Id, UserId, [Name], IsMasterConfiguration, BarrelTwistRate, ScopeHeight, ScopeName, ScopeZero, BarrelTwistDirection, SortOrder)
Values (newid(), newid(), 'John', 1, 'AAA1', '100', 'Default', '001', 1, 1)

The Server Web Api is pretty simple, exactly same as what we have in the documentation : https://dotmimsync.readthedocs.io/Web.html#server-side

public void ConfigureServices(IServiceCollection services)
{
    // [Required]: Handling multiple sessions
    services.AddMemoryCache();
    services.AddControllers();
    var connectionString = Configuration.GetSection("ConnectionStrings")["SqlConnection"];
    var tables = new string[] { "RifleProfile" };
    services.AddSyncServer<SqlSyncProvider>(connectionString, tables);
}

[ApiController]
[Route("api/[controller]")]
public class SyncController : ControllerBase
{
    private WebServerManager webServerManager;
    public SyncController(WebServerManager webServerManager) => this.webServerManager = webServerManager;

    [HttpPost]
    public async Task Post() => await webServerManager.HandleRequestAsync(this.HttpContext);

    [HttpGet]
    public async Task Get() => await webServerManager.HandleRequestAsync(this.HttpContext);
}

Client Side

On the client side, I used a lot of your code, and here is my version: By the way your method ShouldSeedData() was wrong, I had to fix it.

Then , the only thing I'm careful here is to begin with a fresh new database.

static async Task Main(string[] args)
{

    try
    {
        var databasePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "rifleprofile.db");

        var sqliteConnection = new SQLiteConnection(databasePath);
        sqliteConnection.DropTable<RifleProfile>();
        sqliteConnection.CreateTable<RifleProfile>();

        if (ShouldSeedData(databasePath))
            SeedData(databasePath);

        var serverOrchestrator = new WebClientOrchestrator("https://localhost:44333/api/sync");
        var clientProvider = new SqliteSyncProvider(sqliteConnection.DatabasePath);

        var agent = new SyncAgent(clientProvider, serverOrchestrator);

        // Launch the sync process
        var s1 = await agent.SynchronizeAsync();

        // Write results
        Console.WriteLine(s1);

        // Get local schema for the FIRST client sync 
        var scope = await agent.LocalOrchestrator.GetSchemaAsync();

        // Update all rows on the client side that does not have any tracking rows
        await agent.LocalOrchestrator.UpdateUntrackedRowsAsync(scope);

        // Make as second sync.
        // All local rows that were existing before first sync happened, will be uploaded correctly
        s1 = await agent.SynchronizeAsync();

        Console.WriteLine(s1);

    }
    catch (Exception ex)
    {

        Console.WriteLine(ex);
    }
}

public static bool ShouldSeedData(string databasePath)
{
    var sqliteConnection = new SQLiteConnection(databasePath);
    string query = string.Format("SELECT name FROM sqlite_master WHERE type='table' AND name='{0}';", nameof(RifleProfile));
    SQLiteCommand cmd = sqliteConnection.CreateCommand(query);
    var item = sqliteConnection.Query<object>(query);
    return item.Count > 0;
}

public static void SeedData(string databasePath)
{
    RifleProfile defaultRilfe = new RifleProfile()
    {
        Id = Guid.NewGuid(),
        ParentId = null,
        Name = "338 Lapua AI",
        ScopeName = "NF ATAC 7-35",
        ScopeHeight = "1",
        ScopeZero = "200",
        BarrelTwistDirection = BarrelTwistDirection.Clockwise,
        BarrelTwistRate = "10",
        SortOrder = 1,
        IsMasterConfiguration = true,
        UserId = new Guid("5e0a4c19-91a0-4aca-864f-4d6f09595465"),
        Notes = string.Empty
    };

    var sqliteConnection = new SQLiteConnection(databasePath);
    sqliteConnection.Insert(defaultRilfe);
}

And here is the output:

Synchronization done.
        Total changes  uploaded: 0
        Total changes  downloaded: 1
        Total changes  applied: 1
        Total resolved conflicts: 0
        Total duration :0:0:28.664
Synchronization done.
        Total changes  uploaded: 1
        Total changes  downloaded: 0
        Total changes  applied: 0
        Total resolved conflicts: 0
        Total duration :0:0:7.880

If I Check the SQLite database, my record from the server is here:

And If I check the server side, I have the record from the local SQLite client database:

And Now ?

Now we have everything setup correctly, we can use this code to make a traditional sync:

var serverOrchestrator = new WebClientOrchestrator("https://localhost:44333/api/sync");
var clientProvider = new SqliteSyncProvider(sqliteConnection.DatabasePath);

var agent = new SyncAgent(clientProvider, serverOrchestrator);

// Launch the sync process
var s1 = await agent.SynchronizeAsync();

// Write results
Console.WriteLine(s1);

That's all.

[sisaacks]

@Mimetis first thank you, I will try what you have said. I do not understand how my method "ShouldSeedData", I only want to seed data if the table didnt already exist.
In the example I sent you, SortOrder was included wasn't it? I noticed that it was missing.
I am sorry, I am not great at explaining, its actually due to a TBI. I didnt realized "from scratch" meant delete everything from front to end. I will work on trying this today.

I have one last question for you, if I only want to sync records for a specific userId, do I use a filter for this?

[Mimetis]

I have one last question for you, if I only want to sync records for a specific userId, do I use a filter for this?

Yes

[sisaacks]

@Mimetis thank you. I am slightly concerted my issue of not uploading the data may be something with Azure. But I have deleted everything and starting over. Again thank you, I will let you know my results.

[sisaacks]

@Mimetis there is one more question I have, a specific user scenario.
The scenario you helped me with above, the user has created local records, then they decide to create a server account and want to sync the local records to the server.

Now lets expand on that, say the user has already created a server account, but is not logged in, creates records locally and then logs in. Do I have to use the process above again or will records sync both directions?