New LoadWith bug on version 6.10

[bmarotta]

Hi,

There is a new bug on the version 6.10 not found on version 5.70.

If you have a Master/Detail table and you use the LoadWith, the correct values are not grabbed.

Something like this will throw an exception:

Code: Select all

var dictionary = new Dictionary();
foreach (var m in context.Masters.LoadWith(n => n.Detail))
  dictionary.Add(m.Id, m);

For some reason the Id comes more than once. This happened in, at least, two parts of the code, so it is not really linked to the table structure. I am sure that on the DB, the Id does not happen more than once as it is the PK of the table.

As an example, this is the query generated by Devart

Code: Select all

SELECT t1.ID, t1.NAME, t1.KIND_ID, t2.ID AS ID1, t2.STATUS_ID, t2.VALUE, t2.ORDER_SEQUENCE
FROM PUMA_STATUS_DEFINITION t1
LEFT OUTER JOIN PUMA_STATUS_VALUE_DEFINITION t2 ON t1.ID = t2.STATUS_ID
-- Context: Devart.Data.Oracle.Linq.Provider.OracleDataProvider Model: ao Build: 2.20.11.0

For us this is a show stopper, as we cannot replace these cases all over the code.

[StanislavK]

I've sent you a test project in a letter, please check that it was not blocked by your mail filter. Please specify what should be changed in the sample.

There are, in fact, some situations in which values in primary key column may be non-unique. For example, this may occur when using the Direct Path Load interface (e.g., via the OracleLoader component). The sample includes a commented block of code where duplicate rows are inserted with OracleLoader. To check if this is the case, please try retrieving all rows with Id equal to the one with which you are getting the error.

[StanislavK]

Thank you for your assistance, we have reproduced the issue. We will inform you as soon as it is fixed.

[bmarotta]

Did you ve fixed this on the new version?

[StanislavK]

We did not fix this issue yet, we will post here when the fix is available.

[StanislavK]

We have analyzed the issue. Its cause is that Oracle may fetch data unordered unless ORDER BY conditions are explicitly specified. Hence, a large result of a query with joins may contain a pack of joined rows with one master id (e.g., 100), then a pack with another (e.g., 110), and then again a pack with master id = 100. As the query result set is not cached but read sequentially instead, the same master entity is returned several times when enumerating the sequence.

To resolve the issue, you can order the result set before enumerating it:

Code: Select all

var dictionary = new Dictionary(); 
foreach (var m in context.Masters.LoadWith(n => n.Detail).OrderBy(n => n.Id)) 
  dictionary.Add(m.Id, m);

[bmarotta]

Hi Stanislav,

of course the results come unordered.

But you have to fix it on the Devart engine. If you are doing optimizations to get the whole results in only one query, you have to handle it correctly. Either you add the order by, or you make the parsing of the results smart enough to handle the unordered set.

[StanislavK]

We will consider adding implicit ordering by the master key in such queries, and inform you about the results.

[StanislavK]

We have implemented implicit ordering in such queries. This change is available in the new 6.10.121 build of dotConnect for Oracle. The new build can be downloaded 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 fixes and improvements available in dotConnect for Oracle 6.10.121, please refer to
http://www.devart.com/forums/viewtopic.php?t=20472