Coalesce issue

[BretHardin]

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

[StanislavK]

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?

[BretHardin]

Thanks, that works!