Linq2db left join results in strange SQL

linq2db: Version 4.3

Following LINQ query (at least the join part)

join company in _connection.PrtCompany on allRows.CSVHändlerNr equals company.RPTCompanyCode
join user in _connection.PrtUser on new { User = (long)allRows.CSVVerkäufer, CardCode = company.CardCode } equals new { User = (long)user.RPTSalesId, CardCode = user.CardCode } into user1
from user2 in user1.DefaultIfEmpty()

Results in:

LEFT JOIN [dbo].[ITF_User] [user1] ON ([w].[CSVVerkäufer] = [user1].[RPTSalesId] OR [w].[CSVVerkäufer] IS NULL AND [user1].[RPTSalesId] IS NULL) AND [company].[CardCode] = [user1].[CardCode]

I get my expected results if I remove the OR [w]... part.

Models:

User:

    public partial class PrtUser : ITable
    {
        [Identity]
        [PrimaryKey, NotNull] public int CntctCode { get; set; } // int
        [Column, NotNull] public string CardCode { get; set; } // nvarchar(15)
        [Column, Nullable] public string FirstName { get; set; } // nvarchar(50)
        [Column, Nullable] public string LastName { get; set; } // nvarchar(50)
        [Column, Nullable] public int? RPTSalesId { get; set; } // nvarchar(50)
    }

    public partial class PrtCompany : ITable
    {
        [PrimaryKey, NotNull] public string CardCode { get; set; } // nvarchar(15)
        [Column, Nullable] public string CardName { get; set; } // nvarchar(100)
        [Column, Nullable] public string CardType { get; set; } // char(1)
        [Column] public int RPTCompanyCode { get; set; }
    }

Is is possible to force the join to omit the OR... part?

Would be great if somebody could confirm that

Solution

It is how comparison works. If both fields are nullable, linq2db is trying to compare NULLs also.

You can disable this logic globally:

LinqToDB.Common.Configuration.Linq.CompareNullsAsValues = false;

Or via DataOptions starting from linqdb 5.0

options = options.UseCompareNullsAsValues(false);

Also you can correct query to filter out nulls before LETF JOIN

join company in _connection.PrtCompany on allRows.CSVHändlerNr equals company.RPTCompanyCode
join user in _connection.PrtUser.Where(u => u.RPTSalesId != null) 
    on new { User = (long)allRows.CSVVerkäufer, CardCode = company.CardCode } equals new { User = (long)user.RPTSalesId, CardCode = user.CardCode } into user1
from user2 in user1.DefaultIfEmpty()