dotConnect 8.3.146: Count in subquery breaking

[thakkarmayank]

Hi,
I just upgraded to dotConnect 8.3.146(Trial version) from 6.80. (I had to upgrade in order to fix the issue mentioned at http://forums.devart.com/viewtopic.php?f=1&t=29443.)
I am using VS 2010, EF 4.1 and .Net Framework 4

I ran into this breaking change:

The following query, which was working fine in 6.80, is now showing incorrect results.

Code: Select all

var lstUsers = (from cu in UserEntities
 				select new
				{
				user = cu,
				StateLicenses1 = (from sl in StateLicenseHistoryEntities
												where cu.UID == sl.UserUID
											select sl).Count() 
				}).ToList();

The "StateLicenses1" property returns "1" for all non-zero counts. It should return the actual count of child rows in the "StateLicenseHistoryEntities" table.

I read the release history but did not find anything regarding this change.

Am I missing something or have I stumbled into another ugly bug?

~Mayank

[Shalex]

Please send us a small test project with the corresponding DDL/DML script to reproduce the issue in our environment.

[thakkarmayank]

Shalex,
This is a simple parent-child table relationship.
Parent table can a User table with USerUID as PK and some other attributes
Child table can be any table with ParentTable.UserUID as a FK.

Let me know if this doesnt work and I can send you some scripts.

~Mayank

[Shalex]

Please send us a small test project with the corresponding DDL/DML script to reproduce the issue in our environment.

[thakkarmayank]

Here are the scripts:

Code: Select all

CREATE TABLE "DBO"."USER" 
   (	"USER_UID" NUMBER(10,0) NOT NULL ENABLE, 
	"USER_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"USER_PSW" VARCHAR2(100 BYTE), 
	"USER_DEFAULT_ROLE_TYPE" VARCHAR2(10 BYTE) NOT NULL ENABLE, 
  "USER_FIRST_NAME" VARCHAR2(25 BYTE) NOT NULL ENABLE, 
	"USER_LAST_NAME" VARCHAR2(25 BYTE) NOT NULL ENABLE, 
		 PRIMARY KEY ("USER_UID") ENABLE
     ) ;

  CREATE TABLE "DBO"."USOL_USR_STATE_LICENSE_HIST"
  (
    "USOL_UID"      NUMBER(10,0),
    "USOL_USER_UID" NUMBER(10,0) NOT NULL ENABLE,
    "USOL_SOL"      VARCHAR2(6 BYTE) NOT NULL ENABLE,
    "USOL_BEGIN_DATE" DATE NOT NULL ENABLE,
    "USOL_END_DATE" DATE NOT NULL ENABLE
    PRIMARY KEY ("USOL_UID")  ENABLE,
    FOREIGN KEY ("USOL_USER_UID") REFERENCES "DBO"."USER" ("USER_UID") ENABLE
  );
  
  
  
  
  
INSERT
INTO DBO.USER
  (
    USER_UID,
    USER_NAME,
    USER_PSW,
    USER_DEFAULT_ROLE_TYPE,
    USER_FIRST_NAME,
    USER_LAST_NAME  
  )
  VALUES
  (
    1,
    'Test1',
    'test123',
    'EMP',
    'Test',
    '1'  
  );
  
  INSERT
INTO DBO.USER
  (
    USER_UID,
    USER_NAME,
    USER_PSW,
    USER_DEFAULT_ROLE_TYPE,
    USER_FIRST_NAME,
    USER_LAST_NAME  
  )
  VALUES
  (
    2,
    'Test2',
    'test123',
    'EMP',
    'Test',
    '2'  
  );
  
  INSERT
INTO DBO.USER
  (
    USER_UID,
    USER_NAME,
    USER_PSW,
    USER_DEFAULT_ROLE_TYPE,
    USER_FIRST_NAME,
    USER_LAST_NAME  
  )
  VALUES
  (
    3,
    'Test3',
    'test123',
    'EMP',
    'Test',
    '3'  
  );
  
  
INSERT
INTO USOL_USR_STATE_LICENSE_HIST
  (
    USOL_UID,
    USOL_USER_UID,
    USOL_SOL,
    USOL_BEGIN_DATE,
    USOL_END_DATE
    
  )
  VALUES
  (
    1,
    1,
    'CT',
    '01-JAN-2013',
    '31-DEC-2099'
  );


INSERT
INTO USOL_USR_STATE_LICENSE_HIST
  (
    USOL_UID,
    USOL_USER_UID,
    USOL_SOL,
    USOL_BEGIN_DATE,
    USOL_END_DATE
    
  )
  VALUES
  (
    2,
    1,
    'NY',
    '01-JAN-2013',
    '31-DEC-2099'
  );


INSERT
INTO USOL_USR_STATE_LICENSE_HIST
  (
    USOL_UID,
    USOL_USER_UID,
    USOL_SOL,
    USOL_BEGIN_DATE,
    USOL_END_DATE
    
  )
  VALUES
  (
    3,
    3,
    'CT',
    '01-JAN-2013',
    '31-DEC-2099'
  );

[thakkarmayank]

Shalex,
I have uploaded a test project on the link provided. The DB scripts, referenced in the above post, are also included. The DB scripts and edmx file in the project may be out of sync, as I wont be able to provide the actual DB table scripts, due to some restrictions here.

You can tailor the edmx file to match the scripts and you should be able to reproduce the issue.

Please let me know.

[Shalex]

Thank you for your report. We have reproduced the issue and are investigating it.

[Shalex]

The bug with generating extra condition for limiting subselect by one record in generated subqueries with grouping is fixed. We will notify you when the corresponding build of dotConnect for Oracle is available for download.

[Shalex]

New version of dotConnect for Oracle 8.4 is released!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=29687.