"equals" operator in "join" statement. Unwanted SQL code.

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
mars
Posts: 15
Joined: Tue 14 Jul 2009 13:56

"equals" operator in "join" statement. Unwanted SQL code.

Post by mars » Mon 19 Oct 2009 09:30

Hello!

dotConnect builds following SQL fragment for LINQ equals operator :

("Extent2".a = "Extent4".a) OR (("Extent2".a IS NULL) AND ("Extent4".aD IS NULL)

Is it possible to clean “IS NULL” parts?
Leave only "Extent2".a = "Extent4".a

a is not null column

Thanks

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Mon 19 Oct 2009 14:58

Are you using LINQ to Oracle?
If yes, please specify the attributes of the columns the entities are joined by and the attributes of the association(if there are any) between the tables that are joined.

mars
Posts: 15
Joined: Tue 14 Jul 2009 13:56

Post by mars » Tue 20 Oct 2009 07:48

Hello

No, It's LINQ to Entity

1. Database creating script
create table t1
(
t1_a1 number(12) not null,
t1_a2 number(12) not null,
constraint pk_t1 primary key (t1_a1)
);
create table t2
(
t2_b1 number(12) not null,
t2_b2 number(12) not null,
constraint pk_t2 primary key (t2_b1)
);

2. Model was generated from database. One-to-One, without modifications.

3. LINQ

from t1 in context.T1
join t2 in context.T2 on t1.T1_A2 equals t2.T2_B1
elect new { t1.T1_A1, t2.T2_B2 };

4. SQL

SELECT
1 AS C1,
"Extent1".T1_A1 AS T1_A1,
"Extent2".T2_B2 AS T2_B2
FROM PXBANK.T1 "Extent1"
INNER JOIN PXBANK.T2 "Extent2" ON ("Extent1".T1_A2 = "Extent2".T2_B1) OR (("Extent1".T1_A2 IS NULL) AND ("Extent2".T2_B1 IS NULL))


It's just example. In real world such SQLs executes extremly slowly.
Could you add option which allow to generate short comparison sql statement ?

("Extent1".T1_A2 = "Extent2".T2_B1) OR (("Extent1".T1_A2 IS NULL) AND ("Extent2".T2_B1 IS NULL))
=====>
("Extent1".T1_A2 = "Extent2".T2_B1

Please.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 21 Oct 2009 14:17

Unfortunately, this behaviour is designed in Entity Framework v1.
As an alternative, I'd recommend you to try LINQ to Oracle.
I have just checked and the query was generated without null comparisons.

Post Reply