Devart Forums

Posted: **Tue 26 Apr 2011 13:28**

Hello,
I have an issue when using the Include statement.
When I try to include more then 2 relations there seems to be a problem:

These are fine:

Code: Select all

Context.Table.Include("Table1").Include("Table2").FirstOrDefault();
Context.Table.Include("Table1").Include("Table3").FirstOrDefault();
Context.Table.Include("Table2").Include("Table3").FirstOrDefault();

But this one generates an error message {"ORA-01790: expression must have same datatype as corresponding expression"}:

Code: Select all

Context.Table.Include("Table1").Include("Table2").Include("Table3").FirstOrDefault();

After investigating the sql (ToTraceString of the ObjectQuery) that is generated I found the reason for the error:
The underlying query unions all tables, to have the same column format it will generate all columns for each table and set them to null if not applicable, for instance:

Code: Select all

select field1, field2, null, null from table1
union all
select null, null, field1, null from table2

This way, oracle automatically gets the datatype from the column that is not null and unions without a problem for 2 tables.

I would expect the query form 3 tables to be the same:

Code: Select all

select field1, field2, null, null from table1
union all
select null, null, field1, null from table2
union all
select null, null, null, field from table3

But...for three tables the generated query is different:

Code: Select all

select ... from (select field1, field2, null, null from table1
                      union all
                      select null, null, field1, null from table2)
union all
select null, null, null, field1 from table3

This causes the datatype error, for some reason Oracle does not set the correct datatype for the column of table3.
I would like to know if there is another or better way to use multiple includes or if there is a workaround.
Maybe someone can help, because I am stuck.
Thanks,
Femke

Posted: **Tue 26 Apr 2011 14:20**

We have fixed the problem of type mismatch in the recent 6.30.145 build of dotConnect for Oracle. It can be downloaded from here (the trial version) or from Registered Users' Area (provided that you have an active subscription).
For more information about the fixes and improvements available in dotConnect for Oracle Beta, please refer to
this announcement.
Please let us know if the problem persists.

Posted: **Wed 27 Apr 2011 07:14**

Hi Andrey,
Thank you for replying. I've installed the latest build of dotConnect and now run into a different problem that previously worked fine, I'm adding my code so you can see what happens
I'm using the include:

Code: Select all

EFContext.PrimingRuns.Include("PrimingDevices.PrimingRegistrations.PrimingRegistrationSamples").Where(p => p.OrderNo == orderNo & p.RunNumber == runNumber);

This generates the sql:

