EF Core中PostgreSQL数组类型适配SQLite时查询失败的解决方法

针对你遇到的SQLite无法翻译blog.Tags.Contains(tag)的问题，这里提供几种可行的解决方案：

方案1：自定义LINQ表达式翻译，适配逗号分隔字符串 #

由于SQLite中Tags字段存储为逗号分隔的字符串，我们可以将Contains调用翻译为SQLite支持的LIKE查询，同时避免部分标签匹配（比如避免"dot"匹配"dotnet"）。

步骤1：定义标记用的静态方法 #

创建一个仅用于标记EF需要翻译的调用的静态辅助类：

public static class SqliteArrayFunctions
{
    // 仅用于客户端逻辑，EF会将其翻译为SQL
    public static bool ArrayContains(string[] array, string element) 
        => array.Contains(element);
}

步骤2：修改业务查询逻辑 #

将BlogService中的查询替换为自定义方法调用：

public async Task<List<Blog>> FindBlogsWithTag(string tag)
{
    await using var context = await _factory.CreateDbContextAsync();
    return context.Blogs
        .Where(blog => SqliteArrayFunctions.ArrayContains(blog.Tags, tag))
        .ToList();
}

步骤3：注册自定义翻译器 #

在TestDbContext中添加自定义方法翻译逻辑，将ArrayContains转换为SQLite可执行的LIKE语句：

protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
    base.ConfigureConventions(configurationBuilder);

    // 注册自定义翻译器
    configurationBuilder.Services.AddSingleton<IMethodCallTranslatorProvider>(sp =>
        new SqliteArrayContainsTranslatorProvider(
            sp.GetRequiredService<SqliteMethodCallTranslatorProvider>()));
}

// 自定义翻译器实现
private class SqliteArrayContainsTranslatorProvider : IMethodCallTranslatorProvider
{
    private readonly SqliteMethodCallTranslatorProvider _innerProvider;

    public SqliteArrayContainsTranslatorProvider(SqliteMethodCallTranslatorProvider innerProvider)
    {
        _innerProvider = innerProvider;
    }

    public IEnumerable<SqlExpression> Translate(
        SqlExpression instance,
        MethodInfo method,
        IReadOnlyList<SqlExpression> arguments,
        IDiagnosticsLogger<DbLoggerCategory.Query> logger)
    {
        if (method.DeclaringType == typeof(SqliteArrayFunctions) && method.Name == nameof(SqliteArrayFunctions.ArrayContains))
        {
            // 构造安全的LIKE查询：用逗号包裹标签避免部分匹配
            var wrappedTags = SqlExpressionBuilder.Add(
                SqlExpressionBuilder.Add(new SqlConstantExpression(",", typeof(string)), arguments[0]),
                new SqlConstantExpression(",", typeof(string)));
            
            var wrappedTag = SqlExpressionBuilder.Add(
                SqlExpressionBuilder.Add(new SqlConstantExpression(",", typeof(string)), arguments[1]),
                new SqlConstantExpression(",", typeof(string)));

            return new[]
            {
                SqlExpressionBuilder.Like(
                    wrappedTags,
                    SqlExpressionBuilder.Add(
                        new SqlConstantExpression("%", typeof(string)),
                        SqlExpressionBuilder.Add(wrappedTag, new SqlConstantExpression("%", typeof(string))))
                )
            };
        }

        return _innerProvider.Translate(instance, method, arguments, logger);
    }
}

方案2：改用SQLite JSON数组存储 #

将Tags字段存储为JSON数组，利用SQLite的JSON函数进行查询，更接近PostgreSQL数组的行为。

修改TestDbContext的类型转换 #

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder
        .Entity<Blog>()
        .Property(blog => blog.Tags)
        .HasColumnType("TEXT")
        .HasConversion(
            strings => JsonSerializer.Serialize(strings, JsonSerializerOptions.Default),
            s => JsonSerializer.Deserialize<string[]>(s, JsonSerializerOptions.Default) ?? Array.Empty<string>()
        );
}

