DbUpdateConcurrencyException on first sync

[WhitenAND07]

Describe the bug

I have configured my api exactly following all the documentation and code that you have published.

This API connects to an Azure Sql.

The local application has also been configured following exactly the established steps.

Then when you run the app for the first time. When doing PullAsync the following exception occurs:

DbUpdateConcurrencyException. the database operation was expected to affect 1 row(s) but actually affected 0 row(s).

There is previously inserted data in the server database. I have seen that the concurrency error only occurs if the server data has the UpdateAt field reported.

To Reproduce

Steps to reproduce the behavior:

1. Init App first time.
2. Execute SyncronizeAsync

Expected behavior

Successfull Data Sync

What platforms?

DB Server: Azure SQL
Api: App Service Azure
App: .NET Maui

• Server:

  • Version of dotnet being used to compile: net8.0
  • Library versions:
    CommunityToolkit.Datasync.Server Version="8.0.1"
    CommunityToolkit.Datasync.Server.Abstractions Version="8.0.1"
    CommunityToolkit.Datasync.Server.EntityFrameworkCore Version="8.0.1"
    Microsoft.EntityFrameworkCore.SqlServer Version="8.0.8"
    Microsoft.EntityFrameworkCore.Tools Version="8.0.8"
  • What database are you using? Azure SQL
  • Where are you running the server? Azure

• Client:

  • What platform (Android, iOS, Windows, etc.) versions are you running on? IOS
  • Does it happen in an emulator / simulator, or only on a real device? In simulator and real device
  • Version of dotnet being used to compile? net8.0
  • .NET Runtime Environment (WPF, UWP, WinUI3, MAUI, etc.):net8.0
  • Datsync Toolkit NuGet versions? 8.0.0

Screenshots

If applicable, add screenshots to help explain your problem.

Additional context

There is previously inserted data in the server database. I have seen that the concurrency error only occurs if the server data has the UpdateAt field reported.

[adrianhall]

Just to clarify - the UpdatedAt field has a value in it (some time in the past?) I want to ensure I get the right test written to validate this one.

[WhitenAND07]

I explain the steps I have taken to clarify your question:

1. I have initialized the database.
2. I have deployed the API in an Azure App Service.
3. I have inserted data through the following script (2 items):

USE [DBAPPCOMMERCIAL]
GO

INSERT INTO [dbo].[TableExamplem]
           ([Id]
           ,[Name]
           ,[ImagePin]
           ,[Deleted]
           ,[UpdatedAt])
     VALUES
           ('40c5ef491d024cdfac22d9152fe238fe'
           ,'Item1'
           ,0
           ,SYSDATETIMEOFFSET())
GO

5. I have opened the app (.NET Maui) for the first time

6. SyncronizeAsync is executed

7. In the PullAsync method it gives DbUpdateConcurrencyException.

But, if in step 3 I run the script (2 items) without reporting the UpdateAt. Example:

USE [DBAPPCOMMERCIAL]
GO

INSERT INTO [dbo].[TableExamplem]
           ([Id]
           ,[Name]
           ,[ImagePin]
           ,[Deleted])
     VALUES
           ('40c5ef491d024cdfac22d9152fe238fe'
           ,'Item1'
           ,0)
GO

That is, UpdateAt is null

The PullAsync method retrieves the data correctly.

[adrianhall]

Follow up questions:

1. What does your CLIENT SIDE DbContext and model look like? C# code is preferred.
2. How exactly are you initializing and running the synchronization on the client side?
3. What does your SERVER SIDE DbContext and model look like?

An UpdatedAt on the server side is unexpected, and considered an error. Likely, I need to either exclude UpdatedAt or make them UnixEpoch if null. But I'll know what test to run once I get the answers to the above questions.

[WhitenAND07]

1. What does your CLIENT SIDE DbContext and model look like? C# code is preferred.

MyModel:

using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
 
namespace Example.Models
{
    public class MyModel : OfflineClientEntity
    {
        [Required, Unicode(false)]
        public string Nombre { get; set; }
    }
}

OfflineClientEntity:

using System;
using System.ComponentModel.DataAnnotations;
 
namespace Example.Models
{
    public abstract class OfflineClientEntity
    {
        [Key]
        public string Id { get; set; } = Guid.NewGuid().ToString("N");
        public DateTimeOffset? UpdatedAt { get; set; }
        public string? Version { get; set; }
        public bool Deleted { get; set; } = false;
    }
}

DbContext:

using System;
using Example.Utils;
using Example.Models;
using Microsoft.EntityFrameworkCore;
using CommunityToolkit.Datasync.Client.Offline;
using CommunityToolkit.Datasync.Client.Http;
using Newtonsoft.Json;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;
using Microsoft.EntityFrameworkCore.Metadata;
using System.Text;
 
