Any ILIKE linq expression could not be translated in EF Core 3.1 using InMemoryDatabase

[Andrioden]

The following expression is not supported using InMemoryDatabase. If possible, could this be added support for? Either by translated the code below or by supplying a specific function? Seems like it is supported by postgresql.

I understand that the workaround is to load data to memory and filter it there.

Failing code

List<string> skuLikes = ...
return await _context.ProductVariations
    .Where(v => skuLikes.Any(like => EF.Functions.ILike(v.SKU, like)))
    .ToListAsync();

Gives error

    Test method Dyrket.Tests.ProductAPITests.IT_ProductAPI_Get_Organization threw exception: 
    System.InvalidOperationException: The LINQ expression 'DbSet<ProductVariation>
        .Where(p => __skuLikes_0
            .Any(like => __Functions_1
                .ILike(
                    _: p.SKU, 
                    matchExpression: like)))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Stack Trace:

    QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|8_0(ShapedQueryExpression translated, <>c__DisplayClass8_0& )
    QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
    QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
    QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
    QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
    <>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
    CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
    QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
    EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
    EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
    ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
    EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
    ProductAPI.GetOrganizationRuleFilteredProductIdsAsync(Organization org) line 169
    ProductAPI.ApplyOrganizationRuleQueryFilterAsync(IQueryable`1 query, Organization org) line 287
    ProductAPI.BuilQueryAsync(ProductsQuery q) line 254
    ProductAPI.GetAsync(ProductsGetQuery q) line 74
    ProductAPITests.IT_ProductAPI_Get_Organization() line 148

Relevant project config

<PropertyGroup>
  <TargetFramework>netcoreapp3.1</TargetFramework>
  <RuntimeFrameworkVersion>3.1.0</RuntimeFrameworkVersion>
</PropertyGroup>
<ItemGroup>
  <PackageReference Include="Microsoft.AspNetCore.Identity.EntityFrameworkCore" Version="3.1.0" />
  <PackageReference Include="Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore" Version="3.1.0" />
  <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="3.1.0" />
  <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL.NetTopologySuite" Version="3.1.0" />
  <PackageReference Include="Z.EntityFramework.Plus.EFCore" Version="1.8.18" />
</ItemGroup>

[roji]

Please see the below program which works fine, and the SQL it produces. Can you please submit a similar minimal example that triggers the issue?

class Program
{
    static void Main()
    {
        using var ctx = new BlogContext();
        ctx.Database.EnsureDeleted();
        ctx.Database.EnsureCreated();

        var patterns = new[] { "a%", "b%" };
        var blogs = ctx.Blogs.Where(b => patterns.Any(pattern => EF.Functions.ILike(b.Name, pattern))).ToList();
    }
}

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

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseNpgsql("...")
}

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

Resulting SQL:

2019-12-09 17:49:33.739 CET [8554] npgsql_tests@test LOG:  execute <unnamed>: SELECT b.id, b.name
        FROM blogs AS b
        WHERE b.name ILIKE ANY ($1)
2019-12-09 17:49:33.739 CET [8554] npgsql_tests@test DETAIL:  parameters: $1 = '{a%,b%}'

[Andrioden]

My bad. I didnt realise it was because of InMemoryDatabase. Here is an example that fails as stated:

class Program
{
    static void Main()
    {
        BlogContext ctx = BuildMemoryContext();

        ctx.Database.EnsureDeleted();
        ctx.Database.EnsureCreated();

        var patterns = new[] { "a%", "b%" };
        var blogs = ctx.Blogs.Where(b => patterns.Any(pattern => EF.Functions.ILike(b.Name, pattern))).ToList();
    }

    static BlogContext BuildMemoryContext()
    {
        DbContextOptionsBuilder<BlogContext> builder = new DbContextOptionsBuilder<BlogContext>().UseInMemoryDatabase("TestMemoryDB");
        return new BlogContext(builder.Options);
    }
}

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

    public DbSet<Blog> Blogs { get; set; }
}

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

Requires the following NuGet package <PackageReference Include="Microsoft.EntityFrameworkCore.InMemory" Version="3.1.0" />

[roji]

Yeah, that's definitely not going to work. ILike is an Npgsql extension and will not be supported on InMemory. InMemory really is very basic and not meant as a full-fledged relational provider to test again (much less to invoke PG functionality on). See this doc page on InMemory vs. in-memory Sqlite as an alternative.

Frankly, PostgreSQL is so easy to set up and light, that I'd recommend you consider running your tests against... PostgreSQL.

[gojanpaolo]

Frankly, PostgreSQL is so easy to set up and light, that I'd recommend you consider running your tests against... PostgreSQL.

@roji Do you suggest to replace all our unit tests that uses EF Core's InMemory db to target PostgreSQL instead? We just got into an issue testing some functions that involves calling postgres ilike. I'm going to explore how to do unit tests using postgres, but if you could share some online resources, that would be really helpful!. Thank you!

[roji]

@gojanpaolo this is a question which we repeatedly get, I'd start out with the EF Docs on testing for the different possible approaches in testing applications which use EF Core. tl;dr InMemory is neither here no there; if you don't want to test against a real database, then you have to add a repository layer above your DbContext, and mock operations there - no database or mock will support EF.Functions.ILike. Otherwise, you can write your tests to query against a real PostgreSQL database, which is what I'd generally recommend.

[gojanpaolo]

@roji Thank you for the response. I just looked further into the docs and found this: https://docs.microsoft.com/en-us/ef/core/testing/sharing-databases#maintaining-database-state. I think using that technique will help us migrate all our tests to use postgres considering that our tests that uses in-memory has their own set of data and should not affect each other.

[gojanpaolo]

I just tried the pattern described in https://docs.microsoft.com/en-us/ef/core/testing/sharing-databases#maintaining-database-state. But running multiple tests in the same class seems to fail on some of the tests.. But those failing tests are passing if I run them one by one. Running all the tests in Debug mode also passes all the tests.

[roji]

@gojanpaolo if you're using xunit, then by default tests within the same class aren't parallelized. That would indicate a test isolation issue, where some test modifies database state without restoring it, affecting how later tests execute. You'll have to track down the problematic test, and make sure that all database state is probably reset between tests.