修改查询逻辑使用JSON函数 #

public async Task<List<Blog>> FindBlogsWithTag(string tag)
{
    await using var context = await _factory.CreateDbContextAsync();
    return context.Blogs
        .Where(blog => EF.Functions.JsonContains(blog.Tags, JsonSerializer.Serialize(new[] { tag })))
        .ToList();
}

方案3：直接改用PostgreSQL进行该测试 #

如果希望完全匹配生产环境的数据库行为，推荐使用Testcontainers自动启动PostgreSQL容器进行测试，无需修改业务逻辑：

[TestClass]
public class BlogServicePostgreSqlTests
{
    private static IContainer _postgresContainer;
    private static DbContextOptions<BlogContext> _dbContextOptions;

    [ClassInitialize]
    public static async Task ClassInitialize(TestContext context)
    {
        // 启动PostgreSQL测试容器
        _postgresContainer = new ContainerBuilder()
            .WithImage("postgres:15")
            .WithPortBinding(5432, true)
            .WithEnvironment("POSTGRES_USER", "test")
            .WithEnvironment("POSTGRES_PASSWORD", "test")
            .WithEnvironment("POSTGRES_DB", "blogdb")
            .Build();

        await _postgresContainer.StartAsync();

        // 配置DbContext
        _dbContextOptions = new DbContextOptionsBuilder<BlogContext>()
            .UseNpgsql($"Host=localhost;Port={_postgresContainer.GetMappedPublicPort(5432)};Database=blogdb;Username=test;Password=test")
            .Options;

        // 初始化数据库
        await using var db = new BlogContext(_dbContextOptions);
        await db.Database.EnsureCreatedAsync();
    }

    [TestMethod]
    public async Task FindBlogsWithTag_ShouldReturnMatchingBlogs()
    {
        // 准备测试数据
        await using var db = new BlogContext(_dbContextOptions);
        db.Blogs.Add(new Blog { Url = "https://example.com", Tags = new[] { "dotnet", "efcore" } });
        await db.SaveChangesAsync();

        var service = new BlogService(new DbContextFactory<BlogContext>(_dbContextOptions));

        // 执行查询
        var result = await service.FindBlogsWithTag("dotnet");

        // 断言结果
        Assert.AreEqual(1, result.Count);
    }

    [ClassCleanup]
    public static async Task ClassCleanup()
    {
        await _postgresContainer.StopAsync();
        await _postgresContainer.DisposeAsync();
    }
}

// 简易DbContextFactory实现
public class DbContextFactory<TContext> : IDbContextFactory<TContext> where TContext : DbContext
{
    private readonly DbContextOptions<TContext> _options;

    public DbContextFactory(DbContextOptions<TContext> options) => _options = options;

    public Task<TContext> CreateDbContextAsync(CancellationToken cancellationToken = default)
    {
        return Task.FromResult(Activator.CreateInstance(typeof(TContext), _options) as TContext);
    }
}

方案4：客户端评估（不推荐） #

如果测试数据量极小，可以强制切换到客户端评估，但这会拉取全表数据到内存过滤，不符合生产环境行为，仅作为临时 workaround：

public async Task<List<Blog>> FindBlogsWithTag(string tag)
{
    await using var context = await _factory.CreateDbContextAsync();
    return context.Blogs
        .AsEnumerable() // 切换到客户端评估
        .Where(blog => blog.Tags.Contains(tag))
        .ToList();
}

总结 #

• 若需保持SQLite测试且接近生产行为，推荐方案1或方案2
• 若希望完全匹配生产环境的查询逻辑和数据库特性，推荐方案3（Testcontainers自动化PostgreSQL测试）
• 方案4仅作为临时应急方案，不建议长期使用

内容的提问来源于stack exchange，提问作者mu88