Invalid SQL for EF.Functions.Like

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
dejvid
Posts: 3
Joined: Wed 05 Dec 2018 14:34

Invalid SQL for EF.Functions.Like

Post by dejvid » Fri 02 Oct 2020 07:52

Devart.Data.Oracle.EFCore sometimes generates invalid SQL for EF.Functions.Like call.

Below is a minimal code to reproduce this bug.

Code: Select all

class Item {
    public int Id {get;set;}
    public int Type {get;set;}
}

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

    public DbSet<Item> Items {get;set;}
}

AppDbContext context = ...
context.Items
            .FromSqlRaw("select 1 \"Id\", 2 \"Type\" from dual")
            .Select(x => new {
                TypeStr = x.Type == 1 ? "A": "B"
            })
            .Where(x => EF.Functions.Like(x.TypeStr, "C", "/"))
            .ToList();
The above code generates this sql

Code: Select all

SELECT (CASE
          WHEN "i"."Type" = 1 THEN 'A'
          ELSE 'B'
      END) "TypeStr"
      FROM (
          select 1 "Id", 2 "Type" from dual
      ) "i"
      WHERE CASE
          WHEN "i"."Type" = 1 THEN TO_NCLOB('A')
          ELSE TO_NCLOB('B')
      END LIKE TO_NCLOB('C') ESCAPE TO_NCLOB('/')
The main problem is that escape character is specified as NCLOB instead of char. But all 3 translations to NCLOB are unnecessary and slow down the query.

Shalex
Site Admin
Posts: 9302
Joined: Thu 14 Aug 2008 12:44

Re: Invalid SQL for EF.Functions.Like

Post by Shalex » Mon 05 Oct 2020 17:28

Thank you for your report. We have reproduced the issue and will notify you when it is fixed.

Shalex
Site Admin
Posts: 9302
Joined: Thu 14 Aug 2008 12:44

Re: Invalid SQL for EF.Functions.Like

Post by Shalex » Fri 30 Oct 2020 11:06

The bug with generating invalid SQL for EF.Functions.Like in EF Core 3 is fixed: viewtopic.php?f=1&t=42365.

Post Reply