EF.Functions.ILike against an array of patterns with an escape character gets translated to LIKE instead of ILIKE

[rudism]

I'm not sure if this is an efcore.pg problem or an upstream EF Core problem. Here is a quick dotnet-script project that reproduces the problem:

#!/usr/bin/env dotnet-script
#r "nuget: Npgsql.EntityFrameworkCore.PostgreSQL, 8.0.0"

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;

[Table("my_entity")]
public class MyEntity {
  [Key]
  public Guid Id { get; set; }
  public required string Description { get; set; }
}

public class MyDbContext : DbContext {
  private readonly string _connString;

  public MyDbContext(string connString) => _connString = connString;

  public DbSet<MyEntity> MyEntities { get; set; }

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
    optionsBuilder.UseNpgsql(_connString);
  }
}

var dbContext = new MyDbContext("Host=127.0.0.1;Database=mydb;Username=user;Password=password");
var likeTerms = new[] { "%some\\_term%", "%some\\%other\\\\term%" };

var query1 = dbContext.MyEntities.Where(e => EF.Functions.ILike(e.Description, likeTerms[0], "\\"));
Console.WriteLine("-- QUERY 1");
Console.WriteLine(query1.ToQueryString() + ";");

var query2 = dbContext.MyEntities.Where(e => likeTerms.All(t => EF.Functions.ILike(e.Description, t)));
Console.WriteLine("\n-- QUERY 2");
Console.WriteLine(query2.ToQueryString() + ";");

var query3 = dbContext.MyEntities.Where(e => likeTerms.All(t => EF.Functions.ILike(e.Description, t, "\\")));
Console.WriteLine("\n-- QUERY 3");
Console.WriteLine(query3.ToQueryString() + ";");

Here is the output with the generated SQL for the three queries, which are all variations on calling EF.Functions.ILike:

-- QUERY 1
-- @__p_1='%some\_term%'
SELECT m."Id", m."Description"
FROM my_entity AS m
WHERE m."Description" ILIKE @__p_1 ESCAPE '\';

-- QUERY 2
-- @__likeTerms_0={ '%some\_term%', '%some\%other\\term%' } (DbType = Object)
SELECT m."Id", m."Description"
FROM my_entity AS m
WHERE m."Description" ILIKE ALL (@__likeTerms_0);

-- QUERY 3
-- @__likeTerms_0={ '%some\_term%', '%some\%other\\term%' } (DbType = Object)
SELECT m."Id", m."Description"
FROM my_entity AS m
WHERE NOT EXISTS (
    SELECT 1
    FROM unnest(@__likeTerms_0) AS l(value)
    WHERE m."Description" NOT LIKE l.value ESCAPE '\' OR m."Description" IS NULL OR l.value IS NULL);

The first two variations (matching a single pattern with an escape character, and matching an array of patterns with no escape character) translate correctly to ILIKE statements, however the third variation (matching an array of patterns with an escape character) is translating to a statement that uses LIKE instead of ILIKE.

[roji]

Thanks for the repro - I can confirm this is a bug, a fix is out for 8.0.1 (#3039).