Linq left join on string values fetching incorrect data from Oracle database

Question

Working with a left join on an Oracle DB is causing some interesting data issues to be returned when using Linq2Db as the mapping layer.

SELECT * FROM REBATE_HISTORY frh 
LEFT JOIN PAYMENT fp 
    ON frh.TRANSACTION_REFERENCE_NUMBER = fp.TRANSACTION_REFERENCE_NUMBER

Running this select against a specific Rebate year entry returns 8 entries from the database table, due to 3 years not having associated payments. However the following linq statement:

from rebateHistory in database.RebateHistory
    join payments in database.Payments
    on rebateHistory.ReferenceNumber equals payments.ReferenceNumber into paymentHistory
    from payment in paymentHistory.DefaultIfEmpty()

Causes the following SQL query to be generated which in turn returns 3000+ entries

FROM REBATE_HISTORY rebateHistory
LEFT JOIN PAYMENT paymentHistory 
  ON (rebateHistory.TRANSACTION_REFERENCE_NUMBER IS NULL AND paymentHistory.TRANSACTION_REFERENCE_NUMBER IS NULL 
    OR rebateHistory.TRANSACTION_REFERENCE_NUMBER = paymentHistory.TRANSACTION_REFERENCE_NUMBER )

What is going wrong that the generated SQL is including the IS NULL check for both column values?

The DefaultIfEmpty() is a left outer join. The first query is only outputting items where both RebateHistory and Payments have same ReferenceNumber. The second query is outputting every entry in database in the reebateHistory even when there is no matching ReferenceNumber in the Payments table. — jdweng, Mar 11 '21 at 18:11

score 0 · Accepted Answer · answered Mar 12 '21 at 17:28

As was pointed out by svyatoslav-danyliv the configuration for Linq2Db by default uses CompareNullsAsValues which causes the generated SQL to include null checks on both fields.

The fix is in the constructor of your DataConnection object to define:

Linq2Db.Common.Configuration.Linq.CompareNullsAsValues = false

Linq left join on string values fetching incorrect data from Oracle database

1 Answers1