Code: Select all

  SELECT Project1.RUN_NUMBER,
         Project1.ORDER_NO,
         Project1.START_WEIGHT_STEEPING,
         Project1.END_WEIGHT_STEEPED_DRIED,
         Project1.STEEPING_REMARK,
         Project1.END_WEIGHT,
         Project1.SAMPLE_TO_LAB,
         Project1.CREATION_DATE,
         Project1.LAST_MODIFIED,
         Project1.OPERATOR,
         Project1.STEEPING_START,
         Project1.STEEPING_END,
         Project1.DRYING_START,
         Project1.DRYING_END,
         Project1.STEEPING_DEVICE_ID,
         Project1.DRYING_STEEPED_DEVICE_ID,
         Project1.DRYING_PRIMED_DEVICE_ID,
         Project1.PRIMING_START,
         Project1.PRIMING_END,
         Project1.DRYING_REMARK,
         Project1.PRIMING_REMARK,
         Project1.PRIMING_START_WATER_ADDED,
         Project1.C3 AS C1,
         Project1.RUN_NUMBER1,
         Project1.ORDER_NO1,
         Project1.PRIMING_DEVICE_NR,
         Project1.DEVICE_NAME,
         Project1.CREATION_DATE1,
         Project1.LAST_MODIFIED1,
         Project1.OPERATOR1,
         Project1.C2,
         Project1.RUN_NUMBER2,
         Project1.ORDER_NO2,
         Project1.PRIMING_DEVICE_NR1,
         Project1.PRIMING_REGISTRATION_NR,
         Project1.START_WEIGHT,
         Project1.SEED_TEMP,
         Project1.SAMPLES_NR,
         Project1.CREATION_DATE2,
         Project1.LAST_MODIFIED2,
         Project1.OPERATOR2,
         Project1.C1 AS C3,
         Project1.ORDER_NO3,
         Project1.RUN_NUMBER3,
         Project1.PRIMING_DEVICE_NR2,
         Project1.PRIMING_REGISTRATION_NR1,
         Project1.SAMPLE_SEQUENCE,
         Project1.SAMPLE_VALID,
         Project1.WET_WEIGHT,
         Project1.DRY_WEIGHT,
         Project1.CREATION_DATE3,
         Project1.LAST_MODIFIED3,
         Project1.OPERATOR3
    FROM (SELECT Extent1.ORDER_NO,
                 Extent1.RUN_NUMBER,
                 Extent1.START_WEIGHT_STEEPING,
                 Extent1.END_WEIGHT_STEEPED_DRIED,
                 Extent1.STEEPING_REMARK,
                 Extent1.END_WEIGHT,
                 Extent1.SAMPLE_TO_LAB,
                 Extent1.CREATION_DATE,
                 Extent1.LAST_MODIFIED,
                 Extent1.OPERATOR,
                 Extent1.STEEPING_START,
                 Extent1.STEEPING_END,
                 Extent1.DRYING_START,
                 Extent1.DRYING_END,
                 Extent1.STEEPING_DEVICE_ID,
                 Extent1.DRYING_STEEPED_DEVICE_ID,
                 Extent1.DRYING_PRIMED_DEVICE_ID,
                 Extent1.PRIMING_START,
                 Extent1.PRIMING_END,
                 Extent1.DRYING_REMARK,
                 Extent1.PRIMING_REMARK,
                 Extent1.PRIMING_START_WATER_ADDED,
                 Join2.ORDER_NO AS ORDER_NO1,
                 Join2.RUN_NUMBER AS RUN_NUMBER1,
                 Join2.PRIMING_DEVICE_NR,
                 Join2.DEVICE_NAME,
                 Join2.CREATION_DATE AS CREATION_DATE1,
                 Join2.LAST_MODIFIED AS LAST_MODIFIED1,
                 Join2.OPERATOR AS OPERATOR1,
                 Join2.ORDER_NO1 AS ORDER_NO2,
                 Join2.RUN_NUMBER1 AS RUN_NUMBER2,
                 Join2.PRIMING_DEVICE_NR1 AS PRIMING_DEVICE_NR1,
                 Join2.PRIMING_REGISTRATION_NR,
                 Join2.START_WEIGHT,
                 Join2.SEED_TEMP,
                 Join2.SAMPLES_NR,
                 Join2.CREATION_DATE1 AS CREATION_DATE2,
                 Join2.LAST_MODIFIED1 AS LAST_MODIFIED2,
                 Join2.OPERATOR1 AS OPERATOR2,
                 Join2.ORDER_NO2 AS ORDER_NO3,
                 Join2.RUN_NUMBER2 AS RUN_NUMBER3,
                 Join2.PRIMING_DEVICE_NR2 AS PRIMING_DEVICE_NR2,
                 Join2.PRIMING_REGISTRATION_NR1 AS PRIMING_REGISTRATION_NR1,
                 Join2.SAMPLE_SEQUENCE,
                 Join2.SAMPLE_VALID,
                 Join2.WET_WEIGHT,
                 Join2.DRY_WEIGHT,
                 Join2.CREATION_DATE2 AS CREATION_DATE3,
                 Join2.LAST_MODIFIED2 AS LAST_MODIFIED3,
                 Join2.OPERATOR2 AS OPERATOR3,
                 CASE
                    WHEN Join2.ORDER_NO IS NULL THEN TO_NUMBER(NULL)
                    WHEN Join2.ORDER_NO1 IS NULL THEN TO_NUMBER(NULL)
                    WHEN Join2.RUN_NUMBER2 IS NULL THEN TO_NUMBER(NULL)
                    ELSE 1
                 END
                    AS C1,
                 CASE
                    WHEN Join2.ORDER_NO IS NULL THEN TO_NUMBER(NULL)
                    WHEN Join2.ORDER_NO1 IS NULL THEN TO_NUMBER(NULL)
                    ELSE 1
                 END
                    AS C2,
                 CASE
                    WHEN Join2.ORDER_NO IS NULL THEN TO_NUMBER(NULL)
                    ELSE 1
                 END
                    AS C3
            FROM    JIT.PRIMING_RUN Extent1
                 LEFT OUTER JOIN
                    (SELECT Extent2.ORDER_NO,
                            Extent2.RUN_NUMBER,
                            Extent2.PRIMING_DEVICE_NR,
                            Extent2.DEVICE_NAME,
                            Extent2.CREATION_DATE,
                            Extent2.LAST_MODIFIED,
                            Extent2.OPERATOR,
                            Join1.ORDER_NO1,
                            Join1.RUN_NUMBER1,
                            Join1.PRIMING_DEVICE_NR1,
                            Join1.PRIMING_REGISTRATION_NR,
                            Join1.START_WEIGHT,
                            Join1.SEED_TEMP,
                            Join1.SAMPLES_NR,
                            Join1.CREATION_DATE1,
                            Join1.LAST_MODIFIED1,
                            Join1.OPERATOR1,
                            Join1.ORDER_NO2,
                            Join1.RUN_NUMBER2,
                            Join1.PRIMING_DEVICE_NR2,
                            Join1.PRIMING_REGISTRATION_NR1,
                            Join1.SAMPLE_SEQUENCE,
                            Join1.SAMPLE_VALID,
                            Join1.WET_WEIGHT,
                            Join1.DRY_WEIGHT,
                            Join1.CREATION_DATE2,
                            Join1.LAST_MODIFIED2,
                            Join1.OPERATOR2
                       FROM    JIT.PRIMING_DEVICE Extent2
                            LEFT OUTER JOIN
                               (SELECT Extent3.ORDER_NO,
                                       Extent3.RUN_NUMBER,
                                       Extent3.PRIMING_DEVICE_NR,
                                       Extent3.PRIMING_REGISTRATION_NR,
                                       Extent3.START_WEIGHT,
                                       Extent3.SEED_TEMP,
                                       Extent3.SAMPLES_NR,
                                       Extent3.CREATION_DATE,
                                       Extent3.LAST_MODIFIED,
                                       Extent3.OPERATOR,
                                       Extent4.ORDER_NO AS ORDER_NO2,
                                       Extent4.RUN_NUMBER AS RUN_NUMBER2,
                                       Extent4.PRIMING_DEVICE_NR
                                          AS PRIMING_DEVICE_NR2,
                                       Extent4.PRIMING_REGISTRATION_NR
                                          AS PRIMING_REGISTRATION_NR1,
                                       Extent4.SAMPLE_SEQUENCE,
                                       Extent4.SAMPLE_VALID,
                                       Extent4.WET_WEIGHT,
                                       Extent4.DRY_WEIGHT,
                                       Extent4.CREATION_DATE AS CREATION_DATE2,
                                       Extent4.LAST_MODIFIED AS LAST_MODIFIED2,
                                       Extent4.OPERATOR AS OPERATOR2
                                  FROM    JIT.PRIMING_REGISTRATION Extent3
                                       LEFT OUTER JOIN
                                          JIT.PRIMING_REGISTRATION_SAMPLE Extent4
                                       ON (((Extent3.ORDER_NO =
                                                Extent4.ORDER_NO)
                                            AND (Extent3.RUN_NUMBER =
                                                    Extent4.RUN_NUMBER))
                                           AND (Extent3.PRIMING_DEVICE_NR =
                                                   Extent4.PRIMING_DEVICE_NR))
                                          AND (Extent3.PRIMING_REGISTRATION_NR =
                                                  Extent4.PRIMING_REGISTRATION_NR)) Join1
                            ON ((Extent2.ORDER_NO = Join1.ORDER_NO1)
                                AND (Extent2.RUN_NUMBER = Join1.RUN_NUMBER1))
                               AND (Extent2.PRIMING_DEVICE_NR =
                                       Join1.PRIMING_DEVICE_NR1)) Join2
                 ON (Extent1.ORDER_NO = Join2.ORDER_NO)
                    AND (Extent1.RUN_NUMBER = Join2.RUN_NUMBER)
           WHERE (Extent1.ORDER_NO = :p__linq__0)
                 AND (Extent1.RUN_NUMBER = :p__linq__1)) Project1
