character set mismatch due to wrongly generated query

character set mismatch due to wrongly generated query

Postby 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
jongleur
 
Posts: 17
Joined: Thu 11 Dec 2014 09:42

Re: character set mismatch due to wrongly generated query

Postby 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.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: character set mismatch due to wrongly generated query

Postby 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
jongleur
 
Posts: 17
Joined: Thu 11 Dec 2014 09:42

Re: character set mismatch due to wrongly generated query

Postby 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.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: character set mismatch due to wrongly generated query

Postby jongleur » Mon 24 Aug 2015 07:21

uploaded it.
jongleur
 
Posts: 17
Joined: Thu 11 Dec 2014 09:42

Re: character set mismatch due to wrongly generated query

Postby 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).
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle