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

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

Postby 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
mars
 
Posts: 15
Joined: Tue 14 Jul 2009 13:56

Postby 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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby 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.
mars
 
Posts: 15
Joined: Tue 14 Jul 2009 13:56

Postby 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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle