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