namespace Example.DataAccess
{
    public class ClienteDBContext : OfflineDbContext
    {
        public DbSet<MyModel> MyModel=> Set<MyModel>();
 
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            string conexionDB = $"Filename={ConexionDB.DevolverRuta("clientes.db")}";
            optionsBuilder.UseSqlite(conexionDB);
        }
 
        protected override void OnDatasyncInitialization(DatasyncOfflineOptionsBuilder optionsBuilder)
        {
            HttpClientOptions clientOptions = new()
            {
                Endpoint = new Uri("https://apiExample.azurewebsites.net"),
            };
 
            _ = optionsBuilder.UseHttpClientOptions(clientOptions);
        }
 
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<MyModel>(entity =>
            {
                entity.HasKey(col => col.Id);
                entity.Property(col => col.Id).IsRequired().ValueGeneratedOnAdd();
                entity.Property(e => e.Version).IsConcurrencyToken();
            });
 
            base.OnModelCreating(modelBuilder);
        }
 
        public async Task SynchronizeAsync(CancellationToken cancellationToken = default)
        {
            try
            {
                PushResult pushResult = await this.PushAsync(cancellationToken);
                if (!pushResult.IsSuccessful)
                {
                    Console.WriteLine($"Push failed: {pushResult.FailedRequests.FirstOrDefault().Value.ReasonPhrase}");
                    throw new ApplicationException($"Push failed: {pushResult.FailedRequests.FirstOrDefault().Value.ReasonPhrase}");
                }
                else
                {
                    Console.WriteLine("Push ok.");
                }
 
                PullResult pullResult = await this.PullAsync(cancellationToken);
                if (!pullResult.IsSuccessful)
                {
                    Console.WriteLine($"Pull failed: {pullResult.FailedRequests.FirstOrDefault().Value.ReasonPhrase}");
                    throw new ApplicationException($"Pull failed: {pullResult.FailedRequests.FirstOrDefault().Value.ReasonPhrase}");
                }
                else
                {
                    Console.WriteLine("Pull ok.");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: Synchronization failed - " + ex.Message);
                throw new ApplicationException($"Synchronization failed: {ex.Message}", ex);
            }
        }
    }
 
    public class DbContextInitializer(ClienteDBContext context) : IDbInitializer
    {
        public async void Initialize()
        {
            _ = context.Database.EnsureCreated();
        }
 
        public Task InitializeAsync(CancellationToken cancellationToken = default)
            => context.Database.EnsureCreatedAsync(cancellationToken);
    }
}

2. How exactly are you initializing and running the synchronization on the client side?
   I initialize the Dbcontext in MauiProgram:

using CommunityToolkit.Maui;
using UraniumUI;
using Example.Services;
using Example.DataAccess;
using Example.Pages;
using Example.ViewModels.Pages.DashboardPage;
using Microsoft.EntityFrameworkCore;
using Microsoft.Data.Sqlite;
using Example.Popups;
using Example.Views;
using Example.ViewModels.Views;
using Microsoft.Extensions.Logging;
 
namespace Example
{
    public static class MauiProgram
    {
        public static MauiApp CreateMauiApp()
        {
            var builder = MauiApp.CreateBuilder();
            builder
                .UseMauiApp<App>() // Configura la app para usar la clase App como punto de entrada.
                .UseMauiCommunityToolkit() // Integra Maui Community Toolkit para funcionalidades adicionales.
                .UseUraniumUI() // Habilita el uso de UraniumUI para la app.
                .UseUraniumUIMaterial() // Aplica el tema Material Design a través de UraniumUI.
                .UseMauiMaps()
                .ConfigureFonts(fonts =>
                {
                    // Configura las fuentes personalizadas para la app.
                    fonts.AddFont("OpenSans-Light.ttf", "OpenSansLight");
                    fonts.AddFont("OpenSans-Regular.ttf", "OpenSansRegular");
                    fonts.AddFont("OpenSans-Semibold.ttf", "OpenSansSemibold");
                    fonts.AddFont("OpenSans-Bold.ttf", "OpenSansBold");
                })
                .ConfigureMauiHandlers(handlers =>
                {
#if ANDROID || IOS || MACCATALYST
                    handlers.AddHandler<Microsoft.Maui.Controls.Maps.Map, CustomMapHandler>();
#endif
                });
 
            // Registro de servicios para inyección de dependencias
            builder.Services.AddScoped<IDbInitializer, DbContextInitializer>();
            builder.Services.AddDbContext<ClienteDBContext>();
 
            builder.Services.AddTransient<DashboardPage>();
            builder.Services.AddTransient<DashboardPageViewModel>();
 
            // Registro de servicios para inyección de dependencias
            builder.Services.AddTransient<IPopupService, Services.PopupService>();
            builder.Services.AddSingleton<INavigationService, NavigationService>();
 
            builder.Services.AddTransient<DashboardPageViewModel>();
 
            // Construye la aplicación
            var app = builder.Build();
            App.ServiceProvider = app.Services;
 
            // Inicializa la base de datos
            InitializeDatabase();
 
            // Devuelve la aplicación ya construida
            return app;
        }
 
