ORA-12704: character set mismatch in generated query #399
Description
Hi,
this issue might be related to #66, or the recent fixed bug “Bug 35535281 - DEFAULT VALUE FOR NON-UNICODE COLUMN IS GENERATED WITH N PREFIX WHEN USING VALUE CONVERTER AND ENUM TYPE“.
I am getting an ORA-12704 from the following query when applied on a non-Unicode string column:
var results = db.Tests
.Select(t => t.Title.Length > 80 ? t.Title.Substring(0, 77) + "..." : t.Title)
.ToList();This generates the following SQL:
SELECT CASE
WHEN CAST(LENGTH("t"."TITLE") AS NUMBER(10)) > 80 THEN COALESCE(SUBSTR("t"."TITLE", 0 + 1, 77), NULL) || N'...'
ELSE "t"."TITLE"
END
FROM "TEST" "t"As you can see, it tries to append N'...' here even though the column in this case does not support unicode. The column is defined as IsUnicode(false), so I understand it that it shouldn’t use the N prefix here.
If I drop out the substring logic, it also appends N'...' but then it works:
var results = db.Tests
.Select(t => t.Title + "...")
.ToList();SELECT COALESCE("t"."TITLE", NULL) || N'...'
FROM "TEST" "t"Full test code is in the expandable section below.
Show full test code
CREATE TABLE Test (
Id NUMBER(19) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
Title VARCHAR2(1024 CHAR),
CONSTRAINT PK_Test PRIMARY KEY (Id)
)using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;
var connectionString = "Data Source=…";
var dbOptions = new DbContextOptionsBuilder<MyContext>()
.UseOracle(connectionString, oracleOptions =>
oracleOptions.UseOracleSQLCompatibility(OracleSQLCompatibility.DatabaseVersion19))
.Options;
using var db = new MyContext(dbOptions);
var results = db.Tests
.Select(t => t.Title.Length > 80 ? t.Title.Substring(0, 77) + "..." : t.Title)
//.Select(t => t.Title + "...")
.ToList();
foreach (var result in results)
Console.WriteLine(result);
public class MyContext : DbContext
{
public DbSet<Test> Tests { get; set; }
public MyContext(DbContextOptions options) : base(options) { }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Test>(entity =>
{
entity.ToTable("TEST");
entity.Property(e => e.Id).HasColumnName("ID").ValueGeneratedOnAdd();
entity.Property(e => e.Title).HasColumnName("TITLE").HasMaxLength(1024).IsUnicode(false);
});
}
}
public class Test
{
public long Id { get; set; }
public string Title { get; set; }
}I’ve tested this on Oracle.EntityFrameworkCore version 8.23.50. The target is an Oracle 19 database, which I have also set the compatibility level to.