Entity Linq function Contains() generates incomplete SQL query when using NUMBER(20) mapped to Decimal

Entity Linq function Contains() generates incomplete SQL query when using NUMBER(20) mapped to Decimal

Postby jkaralis » Mon 15 Jun 2015 13:25

How to reproduce:

DATABASE script:

Code: Select all
CREATE TABLE MYMODEL.TESTIDS (
   ID NUMBER(20) NOT NULL,
   NAME VARCHAR2(6 CHAR) NULL,
   CONSTRAINT PK_TESTIDS PRIMARY KEY (ID)
);   

Code C#:
Code: Select all
var idlist = new List<decimal>();
decimal startId = 12345678901234567890M;  //(20digits)
for (decimal i = 0; i < 1200; i++)
    idlist.Add(startId + i);

using ( DbContext ctx = MyModel.GetNewContext())
{
    var resultq = ctx.Set<TESTIDS>().Where(x => idlist.Contains(x.ID));
    var result = resultq.ToList();
}


When executed, DBMonitor shows only 2 values (with high distance and result respectively wrong ):

Code: Select all
SELECT
"Extent1".ID,
"Extent1".NAME
FROM TACTICAL.TESTIDS "Extent1"
WHERE ("Extent1".ID IN (12345678901234567890)) OR ("Extent1".ID IN (12345678901234568402))


Note that when using 14 actual digits (6 MSB digits zero) startId = 00000078901234567890M, the SQL result is fine.

Using
DotConnect for Oracle v8.4.379
Entity Framework v6 (6.1.2)
Oracle Database 11g (Same issue on 12c)
Instant Client v11
jkaralis
 
Posts: 2
Joined: Mon 15 Jun 2015 13:11

Re: Entity Linq function Contains() generates incomplete SQL query when using NUMBER(20) mapped to Decimal

Postby Shalex » Tue 16 Jun 2015 11:41

Thank you for your report. We have reproduced the issue and are investigating it. We will notify you about the result.
Shalex
Devart Team
 
Posts: 7616
Joined: Thu 14 Aug 2008 12:44

Re: Entity Linq function Contains() generates incomplete SQL query when using NUMBER(20) mapped to Decimal

Postby Shalex » Fri 19 Jun 2015 10:08

The bug with loosing elements within the IN clause of the generated SQL for the LINQ Contains clause is fixed in the latest (8.4.437) build of dotConnect for Oracle: http://forums.devart.com/viewtopic.php?f=1&t=31996.

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).
Shalex
Devart Team
 
Posts: 7616
Joined: Thu 14 Aug 2008 12:44

Re: Entity Linq function Contains() generates incomplete SQL query when using NUMBER(20) mapped to Decimal

Postby jkaralis » Mon 22 Jun 2015 11:51

Thank you
jkaralis
 
Posts: 2
Joined: Mon 15 Jun 2015 13:11


Return to dotConnect for Oracle