ORA-12704: Character Mismatch with Devart version 6.70

[hweidle]

We have the following LINQ statement:

var adAccount = account as ActiveDirectoryAccount;
return context.Accounts.OfType()
.Where(a => a.Sid == adAccount.Sid)
.Include(a => a.Settings)
.Include(a => a.SearchPatterns)
.SingleOrDefault();

Results in the following SQL-Statement:

SELECT
"UnionAll1".ID AS C1,
"UnionAll1".ID1 AS C2,
"UnionAll1".C2 AS C3,
"UnionAll1".ID2 AS C4,
"UnionAll1".CODE AS C5,
"UnionAll1".ACTIVE_F AS C6,
"UnionAll1".SID AS C7,
"UnionAll1".C1 AS C8,
"UnionAll1".ID3 AS C9,
"UnionAll1".ACCNT_ID AS C10,
"UnionAll1".KEYCODE AS C11,
"UnionAll1".VALUE AS C12,
"UnionAll1".C3 AS C13,
"UnionAll1".C4 AS C14,
"UnionAll1".C5 AS C15,
"UnionAll1".C6 AS C16,
"UnionAll1".C7 AS C17,
"UnionAll1".C8 AS C18,
"UnionAll1".C9 AS C19,
"UnionAll1".C10 AS C20
FROM (SELECT
CASE WHEN "Extent3".ID IS NULL THEN TO_NUMBER(NULL) ELSE 1 END AS C1,
"Extent2".ID,
"Extent1".ID AS ID1,
'0X0X' AS C2,
"Extent1".ID AS ID2,
"Extent2".CODE,
"Extent2".ACTIVE_F,
"Extent1".SID,
"Extent3".ID AS ID3,
"Extent3".ACCNT_ID,
"Extent3".KEYCODE,
"Extent3".VALUE,
TO_NUMBER(NULL) AS C3,
TO_NCHAR(NULL) AS C4,
TO_NUMBER(NULL) AS C5,
TO_NUMBER(NULL) AS C6,
TO_NCHAR(NULL) AS C7,
TO_NUMBER(NULL) AS C8,
TO_BLOB(NULL) AS C9,
TO_NUMBER(NULL) AS C10
FROM TICON.ACTAD0E "Extent1"
INNER JOIN TICON.ACCNT "Extent2" ON "Extent1".ID = "Extent2".ID
LEFT OUTER JOIN TICON.ACTST "Extent3" ON "Extent1".ID = "Extent3".ACCNT_ID
WHERE "Extent1".SID = :p__linq__0
UNION ALL
SELECT
2 AS C1,
"Extent5".ID,
"Extent4".ID AS ID1,
'0X0X' AS C2,
"Extent4".ID AS ID2,
"Extent5".CODE,
"Extent5".ACTIVE_F,
"Extent4".SID,
TO_NUMBER(NULL) AS C3,
TO_NUMBER(NULL) AS C4,
TO_NCHAR(NULL) AS C5,
TO_NCHAR(NULL) AS C6,
"Extent6".ID AS ID3,
"Extent6".CODE AS CODE1,
"Extent6".TYPE_S,
"Extent6".DISPLAY_TYPE_S,
"Extent6".VALID_COLUMNS,
"Extent6".AUTO_SEARCH_F,
"Extent6".QUERY_BLOB,
"Extent6".ACCNT_ID
FROM TICON.ACTAD0E "Extent4"
INNER JOIN TICON.ACCNT "Extent5" ON "Extent4".ID = "Extent5".ID
INNER JOIN TICON.LISTD "Extent6" ON "Extent4".ID = "Extent6".ACCNT_ID
WHERE "Extent4".SID = :p__linq__0) "UnionAll1"
ORDER BY "UnionAll1".ID ASC, "UnionAll1".ID2 ASC, "UnionAll1".C1 ASC

Previous working version was 6.50 with the following SQL-Statement:

