Bug in Where clauses

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
malea
Posts: 33
Joined: Fri 25 Jan 2013 08:25

Bug in Where clauses

Post by malea » Wed 06 Nov 2013 11:11

Hello,

I'm using dotConnect for Oracle 7.7.267 in my project.

I have noticed that my application doesn't work properly and I'm almost sure that there is a bug whith LinqConnect in Where clauses.

I have verified it with two selects:

The first one:

Code: Select all

//0
var dt = new DataTable();
dt.Columns.AddRange(new[]
       {
           new DataColumn("CODIGO",typeof(Int32)),
           new DataColumn("NOMBRE",typeof(string)) 
       });

dt.PrimaryKey = new[] {dt.Columns["CODIGO"]};
//1
var subt = CurrentIdiomaTable.Where(c => c.LANCHP_0 == capitulo);
//2
var spanishSubT = SpanishIdiomaTable.Where(c => c.LANCHP_0 == capitulo);
//3
spanishSubT = spanishSubT.Where(c => !subt.Select(d => d.LANNUM_0).Contains(c.LANNUM_0));
//4
foreach (var table in subt)
{
    var dr = dt.NewRow();
    dr["CODIGO"] = table.LANNUM_0;
    dr["NOMBRE"] = table.LANMES_0;
    dt.Rows.Add(dr);
}
foreach (var table in spanishSubT)
{
    var dr = dt.NewRow();
    dr["CODIGO"] = table.LANNUM_0;
    dr["NOMBRE"] = table.LANMES_0;
    dt.Rows.Add(dr);
}
The second one:

Code: Select all

var clis = Context.BPDLVCUSTs.Where(c => c.BPCNUM_0 == codCliente).ToArray();
For the first one, I have verified that sometimes, where clause in line 3 doesn't work and doesn't filter the result correctly. When it occours, I obtain an exception becouse it tryes to duplicate the primary key in the second foreach ("System.Data.ConstraintException: Column 'CODIGO' is constrained to be unique").

In the second example it occours the same. But I have more info in this case:
I'm executing step by step my app and when I execute the line posted, sometimes I get one register and sometimes I don't get any registers when I should obtain one.
When it occours, I verify that in context I have a total of 2005 registers (with sqldeveloper I check number of registers in the table and it is correct). So the problem is in where clause, becouse the register I'm looking for is in the context.
If I do the same select in sqldeveloper I obtain the register I'm looking for.
And with LinqConnect it's aleatory. Sometimes I obtain the register and sometimes 0 registers.

Do you have idea where is the problem?

Thank you and sorry for my english.

malea
Posts: 33
Joined: Fri 25 Jan 2013 08:25

Re: Bug in Where clauses

Post by malea » Thu 07 Nov 2013 09:49

I have more info:

When the select fails (.where clause concretely), it continues failing until I restart my application. After restarting the application it returns to work well. And I continue working until it fails again (or not, becouse it's random).

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Bug in Where clauses

Post by MariiaI » Thu 07 Nov 2013 10:59

There was a fix, related to this issue in dotConnect for Oracle 8.0.17, released on 17-Oct-13:
The bug with the compiled query cache when query contains parameters with the same values is fixed (please refer to the revision history).
Please update the version of your dotConnect for Oracle or try disabling the compiled query cache (please refer here for more information about disabling it: http://forums.devart.com/viewtopic.php?t=28070).
Please tell us about the results.

malea
Posts: 33
Joined: Fri 25 Jan 2013 08:25

Re: Bug in Where clauses

Post by malea » Mon 11 Nov 2013 14:24

MariiaI wrote:There was a fix, related to this issue in dotConnect for Oracle 8.0.17, released on 17-Oct-13:
The bug with the compiled query cache when query contains parameters with the same values is fixed (please refer to the revision history).
Please update the version of your dotConnect for Oracle or try disabling the compiled query cache (please refer here for more information about disabling it: http://forums.devart.com/viewtopic.php?t=28070).
Please tell us about the results.
I have updated dotConnect for Oracle to version 8.1.26.0 and I have reproduced the same issue after 30 minutes of tests.

More info:
I have disabled the compiled query cache in version 8.1.26.0 and I'm 60 minutes testing my app without reproducing the issue. I coninue my tests...

It's possibly a problem of compiled cache, but the bug related is not the same.

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Bug in Where clauses

Post by MariiaI » Tue 12 Nov 2013 07:30

It's possibly a problem of compiled cache, but the bug related is not the same.
Please send us a test project (with the DDL/DML scripts for database objects), with which this issue could be reproduced and specify the exact steps for your scenario, so that we are able to investigate it in more details and find a solution for you.
As a workaround, please disable the compiled query cache.
JIC: The predefined templates were improved since dotConnect for Oracle 8.0.17: new template setting, "Use Compiled Query Cache", was added. To use it please open your model by double-clicking the *.lqml file, then expand the Templates node in Model Explorer, right-click the "LinqConnect" template, and select properties from the shortcut menu, set the "Use Compiled Query Cache" property to False and save the changes.

malea
Posts: 33
Joined: Fri 25 Jan 2013 08:25

Re: Bug in Where clauses

Post by malea » Tue 12 Nov 2013 09:14

Disabling compiled query cache could affect performance?

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Bug in Where clauses

Post by MariiaI » Tue 12 Nov 2013 12:51

LinqConnect provides compiled queries to shorten the time for translating and materializing - two main tasks, which LinqConnect performs when executing a query:
- translates the LINQ query (or a sequence of extension method calls) to SQL commands;
- constructs (or 'materializes') entity objects from a 'plain' result set returned by the server.
Generally, both translator and materializer are pretty fast. Thus, there shouldn't be significant changes in the performance when disabling compiled query cache.
For more information please refer to:
http://www.devart.com/linqconnect/docs/ ... eries.html

Post Reply