EF Core 7.0: Wrong SQL generated for string.IsNullOrWhitespace

[arthur-liberman]

The provider generates wrong sql when mapper contains string.IsNullOrWhiteSpace and NCLOB.

return dbContext.Items.Select(i => new Dto
{
    Id = i.Id,
    Name = i.Name,
    Data = string.IsNullOrWhiteSpace(i.Data) ? "N/A" : ProcessData(i.Data),
    Colors = $"[ {string.Join(", ", i.Colors)} ]"
}).ToList();

We get the following error: ORA-00932: inconsistent datatypes: expected - got NCLOB The generated SQL:

SELECT "i"."id", "i"."name", CASE
    WHEN (("i"."data" IS NULL) OR (LTRIM(RTRIM("i"."data")) = N'')) THEN 1
    ELSE 0
END, "i"."data", "c"."id", "c"."ItemId", "c"."name"
FROM "Item" "i"
LEFT JOIN "Color" "c" ON "i"."id" = "c"."ItemId"
ORDER BY "i"."id"

A 3rd party provider doesn't exhibit similar issues. Full code to reproduce:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.Extensions.Logging;

namespace Oracle_Guid
{
    internal class Program
    {
        static void Main(string[] args)
        {
            test_oracle_isnull_lob();
            Console.WriteLine("Hello, World!");
        }

        private static void test_oracle_isnull_lob()
        {
            new isnull_lob_issue.Seeder().Seed();
            var items = new isnull_lob_issue.Logic().Query();
        }
    }

    internal class isnull_lob_issue
    {
        public class Logic
        {
            public IList<Dto> Query()
            {
                using (var dbContext = new MyDbContext())
                {
                    return dbContext.Items.Select(i => new Dto
                    {
                        Id = i.Id,
                        Name = i.Name,
                        Data = string.IsNullOrWhiteSpace(i.Data) ? "N/A" : ProcessData(i.Data),
                        Colors = $"[ {string.Join(", ", i.Colors)} ]"
                    }).ToList();
                }
            }

            private static string ProcessData(string data) => data;
        }

        public class Seeder
        {
            public void Seed()
            {
                using (var dbContext = new MyDbContext())
                {
                    var rdc = dbContext.Database.GetService<IRelationalDatabaseCreator>();
                    if (rdc.Exists())
                        rdc.EnsureDeleted();
                }
                using (var dbContext = new MyDbContext())
                {
                    var rdc = dbContext.Database.GetService<IRelationalDatabaseCreator>();
                    rdc.EnsureCreated();
                    if (!dbContext.Items.Any())
                    {
                        for (int i = 0; i < 10; i++)
                        {
                            var rand = new Random();
                            int cat = rand.Next() % 3;
                            int colors = rand.Next() % 4;
                            var item = new Item { Uid = Guid.NewGuid(), CategoryId = cat, Name = $"Item {i}", Data = cat % 2 != 0 ? $"Data for Item {i}" : " " };
                            for (int c = 0; c <= colors; c++)
                            {
                                item.AddColor(c);
                            }
                            dbContext.Items.Add(item);
                        }
                    }
                    if (!dbContext.Categories.Any())
                    {
                        for (int i = 0; i < 3; i++)
                        {
                            var category = new Category { Name = $"Category {i}" };
                            dbContext.Categories.Add(category);
                        }
                    }
                    dbContext.SaveChanges();
                }
            }
        }

        public class Dto
        {
            public int Id { get; set; }
            public Guid Uid { get; set; }
            public string Name { get; set; }
            public string Colors { get; set; }
            public string Data { get; set; }
        }

        public class Item
        {
            private readonly List<Color> _colors = new List<Color>();
            public IReadOnlyCollection<Color> Colors => _colors;
            public int Id { get; set; }
            public Guid? Uid { get; set; }
            public int CategoryId { get; set; }
            public string Name { get; set; }
            public string Data { get; set; }

            internal void AddColor(int i)
            {
                var color = new Color { Name = $"Color {i}" };
                _colors.Add(color);
            }
        }

        public class Color
        {
            public int ItemId { get; private set; }
            public virtual Item Item { get; private set; }
            public int Id { get; set; }
            public string Name { get; set; }
        }

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

        public class MyDbContext : DbContext
        {
            public static readonly ILoggerFactory loggerFactory = LoggerFactory.Create(builder =>
            {
                builder.AddConsole();
            });

