I have the below implementation (classes provided below). I get an error SQLite does not support sequences.

If I remove these lines from CatalogTypeEntityTypeConfiguration, i don't get the error.

//builder.Property(ci => ci.Id)
//    .UseHiLo("catalog_type_hilo")
//    .IsRequired();

How do i solve?

CatalogType.cs

using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace Catalog.Api.Database.Entities
{
    public class CatalogType
    {
        public int Id { get; set; }
        public string Type { get; set; }
    }

    class CatalogTypeEntityTypeConfiguration
    : IEntityTypeConfiguration<CatalogType>
    {
        public void Configure(EntityTypeBuilder<CatalogType> builder)
        {
            builder.ToTable("CatalogType");

            builder.HasKey(ci => ci.Id);

            builder.Property(ci => ci.Id)
                .UseHiLo("catalog_type_hilo")
                .IsRequired();

            builder.Property(cb => cb.Type)
                .IsRequired()
                .HasMaxLength(100);
        }
    }
}

CatalogDbContext.cs

using Catalog.Api.Database.Entities;
using Microsoft.EntityFrameworkCore;

namespace Catalog.Api.Database
{
    public class CatalogDbContext : DbContext
    {
        public CatalogDbContext(DbContextOptions<CatalogDbContext> options) : base(options)
        {
        }

        public DbSet<CatalogType> CatalogTypes { get; set; }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);
            builder.ApplyConfiguration(new CatalogTypeEntityTypeConfiguration());
        }
    }
}

CatalogDatabaseFixture.cs

using Catalog.Api.Database;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using System.Data.Common;

namespace Catalog.Tests.Unit
{
    public class CatalogDatabaseFixture : IDisposable
    {
        private const string InMemConnectionString = "Data Source=:memory:";
        private DbConnection _connection;
        public CatalogDbContext CatalogContext { get; private set; }

        public CatalogDatabaseFixture()
        {
            _connection = new SqliteConnection(InMemConnectionString);
            _connection.Open();
            var options = new DbContextOptionsBuilder<CatalogDbContext>()
                .UseSqlite(_connection)
                .Options;
            CatalogContext = new CatalogDbContext(options);
            CatalogContext.Database.EnsureCreated();

        }

        public void Dispose()
        {
            if(CatalogContext != null)
            {
                CatalogContext.Dispose();
            }

            if (_connection != null)
            {
                _connection.Dispose();
                _connection = null;
            }
        }
    }
}

CatalogControllerTest.cs

    namespace Catalog.Tests.Unit
{
    public class CatalogControllerTest : IClassFixture<CatalogDatabaseFixture>, IDisposable
    {
        private CatalogDatabaseFixture _fixture;
        public CatalogControllerTest(CatalogDatabaseFixture fixture)
        {
            _fixture = fixture;            
        }

        public void Dispose()
        {
    _fixture.CatalogContext.CatalogTypes.RemoveRange(_fixture.CatalogContext.CatalogTypes.AsEnumerable());
            _fixture.CatalogContext.SaveChanges();
        }


        [Fact]
        public void AllAsync_ShouldExist_ControllerAndAction()
        {
            // Given
            var controller = new CatalogController(_fixture.CatalogContext);
            // When
            var response = controller.AllAsync();
        }

        [Fact]
        public void AllAsync_ShouldReturn_ListOfCatalogItems()
        {

        }

    }
}

CodePudding user response：

SQLite doesn't support sequences, and thus doesn't suppor the hi-lo pattern for key value generation. If you're trying to get the same model to work on SQLite and other database providers, you can conditionally exclude that line of configuration on SQLite. Something like this...

var useHiLo = !Database.IsSqlite();
builder.ApplyConfiguration(new CatalogTypeEntityTypeConfiguration(useHiLo));

if (useHiLo)
{
    builder.Property(ci => ci.Id)
        .UseHiLo("catalog_type_hilo")
        .IsRequired();
}

This will use SQLite's AUTOINCREMENT feature for key value generation instead. (Which, ironically uses a table named sqlite_sequence to work.)