ORDER BY Project1.ORDER_NO ASC,
         Project1.RUN_NUMBER ASC,
         Project1.C3 ASC,
         Project1.PRIMING_DEVICE_NR ASC,
         Project1.ORDER_NO1 ASC,
         Project1.RUN_NUMBER1 ASC,
         Project1.C2 ASC,
         Project1.PRIMING_REGISTRATION_NR ASC,
         Project1.ORDER_NO2 ASC,
         Project1.RUN_NUMBER2 ASC,
         Project1.PRIMING_DEVICE_NR1 ASC,
         Project1.C1 ASC;

In this query the field JOIN1.PRIMING_DEVICE_NR1 is queried but this field does not exist in the JOIN1 query so I'm getting the error: ORA-00904: "JOIN1"."PRIMING_DEVICE_NR1": invalid identifier
Can you help out?
Thanks,
Femke

Posted: **Wed 27 Apr 2011 08:30**

Could you please send us a model the problem is reproducible on?

Posted: **Tue 03 May 2011 09:58**

Hi Andrey,
Thank you for replying. I've send an email to devart support containing the model that causes the problem.
Femke

Posted: **Wed 04 May 2011 15:10**

Thank you for the test project, we've reproduced the issue. We will investigate it and inform you about the results as soon as possible.

Posted: **Wed 04 May 2011 15:29**

Hi Stanislav,
Thank you for looking into the issue, will I have to wait for a new version? Are these released on a regular basis?
Regards,
Femke

Posted: **Thu 05 May 2011 13:22**

We have fixed the issue, the fix will be available in the nearest build. We plan to release the new build next week.

As for the releases periodicity, we usually publish new builds once per two weeks.

Posted: **Mon 09 May 2011 11:32**

Thanks!

Posted: **Thu 19 May 2011 11:23**

We have released the new 6.30.160 build of dotConnect for Oracle that includes the fix for this issue. The new build can be dowloaded from
http://www.devart.com/dotconnect/oracle/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For the detailed information about the improvements and fixes available in dotConnect for Oracle 6.30.160, please refer to
http://www.devart.com/forums/viewtopic.php?t=21027

Devart Forums

Error when using multiple includes

Error when using multiple includes