character set mismatch due to wrongly generated query
Posted: Fri 14 Aug 2015 13:53
Hi,
I have a database built using model first and am now replacing the C# code to a code first approach.
.NET string properties are mapped to NVARCHAR2 (usually) or CHAR for fixed-length non-Unicode strings; but for key properties both Ends are always of the same Format.
Nevertheless I came across one Problem I could not solve yet.
Let's look at the following C# code to load data from the DbContext:
It generates the following, but invalid, Oracle SQL query
The table fields CRITERIONGROUPS.SHORTDESC and CRITERIONGROUPS.LONGDESC are of type NVARCHAR2, but as the NULL of the first dataset of the Union Operation is converted by TO_CHAR(NULL) the query as a whole Fails obviously due to a character set mismatch, as NVARCHAR2 allows Unicode characters while CHAR does not.
I tried different ways to solve that already.
First shot:
Using the Devart Attribute Devart.Data.Linq.Mapping.Column which is from the underlying data Provider allows to specify the database type, this didn't work.
Second shot:
Using the OnModelCreating() function overwrite I tried it with
The outcome of this is interesting but didn't help, it depended on the Parameter given to the HasColumnType function:
NVARCHAR2 leads to the nulls being converted with TO_NCHAR (which is still the wrong type).
NCLOB leads to the nulls being converted by TO_NCLOB (wrong again),
CHAR leads to conversion using TO_CHAR(),
But how to get it converted to NVARCHAR2 or at least any type that would work?
regards and thanks
jongleur
I have a database built using model first and am now replacing the C# code to a code first approach.
.NET string properties are mapped to NVARCHAR2 (usually) or CHAR for fixed-length non-Unicode strings; but for key properties both Ends are always of the same Format.
Nevertheless I came across one Problem I could not solve yet.
Let's look at the following C# code to load data from the DbContext:
Code: Select all
await context.Plants.Where(p => p.plid == plant.plid)
.Include(p => p.CMWorkDays)
.Include(p => p.CriterionGroups)
.LoadAsync();
Code: Select all
SELECT
"UnionAll1".C2 AS C1,
"UnionAll1".PLNT_ID AS C2,
"UnionAll1".SHORTDESC AS C3,
"UnionAll1".PLANTNUMBER AS C4,
"UnionAll1".C1 AS C5,
"UnionAll1".CMWD_ID AS C6,
"UnionAll1".MONTH AS C7,
"UnionAll1".YEAR AS C8,
"UnionAll1".WORKDAYS AS C9,
"UnionAll1".PLNT_ID1 AS C10,
"UnionAll1".C3 AS C11,
"UnionAll1".C4 AS C12,
"UnionAll1".C5 AS C13,
"UnionAll1".C6 AS C14
FROM (SELECT
CASE WHEN "Extent2".YEAR IS NULL THEN TO_NUMBER(NULL) ELSE 1 END AS C1,
1 AS C2,
"Extent1".PLNT_ID,
"Extent1".SHORTDESC,
"Extent1".PLANTNUMBER,
"Extent2".CMWD_ID,
"Extent2".MONTH,
"Extent2".YEAR,
"Extent2".WORKDAYS,
"Extent2".PLNT_ID AS PLNT_ID1,
TO_NUMBER(NULL) AS C3,
TO_NUMBER(NULL) AS C4,
TO_CHAR(NULL) AS C5,
TO_CHAR(NULL) AS C6
FROM PLANTS "Extent1"
LEFT OUTER JOIN CMWORKDAYS "Extent2" ON "Extent1".PLNT_ID = "Extent2".PLNT_ID
WHERE "Extent1".PLNT_ID = :p__linq__0
UNION ALL
SELECT
2 AS C1,
2 AS C2,
"Extent3".PLNT_ID,
"Extent3".SHORTDESC,
"Extent3".PLANTNUMBER,
TO_NUMBER(NULL) AS C3,
TO_NUMBER(NULL) AS C4,
TO_NUMBER(NULL) AS C5,
TO_NUMBER(NULL) AS C6,
TO_NUMBER(NULL) AS C7,
"Extent4".CRGR_ID,
"Extent4".PLNT_ID AS PLNT_ID1,
"Extent4".SHORTDESC AS SHORTDESC1,
"Extent4".LONGDESC
FROM PLANTS "Extent3"
INNER JOIN CRITERIONGROUPS "Extent4" ON "Extent3".PLNT_ID = "Extent4".PLNT_ID
WHERE "Extent3".PLNT_ID = :p__linq__0) "UnionAll1"
ORDER BY "UnionAll1".PLNT_ID ASC, "UnionAll1".C1 ASC
I tried different ways to solve that already.
First shot:
Using the Devart Attribute Devart.Data.Linq.Mapping.Column which is from the underlying data Provider allows to specify the database type, this didn't work.
Second shot:
Using the OnModelCreating() function overwrite I tried it with
Code: Select all
modelBuilder.Properties()
.Where(p => p.PropertyType == typeof(string))
.Configure(p => p.HasColumnType("NVARCHAR2"));
NVARCHAR2 leads to the nulls being converted with TO_NCHAR (which is still the wrong type).
NCLOB leads to the nulls being converted by TO_NCLOB (wrong again),
CHAR leads to conversion using TO_CHAR(),
But how to get it converted to NVARCHAR2 or at least any type that would work?
regards and thanks
jongleur