Linq2db left join results in strange SQL

Question

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

Answer 1

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()