why string contains can be translated to like ''

[EmmaCCC]

The issue

This is my entity:

  public class OrderLog : BaseEntity
  {


      public string OrderCode { get; set; }

      public int OperatorId { get; set; }

      public string OperatorName { get; set; }

      public OrderOperatorType OperatorType { get; set; }

      public OrderChangeEventType EventType { get; set; }

      public string Content { get; set; }
  }

my test:

[Test]
public async Task LikeTest()
{
    var operatorName = "lisa";
    var result = await db.OrderLogs.Where(a => a.OperatorName.Contains(operatorName)).ToListAsync();
}

the translated sql:

SELECT `o`.`id`, `o`.`content`, `o`.`create_time`, `o`.`event_type`, `o`.`operator_id`, `o`.`operator_name`, `o`.`operator_type`, `o`.`order_code`
FROM `order_logs` AS `o`
WHERE (@__operatorName_0 LIKE '') OR (LOCATE(@__operatorName_0, `o`.`operator_name`) > 0)

so why (@__operatorName_0 LIKE '') , is this necessary?
Why not just be translated

 WHERE `o`.`operator_name` LIKE concat('%',@__operatorName_0,'%')

or Why not just be translated only

WHERE LOCATE(@__operatorName_0, `o`.`operator_name`) > 0

I think (@__operatorName_0 LIKE '') is redundant ,is it a bug?

Further technical details

MySQL version: 8.0.27
Operating system: windows 10
Pomelo.EntityFrameworkCore.MySql version: 6.0.0
Microsoft.AspNetCore.App version: 6.0

Other details about my project setup:

[lauxjpn]

@EmmaCCC I am referencing this simple console app code below:

Program.cs

using System;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate;

public class IceCream
{
    public int IceCreamId { get; set; }
    public string Name { get; set; }
}

public class Context : DbContext
{
    public DbSet<IceCream> IceCreams { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=Issue1896";
            var serverVersion = ServerVersion.AutoDetect(connectionString);

            optionsBuilder
                .UseMySql(connectionString, serverVersion)
                .LogTo(Console.WriteLine, LogLevel.Information)
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<IceCream>(
            entity =>
            {
                entity.HasData(
                    new IceCream { IceCreamId = 1, Name = "Vanilla" },
                    new IceCream { IceCreamId = 2, Name = "Chocolate" },
                    new IceCream { IceCreamId = 3, Name = "Matcha" });
            });
    }
}

internal static class Program
{
    private static void Main()
    {
        using var context = new Context();
        
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();

        var searchString = "la";
        
        var results = context.IceCreams
            .Where(i => i.Name.Contains(searchString))
            .OrderBy(i => i.IceCreamId)
            .ToList();
        
        Trace.Assert(results.Count == 2);
        Trace.Assert(results[0].Name == "Vanilla");
        Trace.Assert(results[1].Name == "Chocolate");
    }
}

The EF Core team changed their base implementation of string.Contains() (and others) in dotnet/efcore#31482 (comment), which we implemented as well and results in the following change between Pomelo 7.0.x and 8.0.x:

Pomelo 6.0.x/7.0.x Output (SQL)

warn: 20.03.2024 09:26:57.235 CoreEventId.SensitiveDataLoggingEnabledWarning[10400] (Microsoft.EntityFrameworkCore.Infrastructure) 
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.
info: 20.03.2024 09:26:57.682 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (22ms) [Parameters=[], CommandType='Text', CommandTimeout='30']                                
      DROP DATABASE `Issue1896`;                                                                                        
info: 20.03.2024 09:26:57.909 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']                                 
      CREATE DATABASE `Issue1896`;                                                                                      
info: 20.03.2024 09:26:58.108 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='30']                                
      ALTER DATABASE CHARACTER SET utf8mb4;                                                                             
info: 20.03.2024 09:26:58.147 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (37ms) [Parameters=[], CommandType='Text', CommandTimeout='30']                                
      CREATE TABLE `IceCreams` (                                                                                        
          `IceCreamId` int NOT NULL AUTO_INCREMENT,
          `Name` longtext CHARACTER SET utf8mb4 NULL,
          CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)
      ) CHARACTER SET=utf8mb4;
info: 20.03.2024 09:26:58.156 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (8ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      INSERT INTO `IceCreams` (`IceCreamId`, `Name`)
      VALUES (1, 'Vanilla'),
      (2, 'Chocolate'),
      (3, 'Matcha');
info: 20.03.2024 09:26:58.669 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (29ms) [Parameters=[@__searchString_0='la' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SELECT `i`.`IceCreamId`, `i`.`Name`
      FROM `IceCreams` AS `i`
      WHERE (@__searchString_0 LIKE '') OR (LOCATE(@__searchString_0, `i`.`Name`) > 0)
      ORDER BY `i`.`IceCreamId`

Pomelo 8.0.x Query Output (SQL)

info: 20.03.2024 09:27:42.723 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (21ms) [Parameters=[@__searchString_0_contains='%la%' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SELECT `i`.`IceCreamId`, `i`.`Name`
      FROM `IceCreams` AS `i`
      WHERE `i`.`Name` LIKE @__searchString_0_contains
      ORDER BY `i`.`IceCreamId`

So once you upgrade to Pomelo 8.0.x, the translation will change.

---

so why (@__operatorName_0 LIKE '') , is this necessary?

I think (@__operatorName_0 LIKE '') is redundant ,is it a bug?

This clause has the intention to produce the behavior of the .NET string.Contains(string) method:

[...]

Returns

Boolean
true if the value parameter occurs within this string, or if value is the empty string (""); otherwise, false.
[...]

This is also referenced by the following excerpt in dotnet/efcore#31482 (comment):

[...] the case of an empty-string pattern - which in .NET returns true for any non-null string [...]

So this is not a bug but intentional behavior.

---

There is also the EF.Functions.Like() method that you can use instead:

var searchString = "%la%";

var results = context.IceCreams
    .Where(i => EF.Functions.Like(i.Name, searchString))
    .OrderBy(i => i.IceCreamId)
    .ToList();

It generates the following SQL in Pomelo 6+:

info: 20.03.2024 10:03:29.085 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (9ms) [Parameters=[@__searchString_1='%la%' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SELECT `i`.`IceCreamId`, `i`.`Name`
      FROM `IceCreams` AS `i`
      WHERE `i`.`Name` LIKE @__searchString_1
      ORDER BY `i`.`IceCreamId`

[EmmaCCC]

@lauxjpn Oh, I see. Thanks for your help. I will try to use EF.Functions.Like().I don't plan to upgrade the version just yet.