Devart Forums

Given a query similar to this:
from cm2 in ProjectDataModel.VW_PROJECT_CATALOG
join cm in ProjectDataModel.VW_PROJECT_CATALOG
on new{ PRODID = cm2.PRODID, PRODUNIT = cm2.PROD_UNIT_ID }
equals new { PRODID = cm.PRODID, PRODUNIT = cm.PROD_UNIT_ID}
where productStatus.Contains(cm.PRODSTATUS)
&& processedStatus.Contains(cm2.PRODSTATS)
select cm);

We end up getting a bunch of null checks in the generated sql

FROM PROJECT.VW_PROJECT_CATALOG t1
INNER JOIN PROJECT_APP.VW_PROJECT_CATALOG t2
ON ((t1.PRODID = t2.PRODID)
OR ((t1.PRODID IS NULL) AND (t2.PRODID IS NULL)))
AND ((t1.PROD_UNIT_ID = t2.PROD_UNIT_ID)
OR ((t1.PROD_UNIT_ID IS NULL) AND (t2.PROD_UNIT_ID IS NULL)))
WHERE (t2.PRODSTATUS IN (:p4,:p5))
AND (t1.PRODSTATUS IN (:p6,:p7,:p8))

We have set the columns in the model to Nullable = false, we are unable to modify the db columns themselves as we don't have access to modify them. Is there any other way to get around having this null check generated in the sql? It's causing terrible performance.

Could you please specify the following:
- the DBMS (and its version) you are working with;
- the product (e.g., dotConnect for Oracle or LinqConnect) you are using, and its edition and version;
- the definitions of the database objects you are accessing.

We couldn't reproduce such behaviour when working with LinqConnect build 3.1.34 and MySQL server 5.5

1. the DBMS is Oracle 11g
2. Product we are using is dotConnect
3a. PRODID: NUMBER (19) - NOT NULLABLE
3b. PROD_UNIT_ID: NUMBER (19) - NOT NULLABLE

Thank you for the clarification. We have reproduced the problem.
We will investigate it and notify you about the results as soon as possible.

The bug with 'IS NULL' checks for non-nullable columns is fixed.
New build of LinqConnect 4.0.21 is available for download now!
It can be downloaded from http://www.devart.com/linqconnect/download.html (trial version) or from Registered Users' Area (for users with active subscription only).

For more information, please refer to http://forums.devart.com/viewtopic.php?f=31&t=24289