Devart Forums

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

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): 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 Please tell us if this helps.

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

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

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

It all sounds good. Thanks!

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