EF Core 7.0: Wrong SQL generated for COALESCE with Nullable GUID - RAW(16) column

[arthur-liberman]

The provider generates wrong sql when mapping a nullable guid field in the domain object to a non-nullable guid field in the DTO.

return dbContext.Items.Select(i => new Dto
{
    Id = i.Id,
    Uid = i.Uid ?? Guid.Empty,
    Name = i.Name,
    Colors = $"[ {string.Join(", ", i.Colors)} ]"
}).ToList();

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

SELECT "i"."id", COALESCE("i"."uid", '00000000000000000000000000000000'), "i"."name", "c"."id", "c"."ItemId", "c"."name"
FROM "Item" "i"
LEFT JOIN "Color" "c" ON "i"."id" = "c"."ItemId"
ORDER BY "i"."id"

There's a missing CAST(x AS RAW(16)). A third party provider does this correctly. 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
{
    public class Logic
    {
        public IList<Dto> Query()
        {
            using (var dbContext = new MyDbContext())
            {
                return dbContext.Items.Select(i => new Dto
                {
                    Id = i.Id,
                    Uid = i.Uid ?? Guid.Empty,
                    Name = i.Name,
                    Colors = $"[ {string.Join(", ", i.Colors)} ]"
                }).ToList();
            }
        }
    }

    public class Seeder
    {
        public void Seed()
        {
            using (var dbContext = new MyDbContext())
            {
                var rdc = dbContext.Database.GetService<IRelationalDatabaseCreator>();
                if (rdc.Exists())
                    rdc.EnsureDeleted();
                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}" };
                        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 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; }

        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");
            builder.Property(t => t.CategoryId).HasColumnName("categoryId");
            builder.Property(t => t.Name).HasColumnName("name").HasMaxLength(255);
        }
    }

    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);
        }
    }

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

        private static void test_oracle_fail_cast_guid()
        {
            new Seeder().Seed();
            var items = new Logic().Query();
            throw new NotImplementedException();
        }
    }
}

[alexkeh]

Thanks for reporting @arthur-liberman. I was able to reproduce the issue and filed a bug (35533638) for the Oracle dev team to review.

[arthur-liberman]

That's great, thank you. It would be nice if we could get an internal build with a fix if/when such becomes available. As this issue breaks a part of the normal workflow of the application. We are currently migrating from a 3rd party provider to this one, and it's still very early days for us. We will be doing further testing and verification, which may result in more reports :)

[alexkeh]

For an early validation drop, you'll need to open an Oracle Support service request. Unfortunately, we can't provide these validation drops through any other means.

Once the dev team has a fix, we can provide you the validation drop to test out. You can continue to use it for non-production uses until there's a permanent fix.

[arthur-liberman]

Ok, thanks. I will look into it next week.

[arthur-liberman]

@alexkeh Is there a how-to on how to open an SR for this component?

[alexkeh]

Log into My Oracle Support and search for Doc ID 1321379.1 (How to create a Technical Service Request (SR) in My Oracle Support).

[alexkeh]

Fixed with Oracle EF Core 8 release.