HiLo Id generation for Sqlite

[YeskaNova]

I'm working on a project which I try to keep Db-Agnostic as much as possible. For practical reasons, my first line of unit testing is Sqlite only. I need to use HiLo for performance reasons, I'm using it for SQL sever, but because there is no HiLo strategy for sqlite, I'm running my unit tests with Identity strategy.

It would be great if we can UseHiLo for sqlite, which would let us run the unit tests in a near-production situation.

If there is no Sequence in Sqlite, why not to use just a simple Table to store the HiLo values?

[raymens]

Linking #302 just in case to show there has been demand for something similar before.

[cesarsouza]

+1, I also support that this would have been a nice addition to the Sqlite provider. In my case, I am mostly interested in using Sqlite for unit tests, so for the time being, I've managed to workaround this issue by writing a small utility class:

public static class Sequence
{
    public static async Task<long> GetNextAsync<TEntity>(ApplicationDbContext context)
    {
        var seq = new Sequence<TEntity>();
        context.Add(seq);
        await context.SaveChangesAsync();
        long id = seq.Id;
        context.Remove(seq);
        return id;
    }
}

public class Sequence<TEntity>
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long Id { get; set; }
}

Using this class, I am able to call GetNextAsync<TEntity>() to obtain an unique ID that I could then use as a replacement for the sequence ID in my tests. I've used a generic argument on Sequence so I could setup different sequences for different entities during the OnModelCreating call. I've also added some checks to use this method only when the database is configured to use InMemory/Sqlite in memory so they are only actually used during unit/integration tests. Maybe this could even be used in production to overcome the impedance between the tests and the production environment while the EF team decides to work/not work on this issue as it could maybe be a workaround for SQLServer instances prior to the 2014 version but I haven't 100% tested this approach in all my tests yet.

I also normally call this method from within a transaction so the call to SaveChangesAsync is not persisted in case there is an error down the chain.

If you need to apply different configurations while relying on an ApplyConfigurationsFromAssembly from your OnModelCreating like me, you can add a call to modelBuilder.HasAnnotation("IsInMemory", true); in OnModelCreating so that it should be possible to detect whether the setup is running in memory or not by evaluating builder.GetInfrastructure().ModelBuilder.Metadata.FindAnnotation("IsInMemory") in the IEntityTypeConfiguration implementations. I am not sure if there is another more straightforward way to do this, but for the time being it seems to be working for me.