SELECT
"UnionAll1".ID AS C1,
"UnionAll1".ID1 AS C2,
"UnionAll1".C2 AS C3,
"UnionAll1".ID2 AS C4,
"UnionAll1".CODE AS C5,
"UnionAll1".ACTIVE_F AS C6,
"UnionAll1".SID AS C7,
"UnionAll1".C1 AS C8,
"UnionAll1".ID3 AS C9,
"UnionAll1".ACCNT_ID AS C10,
"UnionAll1".KEYCODE AS C11,
"UnionAll1".VALUE AS C12,
"UnionAll1".C3 AS C13,
"UnionAll1".C4 AS C14,
"UnionAll1".C5 AS C15,
"UnionAll1".C6 AS C16,
"UnionAll1".C7 AS C17,
"UnionAll1".C8 AS C18,
"UnionAll1".C9 AS C19,
"UnionAll1".C10 AS C20
FROM (SELECT
CASE WHEN "Extent3".ID IS NULL THEN TO_NUMBER(NULL) ELSE 1 END AS C1,
"Extent2".ID,
"Extent1".ID AS ID1,
'0X0X' AS C2,
"Extent1".ID AS ID2,
"Extent2".CODE,
"Extent2".ACTIVE_F,
"Extent1".SID,
"Extent3".ID AS ID3,
"Extent3".ACCNT_ID,
"Extent3".KEYCODE,
"Extent3".VALUE,
TO_NUMBER(NULL) AS C3,
TO_CHAR(NULL) AS C4,
TO_NUMBER(NULL) AS C5,
TO_NUMBER(NULL) AS C6,
TO_CHAR(NULL) AS C7,
TO_NUMBER(NULL) AS C8,
TO_BLOB(NULL) AS C9,
TO_NUMBER(NULL) AS C10
FROM TICON.ACTAD0E "Extent1"
INNER JOIN TICON.ACCNT "Extent2" ON "Extent1".ID = "Extent2".ID
LEFT OUTER JOIN TICON.ACTST "Extent3" ON "Extent1".ID = "Extent3".ACCNT_ID
WHERE "Extent1".SID = :p__linq__0
UNION ALL
SELECT
2 AS C1,
"Extent5".ID,
"Extent4".ID AS ID1,
'0X0X' AS C2,
"Extent4".ID AS ID2,
"Extent5".CODE,
"Extent5".ACTIVE_F,
"Extent4".SID,
TO_NUMBER(NULL) AS C3,
TO_NUMBER(NULL) AS C4,
TO_CHAR(NULL) AS C5,
TO_CHAR(NULL) AS C6,
"Extent6".ID AS ID3,
"Extent6".CODE AS CODE1,
"Extent6".TYPE_S,
"Extent6".DISPLAY_TYPE_S,
"Extent6".VALID_COLUMNS,
"Extent6".AUTO_SEARCH_F,
"Extent6".QUERY_BLOB,
"Extent6".ACCNT_ID
FROM TICON.ACTAD0E "Extent4"
INNER JOIN TICON.ACCNT "Extent5" ON "Extent4".ID = "Extent5".ID
INNER JOIN TICON.LISTD "Extent6" ON "Extent4".ID = "Extent6".ACCNT_ID
WHERE "Extent4".SID = :p__linq__0) "UnionAll1"
ORDER BY "UnionAll1".ID ASC, "UnionAll1".ID2 ASC, "UnionAll1".C1 ASC

The main difference is TO_NCHAR instead of TO_CHAR within the union. Even changing the column type in database to NCHAR or NVARCHAR2 does not fix the problem.

[Shalex]

The change in behaviour is caused by the following fix in the 6.60.283 version: "The bug with creating columns of national data types (NCHAR/NVARCHAR2/NCLOB) in Entity Framework Code-First CreateDatabase is fixed". Current behaviour is more correct from the Entity Framework point of view.

The issue is discussed at http://www.devart.com/forums/viewtopic.php?t=23267. So, you can set the HasMaxLength(100) and IsUnicode(false) attributes for the corresponding property. Also we recommend to specify the server data type explicitly.