            public DbSet<Item> Items { get; set; }
            public DbSet<Color> Colors { get; set; }
            public DbSet<Category> Categories { get; set; }

            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                modelBuilder.ApplyConfiguration(new ItemConfiguration());
                modelBuilder.ApplyConfiguration(new CategoryConfiguration());
                modelBuilder.ApplyConfiguration(new ColorConfiguration());
            }

            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder
                    .UseLoggerFactory(loggerFactory)
                    .EnableSensitiveDataLogging()
                    .UseOracle(OracleConstants.ORACLE_CONNECTION);
            }
        }

        public class ItemConfiguration : IEntityTypeConfiguration<Item>
        {
            public void Configure(EntityTypeBuilder<Item> builder)
            {
                builder.ToTable("Item");
                builder.HasKey(o => o.Id);
                builder.Property(t => t.Id).HasColumnName("id").ValueGeneratedOnAdd();
                builder.Property(t => t.Uid).HasColumnName("uid").IsRequired(false);
                builder.Property(t => t.CategoryId).HasColumnName("categoryId");
                builder.Property(t => t.Name).HasColumnName("name").HasMaxLength(255);
                builder.Property(t => t.Data).HasColumnName("data").HasMaxLength(int.MaxValue).IsRequired(false);
            }
        }

        public class ColorConfiguration : IEntityTypeConfiguration<Color>
        {
            public void Configure(EntityTypeBuilder<Color> builder)
            {
                builder.ToTable("Color");
                builder.HasKey(o => o.Id);
                builder.Property(t => t.Id).HasColumnName("id").ValueGeneratedOnAdd();
                builder.Property(t => t.Name).HasColumnName("name").HasMaxLength(255);

                builder.HasOne(s => s.Item)
                    .WithMany(s => s.Colors)
                    .HasForeignKey(s => s.ItemId);
            }
        }

        public class CategoryConfiguration : IEntityTypeConfiguration<Category>
        {
            public void Configure(EntityTypeBuilder<Category> builder)
            {
                builder.ToTable("Category");
                builder.HasKey(o => o.Id);
                builder.Property(t => t.Id).HasColumnName("id").ValueGeneratedOnAdd();
                builder.Property(t => t.Name).HasColumnName("name").HasMaxLength(255);
            }
        }
    }
}

[alexkeh]

This is related to the same issue as #272, which is associated with Bug 34989781.

[arthur-liberman]

Ah, I see I found a dup :) Thanks.

[arthur-liberman]

@alexkeh I'm not sure how high of a priority this is, but the root cause is comparing the CLOB as a normal string. I have just found another problem, which is a lot worse, I couldn't find it mentioned before for some reason. dbContext.Items.Where(i => i.Data.Equals("N/A")) results in ORA-00932: inconsistent datatypes: expected - got NCLOB with the following SQL:

SELECT "i"."id", "i"."name", "c"."id", "c"."ItemId", "c"."name"
FROM "Item" "i"
LEFT JOIN "Color" "c" ON "i"."id" = "c"."ItemId"
WHERE "i"."data" =  N'N/A' 
ORDER BY "i"."id"

instead of (generated by 3rd party provider)

SELECT "i"."id", "i"."name", "c"."id" "id1", "c"."ItemId", "c"."name" "name1"
FROM "Item" "i"
LEFT JOIN "Color" "c" ON "i"."id" = "c"."ItemId"
WHERE DBMS_LOB.COMPARE("i"."data", TO_NCLOB('N/A')) = 0
ORDER BY "i"."id"

Looks like it's the same reason we have this error in IsNullOrWhiteSpace. The 3rd party provider uses TRIM(str) IS NULL instead of comparing it to empty string.

[alexkeh]

@arthur-liberman Thanks for the update. We'll take a look.

[arthur-liberman]

Have you managed to reproduce this? This is a major blocker for us, is there any ETA on a fix?

[alexkeh]

I don't have an ETA. If you need to get a fix ASAP, I would recommend opening a service request with Oracle Support. Oracle can then get you a one-off fix sooner. Typically, ODP.NET delivers patches via NuGet Gallery once a quarter. The next quarterly patch is coming up soon (July/August) and it doesn't look like a fix for this bug will make it by the cutoff date.

[arthur-liberman]

Ok, that's what we will do. Will you add the new information above to Bug 34989781, and that it's probably a higher priority issue? Or do we need a new bug report for this?

[alexkeh]

I've added to the bug log that this issue has high impact for you. However, to formally get a bug escalated, your SR needs to be associated with the bug and your team's commitment to participate in the escalation at the same level as the Oracle Support and dev team will be. i.e. if it's 24x7, both sides work on it 24x7 until resolution. If it's 8x5, then both sides work on it 8x5. Only a customer can make that commitment

[alexkeh]

Fixed with Oracle EF Core 8.