LinqToDB查询未生成select 1，始终生成select *的解决方法

问题场景 #

你需要生成包含SELECT 1的NOT EXISTS子句，目标SQL如下：

and    not exists ( select 1
                    from    pdi.tblPDIVehicleCheckDetail pvcd
                      inner join pdi.tblPDIVehicleCheckDetailPFNA pvcdp on pvcd.PDIVehicleCheckDetailID = pvcdp.PDIVehicleCheckDetailID
                    where pvcd.CompoundVehicleID = cvsl.CompoundVehicleID
                  ) 

但你编写的Linq代码：

&& (from pvcd in _context.GetTable<tblPDIVehicleCheckDetail>()
       join pvcdp in _context.GetTable<tblPDIVehicleCheckDetailPFNA>() on pvcd.PDIVehicleCheckDetailID equals pvcdp.PDIVehicleCheckDetailID
     where pvcd.CompoundVehicleID == cvsl.CompoundVehicleID
     select LinqToDB.Sql.Expr<int>("1") 
    ).Any() == false   

生成的SQL却是：

(NOT EXISTS(
        SELECT
            *
        FROM
            [pdi].[tblPDIVehicleCheckDetail] [pvcd]
                INNER JOIN [pdi].[tblPDIVehicleCheckDetailPFNA] [pvcdp] ON [pvcd].[PDIVehicleCheckDetailID] = [pvcdp].[PDIVehicleCheckDetailID]
        WHERE
            [pvcd].[CompoundVehicleID] = [cvsl].[CompoundVehicleID]
    ))

尝试修改select pvcd后问题依旧。

解决方案 #

LinqToDB的Any()方法会被框架默认优化为EXISTS(SELECT *)，若要强制生成SELECT 1，需使用Exists()方法并明确指定选择常量1，替换原有的Any() == false逻辑：

&& !LinqToDB.LinqExtensions.Exists(
    from pvcd in _context.GetTable<tblPDIVehicleCheckDetail>()
    join pvcdp in _context.GetTable<tblPDIVehicleCheckDetailPFNA>() 
        on pvcd.PDIVehicleCheckDetailID equals pvcdp.PDIVehicleCheckDetailID
    where pvcd.CompoundVehicleID == cvsl.CompoundVehicleID
    select 1
)

原理说明 #

• Any()是Linq标准方法，LinqToDB对其做了特定优化，自动生成SELECT *的EXISTS子句；
• Exists()是LinqToDB的扩展方法，会严格遵循你指定的SELECT内容，当你选择常量1时，就会生成SELECT 1的EXISTS子句。

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