        private static void InitializeDatabase()
        {
            using IServiceScope scope = App.ServiceProvider.CreateScope();
            IDbInitializer initialitzer = scope.ServiceProvider.GetRequiredService<IDbInitializer>();
            initialitzer.Initialize();
        }
    }
}

And the running of SyncronizeAsync here DashboardPage:

using System.ComponentModel;
using System.Data.SqlTypes;
using System.Globalization;
using Example.DataAccess;
using Example.Models;
using Example.Pages.Dashboard;
using Example.Services;
using Example.ViewModels.Pages.DashboardPage;
using CommunityToolkit.Mvvm.Messaging;
using Microsoft.EntityFrameworkCore;
 
namespace Example.Pages
{
    public partial class DashboardPage : ContentPage, INotifyPropertyChanged
    {
 
        private readonly ClienteDBContext _dbContext;
 
        public DashboardPage(ClienteDBContext viewModel)
        {
            InitializeComponent();
            _dbContext = viewModel;
 
            // Acceso correcto al ServiceProvider estático
            var popupService = App.ServiceProvider.GetService<IPopupService>();
            if (popupService != null)
            {
                BindingContext = new DashboardPageViewModel(popupService);
            }
  
            Loaded += OnLoadedAsync;
        }
 
        private async void OnLoadedAsync(object sender, EventArgs e)
        {
                Console.WriteLine("Init Sincro Dashboard");
                await _dbContext.SynchronizeAsync(default);
        }     
    }
}

3. What does your SERVER SIDE DbContext and model look like?

Model:

using CommunityToolkit.Datasync.Server.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations;

namespace My.Datasync.Server.Models
{
    public class MyModel: EntityTableData
    {
        [Required,Unicode(false)]
        public string Nombre { get; set; }
    }
}

DbContext:

using Microsoft.EntityFrameworkCore;
using My.Datasync.Server.Models;

namespace My.Datasync.Server.Context
{
    public class DbAppContext : DbContext
    {
        public DbAppContext(DbContextOptions<DbAppContext> options) : base(options)
        {
        }
        public DbSet<MyModel> MyModel=> Set<MyModel>();

        public async Task InitializeDatabaseAsync()
        {
            await Database.EnsureCreatedAsync();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            string sequenceSufix = "_PkSeq";

            foreach (var entityType in modelBuilder.Model.GetEntityTypes())
            {
                foreach (var foreignKey in entityType.GetForeignKeys())
                {
                    foreignKey.DeleteBehavior = DeleteBehavior.Restrict;
                }

                var keys = entityType.GetKeys().Where(key => key.IsPrimaryKey());

                if (keys.Count() == 1)
                {
                    var key = keys.First();

                    if (key.Properties[0].ClrType == typeof(int))
                    {
                        var sequenceName = entityType.GetTableName() + sequenceSufix;

                        _ = modelBuilder.HasSequence<int>(sequenceName);

                        entityType.FindProperty(key.Properties[0].Name).SetHiLoSequenceName(sequenceName);

                        modelBuilder.Entity(entityType.ClrType, entity =>
                        {
                            SqlServerPropertyBuilderExtensions.UseHiLo(entity.Property<int>(key.Properties[0].Name), sequenceName);
                        });
                    }
                }
            }
        }
    }
}

[adrianhall]

I believe I understand what the issue is.

You have the Version property in the CLIENT-SIDE model registered as a concurrency token.
The Version property in the CLIENT-SIDE model is controlled by the SERVER and should not be used as a concurrency token.

I should probably have some error checking around those situations. You should NOT put any client-side checks on UpdatedAt or Version (such as concurrency checks or generation) - if you do, things will break.

[github-actions]

We have noticed this issue has not been updated within 21 days. If there is no action on this issue in the next 14 days, we will automatically close it.

[WhitenAND07]

Then the UpdatedAt field will always have a null value on the server.

I have tried using the tigger from the documentation but when the UpdatedAt field is informed I have the synchronization problem again.

[adrianhall]

I have not been able to reproduce this UNLESS the updatedAt property within the database is not set (which is an invalid state). Can you please provide an xUnit test (potentially as a PR) to show off the problem?

[github-actions]

We have noticed this issue has not been updated within 21 days. If there is no action on this issue in the next 14 days, we will automatically close it.

[github-actions]

This issue has been stale for 5 weeks and has been automatically closed.