Coalesce issue

Coalesce issue

Postby BretHardin » Tue 01 Mar 2011 18:34

LinqConnect converts certain queries to use coalesce and case which slows the query down to a crawl. Is there a way to turn this off?

SELECT t1.ID, t1.CUSTNO, t1.TITLE, t1.STK_NUM, t1.FORMAT, t1.ORIG_TABLE_CODE
FROM MWTINT.ORDER_LINE t1
INNER JOIN MWTINT.MRCCUSTOMER t2 ON t1.CUSTNO = t2.CUSTNO
INNER JOIN MWTINT.MRCCUSTOMERFORMATS t3 ON t1.CUSTNO = t3.CUSTNO
INNER JOIN MWTINT.FORMAT t4 ON (t3.FORMATID = t4.ID) AND ((t1.FORMAT = t4.CODE) OR ((t1.FORMAT IS NULL) AND (t4.CODE IS NULL)))
WHERE (COALESCE(
(CASE
WHEN t1.DATE_EST_DELIVERY > :p0 THEN 1
ELSE 0
END),:p1)) <> 0
-- p0: Input TimeStamp (Size = 0; DbType = DateTime) [2/25/2011 12:00:00 AM]
-- p1: Input Number (Size = 0; DbType = Decimal) [False]
-- Context: Devart.Data.Oracle.Linq.Provider.OracleDataProvider Model: ao Build: 2.20.11.0
BretHardin
 
Posts: 2
Joined: Tue 01 Mar 2011 18:29

Postby StanislavK » Wed 02 Mar 2011 12:43

Such issue may occur for VB LINQ queries which refer to nullable entity properties. It is actually caused by the fact that Visual Basic uses implicit conversions: as nullable types are used in the Where clause, the entire Where condition is converted to 'Nullable(Of Boolean)' instead of being treated as 'Boolean'. Thus, the generated SQL should include COALESCE to handle the case if this statement is null.

To resolve such issues, you can, e.g., explicitly convert the Where condition to Boolean, like
Code: Select all
Dim query = From item in myDataContext.MyEntities
            Where CType( [condition], Boolean)
            Select item

Please tell us if this helps.

In case you've experienced this behaviour with C# LINQ queries, could you please send us a small test project with which the issue can be reproduced?
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby BretHardin » Mon 07 Mar 2011 12:57

Thanks, that works!
BretHardin
 
Posts: 2
Joined: Tue 01 Mar 2011 18:29


Return to dotConnect for Oracle