Fix for the sqlite composite primary key (compound key) auto incrementation issue.

EFCoreFix.cs

//...
// in the DbContext -> protected override void OnModelCreating(ModelBuilder modelBuilder)

// orverride the property which is the key that should be incremented automatically
// - or do it for all the keys that need that

if (this.Database.IsSqlite())
{
  // generally fix the sqlite composite primary key auto increment issue
  // see https://github.com/dotnet/efcore/issues/15497

  foreach (var propertyOfCompositeKey in modelBuilder.Model.GetEntityTypes()
    .Where(t => ThisContextEntityTypes.Contains(t.Name))
    .Select(t => t.FindPrimaryKey())
    .Where(pk => pk != null && pk.Properties.Count == 2)
    .SelectMany(pk => pk.Properties)
    .Where(pkp => pkp.ValueGenerated == Microsoft.EntityFrameworkCore.Metadata.ValueGenerated.OnAdd && pkp.ClrType == typeof(int)))
  {
    propertyOfCompositeKey.SetValueGeneratorFactory((p, t) => new CompositePrimaryKeyAutoIncrementValueGenerator());
  }


  // generally fix the sqlite DateTimeOffset support issue
  // SQLite does not have proper support for DateTimeOffset via Entity Framework Core, see the limitations
  // here: https://docs.microsoft.com/en-us/ef/core/providers/sqlite/limitations#query-limitations
  // To work around this, when the Sqlite database provider is used, all model properties of type DateTimeOffset
  // use the DateTimeOffsetToStringConverter
  // Based on: https://github.com/aspnet/EntityFrameworkCore/issues/10784#issuecomment-415769754
  // This is significantly slower but is sufficient for unit test purposes

  foreach (var dateTimeOffsetProperty in modelBuilder.Model.GetEntityTypes()
    .Where(t => ThisContextEntityTypes.Contains(t.Name))
    .SelectMany(t => t.GetProperties())
    .Where(p => p.ClrType == typeof(DateTimeOffset) || p.ClrType == typeof(DateTimeOffset?)))
  {
    dateTimeOffsetProperty.SetValueConverter(new DateTimeOffsetToStringConverter());
  }

  // generally fix the sqlite decimal support issue
  // SQLite does not have proper support for Decimal via Entity Framework Core, see the limitations
  // here: https://docs.microsoft.com/en-us/ef/core/providers/sqlite/limitations#query-limitations
  // To work around this, when the Sqlite database provider is used, all model properties of type Decimal
  // use the conversion to double

  foreach (var decimalProperty in modelBuilder.Model.GetEntityTypes()
    .Where(t => ThisContextEntityTypes.Contains(t.Name))
    .SelectMany(t => t.GetProperties())
    .Where(p => p.ClrType == typeof(decimal) || p.ClrType == typeof(decimal?)))
  {
    decimalProperty.SetProviderClrType(typeof(double));
  }
}


// and then generate the next key
//

  using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Linq.Expressions;
  using System.Reflection;
  using Microsoft.EntityFrameworkCore;
  using Microsoft.EntityFrameworkCore.ChangeTracking;
  using Microsoft.EntityFrameworkCore.Metadata;
  using Microsoft.EntityFrameworkCore.ValueGeneration;

  internal class CompositePrimaryKeyAutoIncrementValueGenerator : ValueGenerator<int>
  {
    private static readonly Dictionary<string, int> TrackedKeys = new Dictionary<string, int>();
    private readonly int increment;
    private readonly int seed;

    public CompositePrimaryKeyAutoIncrementValueGenerator(int seed = 1, int increment = 1)
    {
      this.seed = seed;
      this.increment = increment;
    }

    public override bool GeneratesTemporaryValues => false;

    public override int Next(EntityEntry entry)
    {
      if (entry is null)
      {
        throw new ArgumentNullException(nameof(entry));
      }

      var entityType = entry.Entity.GetType();
      var context = entry.Context;
      var trackedKey = context.ContextId.InstanceId.ToString().ToUpperInvariant() + "_" + entityType.FullName;

      // get the db - set with context.Set<TEntity>();

      var set = context.GetType().GetMethod("Set")?.MakeGenericMethod(entityType).Invoke(context, null);
      if (set is null)
      {
        throw new NotSupportedException($"The auto increment for the composite keys of {entityType} is not defined.");
      }

      var queryableMethods = typeof(Queryable)
        .GetMethods(BindingFlags.Static | BindingFlags.Public);
      var any = queryableMethods
        .First(m => m.Name == "Any" && m.ContainsGenericParameters && m.GetGenericArguments().Length == 1)
        .MakeGenericMethod(entityType);
      var anyResult = any.Invoke(set, new[] { set });
      if (anyResult != null && !(bool)anyResult)
      {
        // if set is empty ... !set.Any()

        if (!CompositePrimaryKeyAutoIncrementValueGenerator.TrackedKeys.ContainsKey(trackedKey))
        {
          // we have to keep track ourselves because the user could add multiple entities without saving the context.
          CompositePrimaryKeyAutoIncrementValueGenerator.TrackedKeys.Add(trackedKey, this.seed);
        }
      }
      else
      {
        // do this so the name of the composite primary key property that should be incremented can be dynamic

        // find the type in the ef mode definitions for this entity
        var modelType = context.Model.FindEntityType(entityType);

        // find the property that should be incremented
        var keyProperty = modelType.FindPrimaryKey().Properties.Single(pkp => pkp.ValueGenerated == ValueGenerated.OnAdd);

        // absolutely dynamic access to the last key value in the database as a start for incrementation
        // DbSet<Entity>.OrderBy(item => item.[KeyPropertyName]).Last();
        var itemParameterExpression = Expression.Parameter(entityType, "item");
        var ordered = queryableMethods
          .First(m => m.Name == "OrderBy" && m.ContainsGenericParameters && m.GetGenericArguments().Length == 2)
          .MakeGenericMethod(entityType, typeof(int))
          .Invoke(set,
            new[]
            {
              set, Expression.Lambda(typeof(Func<,>).MakeGenericType(entityType, typeof(int)), Expression.Property(itemParameterExpression, keyProperty.Name), itemParameterExpression)
            });
        var lastItem = queryableMethods
          .First(m => m.Name == "Last" && m.ContainsGenericParameters && m.GetGenericArguments().Length == 1)
          .MakeGenericMethod(entityType).Invoke(null, new[] { ordered });

        var lastKeyInTheDatabase = (int)keyProperty.GetGetter().GetClrValue(lastItem);

        if (!CompositePrimaryKeyAutoIncrementValueGenerator.TrackedKeys.ContainsKey(trackedKey))
        {
          // we have to keep track ourselves because the user could add multiple entities without saving the context.
          CompositePrimaryKeyAutoIncrementValueGenerator.TrackedKeys.Add(trackedKey, lastKeyInTheDatabase);
        }
        else if (lastKeyInTheDatabase > CompositePrimaryKeyAutoIncrementValueGenerator.TrackedKeys[trackedKey])
        {
          // only if the last key in the database beats out own tracked one (maybe the user added entities outside the program)
          // we need to adjust ourselves to that
          CompositePrimaryKeyAutoIncrementValueGenerator.TrackedKeys[trackedKey] = lastKeyInTheDatabase;
        }

        // increment the key with given value and return it as the new key value;
        CompositePrimaryKeyAutoIncrementValueGenerator.TrackedKeys[trackedKey] += this.increment;
      }


      return CompositePrimaryKeyAutoIncrementValueGenerator.TrackedKeys[trackedKey];
    }
  }