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

mars · 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 · 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 · 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 · 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.