Linq2db左连接生成异常SQL，如何强制去除OR条件？

使用linq2db 4.3版本，以下LINQ查询的连接部分：

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

生成的SQL语句为：

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]

去除其中的OR [w].[CSVVerkäufer] IS NULL AND [user1].[RPTSalesId] IS NULL部分后可得到预期结果。相关模型定义如下：

PrtUser类：

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

PrtCompany类：

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; }
}

请问是否可以强制该连接生成的SQL省略上述OR...部分？

可以通过以下几种方式实现：

• 显式控制类型匹配，避免NULL相等逻辑
  将CSVVerkäufer的强制转换改为可空类型，匹配RPTSalesId的int?类型，同时添加过滤条件排除不需要的NULL匹配场景：

  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 = user.RPTSalesId, CardCode = user.CardCode } into user1
  from user2 in user1.DefaultIfEmpty()
  where allRows.CSVVerkäufer != null || user2 == null
  

  这种方式让linq2db不再生成NULL相等的OR分支，同时通过where条件保证逻辑符合预期。

• 使用linq2db原生方法指定不处理NULL相等
  利用Sql.Ext.Equals方法，通过treatNullAsEqual参数明确禁止生成NULL匹配逻辑：

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

  该方法直接告知linq2db，仅当两个字段非空且值相等时才匹配，不会生成OR分支。

• 使用null-forgiving运算符明确字段非空
  如果业务场景中CSVVerkäufer和RPTSalesId不会为NULL，可以使用C#的null-forgiving运算符!标记字段非空，让linq2db跳过NULL匹配逻辑：

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

  注意：仅当你能确保字段不会为NULL时使用该方法，否则可能引发数据不匹配问题。

内容的提问来源于stack exchange，提问作者fops