'Contains' on Value Types / Different Behavior of Like and ILike

[mneundorfer]

I want to implement a search function on certain fields on my entity which are value types. Concrete, simplified example:

public readonly record struct Email
{
    private readonly string _email;

    public Email(string email)
    {
        _email = email;
    }
}

public class User {
  public Email Email { get; private set; }
}

entityTypeBuilder
  .Property(x => x.Email)
  .HasConversion(
    v => v.ToString(),
    v => new Email(v)
  );

When I now tried to apply my filtering logic

_dbContext
  .Users
  .Where(u => u.Email.ToString().Contains(searchTerm))
  .ToList();

It explodes:

The LINQ expression 'DbSet<User>()
    .Where(u => u.Email.ToString().Contains(__searchTerm_0))' could not be translated.

This kind of confused me, since I do already have queries in place checking for equality, where the following works:

_dbContext
  .Users
  .SingleOrDefault(u => u.Email == email);

Nevertheless, I went ahead and tested what would happen if I tackled the issue differently:

from item in _dbContext.Users
  where EF.Functions.ILike(item.Email, $"%{searchTerm}%")
  select item;

But, the result appears to be similar:

The LINQ expression 'DbSet<User>()
    .Where(u => __Functions_0
        .Like(
            matchExpression: (string)u.Email, 
            pattern: __Format_1))
    .Where(u => u.Email.ToString().Contains(__searchTerm_2))' could not be translated

Now things become interesting. Because when I switch from ILike to Like, everything works like a charm:

from item in _dbContext.Users
  where EF.Functions.Like(item.Email, $"%{searchTerm}%")
  select item;

I'm wondering where I went off track here and how I can achieve what I initially wanted to to: Build a case-insensitive search on the Value-typed Email property of the User

Include provider and version information

EF Core version: 8.0.0
Database provider: (e.g. Microsoft.EntityFrameworkCore.Sqlite)
Target framework: NET 8.0
Operating system: Ubuntu 22.04
IDE: Rider 2023.3.4

[ajcvickers]

In the general case, this is covered by #10434. Make sure to vote (👍) for that issue.

[mneundorfer]

Thanks for the hint, will do! Is there any known workarounds for this until then?

[ajcvickers]

@mneundorfer I'll let the query experts (@roji, @maumar, @cincuranet) comment on that.

[maumar]

@mneundorfer you can get case-insensitive behavior by calling ToLower on both arguments to the like function. Something like this:

using var ctx = new MyContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

var u1 = new User { Email = new Email("[email protected]") };
var u2 = new User { Email = new Email("[email protected]") };
var u3 = new User { Email = new Email("[email protected]") };

ctx.Set<User>().AddRange(u1, u2, u3);
ctx.SaveChangesAsync();


var searchTerm = "Foo";

var query = from item in ctx.Set<User>()
            where EF.Functions.Like(((string)item.Email).ToLower(), $"%{searchTerm.ToLower()}%")
            select item;


var result = await query.ToListAsync();

Console.WriteLine(result.Count);


public readonly record struct Email
{
    private readonly string _email;

    public Email(string email)
    {
        _email = email;
    }

    public override string ToString()
        => "Email: " + _email;

    public static implicit operator string(Email d) => d._email;
}

public class User
{
    public int Id { get; set; }
    public Email Email { get; /*private*/ set; }
}

public class MyContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
          .Property(x => x.Email)
          .HasConversion(
            v => v.ToString(),
            v => new Email(v)
          );
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Repro;Trusted_Connection=True;MultipleActiveResultSets=true");
    }
}

Sql that gets produced (on SqlServer) is as follows:

exec sp_executesql N'SELECT [u].[Id], [u].[Email]
FROM [User] AS [u]
WHERE LOWER(CAST([u].[Email] AS nvarchar(max))) LIKE @__Format_1',N'@__Format_1 nvarchar(4000)',@__Format_1=N'%foo%'

[mneundorfer]

@maumar Thanks - works as it should.

Still, I'm kind of curious: Why does this work for Like, but not for ILike? Or to put it differently: If the string conversion would work in the ILike scenario, we wouldn't need this workaround of calling ToLower() on both arguments. Am I hitting some edge case here or is this "expected"?

[roji]

@mneundorfer what you're doing is generally unsupported - once you have a value converter on something, you can no longer (reliably) query it, since EF has no idea on the actual data format saved to the database, etc. (that's what #10434 is about, as @ajcvickers wrote above).

Now, some things may work by chance, and there's apparently some implementation detail that differs between Like and ILike, and which accounts for the different (the latter is a PostgreSQL-specific operator and is implemented elsewhere, in the PG provider). But in general this is something we don't support at the moment.

[roji]

I'm going to go ahead and close this as an unsupported scenario and a dup of #10434. If critical, you can open an issue in https://github.com/npgsql/efcore.pg requesting this work for ILike, but I'm unlikely to find time to invest in doing that any time soon.

[roji]

Duplicate of #10434