SQLite不支持序列的解决方案：EF Core HiLo配置报错处理

使用Entity Framework Core实现代码时，遇到错误提示：SQLite does not support sequences.。移除CatalogTypeEntityTypeConfiguration中的以下代码后错误消失：

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

请问该如何解决？

SQLite本身不支持数据库序列，而UseHiLo依赖序列生成ID，因此触发报错。可以通过以下几种方式适配不同数据库环境：

方法1：按数据库类型动态配置ID生成策略 #

修改CatalogTypeEntityTypeConfiguration，根据当前数据库提供者选择对应的ID生成方式：

public void Configure(EntityTypeBuilder<CatalogType> builder)
{
    builder.ToTable("CatalogType");
    builder.HasKey(ci => ci.Id);

    // 根据数据库类型切换配置
    if (builder.Metadata.Model.GetDbContextDependencies().DatabaseProvider.Name == "Microsoft.EntityFrameworkCore.Sqlite")
    {
        // SQLite使用自增主键
        builder.Property(ci => ci.Id)
            .ValueGeneratedOnAdd()
            .UseIdentityColumn()
            .IsRequired();
    }
    else
    {
        // 其他支持序列的数据库（如SQL Server）保留HiLo配置
        builder.Property(ci => ci.Id)
            .UseHiLo("catalog_type_hilo")
            .IsRequired();
    }

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

方法2：全局统一适配数据库特性 #

在CatalogDbContext的OnModelCreating中批量处理所有实体的ID配置：

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

    // 针对SQLite替换所有HiLo配置为自增
    if (Database.ProviderName == "Microsoft.EntityFrameworkCore.Sqlite")
    {
        foreach (var entityType in builder.Model.GetEntityTypes())
        {
            var primaryKey = entityType.FindPrimaryKey();
            if (primaryKey != null)
            {
                foreach (var property in primaryKey.Properties)
                {
                    if (property.ValueGenerated == ValueGenerated.OnAdd)
                    {
                        property.SetValueGenerated(ValueGenerated.OnAdd);
                        property.UseIdentityColumn();
                    }
                }
            }
        }
    }
}

方法3：仅在测试环境调整配置 #

如果仅测试用SQLite出现问题，可在测试项目的CatalogDatabaseFixture中覆盖配置：

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

    // 覆盖CatalogType的ID生成策略为SQLite支持的自增
    CatalogContext.ModelBuilder.Entity<CatalogType>()
        .Property(ci => ci.Id)
        .ValueGeneratedOnAdd()
        .UseIdentityColumn();

    CatalogContext.Database.EnsureCreated();
}

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.Db;

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()
        {

        }

    }
}

内容的提问来源于stack exchange，提问作者Tech with Thiru