EF Core Array, PostgreSQL and SQLite-CodePudding

In one of our .NET 7 apps (using EF Core 7) I want to make use of the PostgreSQL Array Type Mapping feature and I'm struggling with testing a certain part of the code base.

Please see the following (rather simplified) example:

public class Blog
{
    public int Id { get; set; }

    public string Url { get; set; }

    public string[] Tags { get; set; }
}

public class BlogContext : DbContext
{
    DbSet<Blog> Blogs { get; set; }
}

This works nicely in PostgreSQL, the corresponding data type for Tags in PostgreSQL is text[] and all the system integration tests (running PostgreSQL in Docker) are passing.

But for the integration tests, we're using an in-memory SQLite database. Since SQLite doesn't seem to support text[] as data type, I came up with the following workaround:

public class TestDbContext : BlogContext
{
    public TestDbContext(DbContextOptions options)
        : base(options)
    {
    }

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

        modelBuilder
            .Entity<Blog>()
            .Property(blog => blog.Tags)
            .HasColumnType("text")
            .HasConversion(
                strings => string.Join(",", strings),
                s => s.Split(",", StringSplitOptions.RemoveEmptyEntries));
    }
}

All the integration tests are now working, except for the following class:

public class BlogService
{
    public BlogService(IDbContextFactory<BlogContext> factory) => _factory = factory;

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

The corresponding test fails with:

System.InvalidOperationException
The LINQ expression 'DbSet<Blog>()
    .Where(b => b.Tags
        .Contains(__tag_0))' could not be translated. Additional information: Translation of method 'System.Linq.Enumerable.Contains' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

So my question: how can I make this test pass with SQLite? Is it even possible? Or should I convert the integration test into a system integration test and hereby implicitly switch from SQLite to PostgreSQL?

Thanks!

CodePudding user response：

I've asked for help in the EF Core repo (see this issue) and got the advice to use a proper PostgreSQL instance, i. e. converting my integration into a system integration test.