Serialization Mode = Unidirectional Causes Unnecessary Query

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
zsessary
Posts: 10
Joined: Thu 25 Mar 2010 20:05

Serialization Mode = Unidirectional Causes Unnecessary Query

Post by zsessary » Thu 05 May 2011 21:09

Here's the example to produce the bug...

Tables:
ParentTable
ChildInsertTable
ChildOtherTable

Dim dc As New Data.TestDataContext()
databaseMonitor.IsActive = True

Dim parent = dc.Parenttables.FirstOrDefault()

Dim insert As New Data.Childinserttable()
parent.Childinserttables.Add(insert)
insert.Mydata = "My Insert"

dc.SubmitChanges()

If Serialization Mode = Unidirectional then, for no apparent reason, the above code will perform a query of Childothertable as follows

SELECT t1.ID, t1.MYDATA, t1.PARENTTABLEID
FROM ZSESSARY.CHILDOTHERTABLE t1
WHERE :np0 = t1.PARENTTABLEID

where paremeter :np0 is the PK of parent.

However, if you leave the default Serialization Mode = None, this pointless query is not performed.

I can email a solution demonstrating the issue. The SQL to generate my example schema is posted at the end.

One of my team members is doing work that requires serialization of the data objects, but pointlessly querying hundreds or even hundreds of thousands of child records is not going to work for us.

Thanks,
Zach

/*
-- Drop talbes and values
DROP TABLE PARENTTABLE CASCADE CONSTRAINTS PURGE;
DROP TABLE CHILDINSERTTABLE CASCADE CONSTRAINTS PURGE;
DROP TABLE CHILDOTHERTABLE CASCADE CONSTRAINTS PURGE;

-- Drop sequences
DROP SEQUENCE PARENTTABLE_SEQ;
DROP SEQUENCE CHILDINSERTTABLE_SEQ;
DROP SEQUENCE CHILDOTHERTABLE_SEQ;
*/

-- Create the Tables
CREATE TABLE parenttable (
id INTEGER PRIMARY KEY,
mydata VARCHAR2(50) NOT NULL
);

CREATE TABLE childinserttable (
id INTEGER PRIMARY KEY,
mydata VARCHAR2(50) NOT NULL,
parenttableid NUMBER NOT NULL,
CONSTRAINT parenttable_childinserttable
FOREIGN KEY (parenttableid)
REFERENCES parenttable (id)
);

CREATE TABLE childothertable (
id INTEGER PRIMARY KEY,
mydata VARCHAR2(50) NOT NULL,
parenttableid NUMBER NOT NULL,
CONSTRAINT parenttable_childothertable
FOREIGN KEY (parenttableid)
REFERENCES parenttable (id)
);

-- Create the Sequences
CREATE SEQUENCE PARENTTABLE_SEQ NOCACHE START WITH 2;
CREATE SEQUENCE CHILDINSERTTABLE_SEQ NOCACHE START WITH 2;
CREATE SEQUENCE CHILDOTHERTABLE_SEQ NOCACHE START WITH 4;

-- Create the Triggers
CREATE TRIGGER PARENTTABLE_BEFORE_INSERT
BEFORE INSERT ON PARENTTABLE FOR EACH ROW
BEGIN
-- If not provided, auto-generate ID
IF :NEW.ID IS NULL THEN
SELECT PARENTTABLE_SEQ.NEXTVAL
INTO :NEW.ID FROM DUAL;
END IF;
END;
/

CREATE TRIGGER CHILDINSERTTABLE_BEFORE_INSERT
BEFORE INSERT ON CHILDINSERTTABLE FOR EACH ROW
BEGIN
-- If not provided, auto-generate ID
IF :NEW.ID IS NULL THEN
SELECT CHILDINSERTTABLE_SEQ.NEXTVAL
INTO :NEW.ID FROM DUAL;
END IF;
END;
/

CREATE TRIGGER CHILDOTHERTABLE_BEFORE_INSERT
BEFORE INSERT ON CHILDOTHERTABLE FOR EACH ROW
BEGIN
-- If not provided, auto-generate ID
IF :NEW.ID IS NULL THEN
SELECT CHILDOTHERTABLE_SEQ.NEXTVAL
INTO :NEW.ID FROM DUAL;
END IF;
END;
/

-- Insert initial Values
INSERT INTO PARENTTABLE VALUES (1, 'Parent Value 1');
INSERT INTO CHILDINSERTTABLE VALUES (1, 'Insert Value 1', 1);
INSERT INTO CHILDOTHERTABLE VALUES (1, 'Other Value 1', 1);
INSERT INTO CHILDOTHERTABLE VALUES (2, 'Other Value 2', 1);
INSERT INTO CHILDOTHERTABLE VALUES (3, 'Other Value 3', 1);

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 06 May 2011 10:50

Thank you for the report, we have reproduced the issue. Apparently, the problem is caused by the following line in the generated code (please see the [Your]DataContext.designer.cs file):

Code: Select all

If (Me.serializing And (Not Me._Childothertables.HasLoadedOrAssignedValues  AND (Me._Childothertables.IsDeferred OR Me._Childothertables.Count = 0)))
Visual Basic always checks both sides of a binary operator, even if the result is evident from the left side only. Thus, the inner Count method will be executed, leading to fetching all Childothertable entities from the database.

We will consider changing our VB code generation template, and post here about the results. As a temporary workaround, you can manually change the code in this place to something like

Code: Select all

If Me.serializing 
 If Not Me._Childothertables.HasLoadedOrAssignedValues 
  If (Me._Childothertables.IsDeferred OR Me._Childothertables.Count = 0)
   ...
  End If
 End If
End If
Please tell us if this helps.

zsessary
Posts: 10
Joined: Thu 25 Mar 2010 20:05

Post by zsessary » Fri 06 May 2011 17:51

The obvious downside to the proposed solution is that my true database schema has about 50 tables in it. That means a lot of modification. If the problem is simple, why can't the designer use "AndAlso" instead of "And"? Same for the use of "Or" vs "OrElse".

Would it be a good temporary solution for me to modify the template file, line 1135, from

Code: Select all

If (Me.serializing And (Not Me._.HasLoadedOrAssignedValues  AND (Me._.IsDeferred OR Me._.Count = 0)))
to

Code: Select all

If (Me.serializing AndAlso (Not Me._.HasLoadedOrAssignedValues  AndAlso (Me._.IsDeferred OrElse Me._.Count = 0)))
???

I don't like being in this template customizing business, so hopefully a future release will resolve this issue for us.

Since I have you on the line, I also want to suggest that you come up with a different solution for templates other than using the registry entry. It's problematic when you have several developers. Maybe a relative path in the .edps file or something along those lines?

Thanks,
Zach

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Tue 10 May 2011 15:12

You are right, AndAlso and OrElse operators should be used in this line. We have fixed the code generation templates, now the .Count method should not be executed unless necessary. This change will be available in the nearest build, which we plan to release in about a week.

As for custom templates, registry entries are no more created for them in the 6.30 Beta version of dotConnect for Oracle. For the detailed information about the improvements (in particular, the ones related to working with templates) available in the Beta version, please refer to
http://www.devart.com/forums/viewtopic.php?t=20828

zsessary
Posts: 10
Joined: Thu 25 Mar 2010 20:05

Post by zsessary » Tue 10 May 2011 16:32

It all sounds good. Thanks!

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Thu 19 May 2011 11:24

We have released the new 6.30.160 build of dotConnect for Oracle that includes the fixed VB code generation template. 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

Post Reply