character set mismatch due to wrongly generated query

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
jongleur
Posts: 17
Joined: Thu 11 Dec 2014 09:42

character set mismatch due to wrongly generated query

Post by jongleur » 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:

Code: Select all

await context.Plants.Where(p => p.plid == plant.plid)
             .Include(p => p.CMWorkDays)
             .Include(p => p.CriterionGroups)
             .LoadAsync();
It generates the following, but invalid, Oracle SQL query

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
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

Code: Select all

modelBuilder.Properties()
        .Where(p => p.PropertyType == typeof(string))
        .Configure(p => p.HasColumnType("NVARCHAR2"));
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

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: character set mismatch due to wrongly generated query

Post by Shalex » Tue 18 Aug 2015 07:49

1. Please specify the exact text of the error and its call stack with all inner exceptions.
2. Have you tried using the newest (8.5.464) build of dotConnect for Oracle?
3. Send us a small test project with the corresponding DDL/DML script for reproducing the issue in our environment.

jongleur
Posts: 17
Joined: Thu 11 Dec 2014 09:42

Re: character set mismatch due to wrongly generated query

Post by jongleur » Wed 19 Aug 2015 13:18

Hi again,
took a while to delete all Overhead of the Project, but here are the results.

Detail of the error message (no, there is no inner exception available, and the stacktrace is that empty):

Code: Select all

Devart.Data.Oracle.OracleException occurred
  _HResult=-2147467259
  _message=ORA-01790: expression must have same datatype as corresponding expression
  HResult=-2147467259
  IsTransient=false
  Message=ORA-01790: expression must have same datatype as corresponding expression
  Source=Devart.Data.Oracle
  ErrorCode=-2147467259
  Code=1790
  IsRecoverable=false
  Offset=666
  StackTrace:
       bei Devart.Data.Oracle.an.d()
  InnerException: 
I can reproduced the bug with dotconnect 8.5.464.6 as well as one 8.4.x build.

Faulty DML generated is:

Code: Select all

SELECT 
"UnionAll1".C2 AS C1,
"UnionAll1".PRSC_ID AS C2,
"UnionAll1".SHORTDESC AS C3,
"UnionAll1".PRSC_KEY AS C4,
"UnionAll1".C1 AS C5,
"UnionAll1".PSPL_ID AS C6,
"UnionAll1".IS_RELEVANT4CRITDEF AS C7,
"UnionAll1".PRSC_ID1 AS C8,
"UnionAll1".PLNT_ID AS C9,
"UnionAll1".C3 AS C10,
"UnionAll1".C4 AS C11,
"UnionAll1".C5 AS C12,
"UnionAll1".C6 AS C13
FROM  (SELECT 
	CASE WHEN "Extent2".PSPL_ID IS NULL THEN TO_NUMBER(NULL) ELSE 1 END AS C1,
	1 AS C2,
	"Extent1".PRSC_ID,
	"Extent1".SHORTDESC,
	"Extent1".PRSC_KEY,
	"Extent2".PSPL_ID,
	"Extent2".IS_RELEVANT4CRITDEF,
	"Extent2".PRSC_ID AS PRSC_ID1,
	"Extent2".PLNT_ID,
	TO_NUMBER(NULL) AS C3,
	TO_NCLOB(NULL) AS C4,
	TO_NUMBER(NULL) AS C5,
	TO_NCLOB(NULL) AS C6
	FROM  PRODUCTIONSCOPES "Extent1"
	LEFT OUTER JOIN PRODUCTIONSCOPE_PLANT "Extent2" ON "Extent1".PRSC_ID = "Extent2".PRSC_ID
UNION ALL
	SELECT 
	2 AS C1,
	2 AS C2,
	"Extent3".PRSC_ID,
	"Extent3".SHORTDESC,
	"Extent3".PRSC_KEY,
	TO_NUMBER(NULL) AS C3,
	TO_NUMBER(NULL) AS C4,
	TO_NUMBER(NULL) AS C5,
	TO_NUMBER(NULL) AS C6,
	"Join2".SUSE_ID1 AS SUSE_ID,
	"Join2".NAMEENUM,
	"Join2".PLNT_ID,
	"Join2".PRODLAYOUTMAPPINGID
	FROM  PRODUCTIONSCOPES "Extent3"
	INNER JOIN  (SELECT 
		"Extent4".PRSC_ID,
		"Extent4".SUSE_ID AS SUSE_ID2,
		"Extent5".SUSE_ID AS SUSE_ID1,
		"Extent5".NAMEENUM,
		"Extent5".PLNT_ID,
		"Extent5".PRODLAYOUTMAPPINGID
		FROM  PRODUCTIONSCOPE_SUBSECTION "Extent4"
		INNER JOIN SUBSECTIONS "Extent5" ON "Extent5".SUSE_ID = "Extent4".SUSE_ID ) "Join2" ON "Extent3".PRSC_ID = "Join2".PRSC_ID) "UnionAll1"
ORDER BY "UnionAll1".PRSC_ID ASC, "UnionAll1".C1 ASC
Test Project code has been sent as well.

Thanks
jongleur

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: character set mismatch due to wrongly generated query

Post by Shalex » Fri 21 Aug 2015 11:50

jongleur wrote:Test Project code has been sent as well.
We have asked you by email to resend a test project to our ftp server.

jongleur
Posts: 17
Joined: Thu 11 Dec 2014 09:42

Re: character set mismatch due to wrongly generated query

Post by jongleur » Mon 24 Aug 2015 07:21

uploaded it.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: character set mismatch due to wrongly generated query

Post by Shalex » Tue 25 Aug 2015 11:52

Thank you for the test project.

The issue is caused by incomplete mapping. There are two alternative ways to solve the problem:

1. Specify the types of target columns in the mapping (recommended). Use

Code: Select all

        [Column("NAMEENUM", TypeName = "varchar2")]
        public virtual string nameEnum
...
        [Column("PRODLAYOUTMAPPINGID", TypeName = "varchar2")]
        public virtual string ProductionLayoutMappingId
instead of

Code: Select all

        [Column("NAMEENUM")]
        public virtual string nameEnum
...
        [Column("PRODLAYOUTMAPPINGID")]
        public virtual string ProductionLayoutMappingId
2. Use the following options (not recommended)

Code: Select all

    var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
    config.CodeFirstOptions.UseNonUnicodeStrings = true;
    config.CodeFirstOptions.UseNonLobStrings = true;
JIC: the predefined DbContext template generates the most complete mapping (exact data types, etc).

Post Reply