如何将VB.NET LINQ中BREATHRESULT为空的条件转为SQL语句

原VB.NET LINQ查询： #

Dim i = ( _
    From o In model.DUIMAINs _
        .Include("VIOLATOR") _
        .Include("ARRESTINGOFFICER") _
        .Include("DR15") _
    Where _
        o.BREATHRESULT Is Nothing _
    Order By _
        o.ARRESTDATETIME _
)

现有核对用SQL： #

SELECT
    *

FROM
    "DUI" ."DUI_MAIN"           main,
    "DATA"."TBL_TRAFFIC_PERSON" violator,
    "HRIS"."TBL_PERSONNEL"      ARRESTINGOFFICER,
    "DUI" ."DR15"               dr15,
    "DUI" ."BREATHRESULTS"      BREATHRESULTS

WHERE
    main.Duiguid = 'deaee240-cdc3-4b50-b215-51307e7d96a5'
    and
    main.Duiguid = violator.seq_guid
    and
    main.arrestingofficerusername = ARRESTINGOFFICER.USERNAME
    and
    main.Duiguid = dr15.Duiguid

解决方案： #

LINQ中的o.BREATHRESULT Is Nothing表示当前DUIMAIN记录没有对应的BREATHRESULTS关联记录，因为BREATHRESULT是导航属性，为空即无匹配关联。

现有SQL使用了隐式内连接（逗号分隔表），内连接会自动过滤掉没有匹配关联的记录，因此需要调整为左连接BREATHRESULTS表再判断关联记录不存在，或者使用NOT EXISTS子查询实现，两种写法如下：

写法1：左连接 + 判空

SELECT
    *
FROM
    "DUI"."DUI_MAIN"           main
    JOIN "DATA"."TBL_TRAFFIC_PERSON" violator ON main.Duiguid = violator.seq_guid
    JOIN "HRIS"."TBL_PERSONNEL"      ARRESTINGOFFICER ON main.arrestingofficerusername = ARRESTINGOFFICER.USERNAME
    JOIN "DUI"."DR15"               dr15 ON main.Duiguid = dr15.Duiguid
    LEFT JOIN "DUI"."BREATHRESULTS"  BREATHRESULTS ON main.Duiguid = BREATHRESULTS.Duiguid
WHERE
    main.Duiguid = 'deaee240-cdc3-4b50-b215-51307e7d96a5'
    AND BREATHRESULTS.Duiguid IS NULL

写法2：NOT EXISTS子查询

SELECT
    *
FROM
    "DUI"."DUI_MAIN"           main
    JOIN "DATA"."TBL_TRAFFIC_PERSON" violator ON main.Duiguid = violator.seq_guid
    JOIN "HRIS"."TBL_PERSONNEL"      ARRESTINGOFFICER ON main.arrestingofficerusername = ARRESTINGOFFICER.USERNAME
    JOIN "DUI"."DR15"               dr15 ON main.Duiguid = dr15.Duiguid
WHERE
    main.Duiguid = 'deaee240-cdc3-4b50-b215-51307e7d96a5'
    AND NOT EXISTS (
        SELECT 1
        FROM "DUI"."BREATHRESULTS" br
        WHERE br.Duiguid = main.Duiguid
    )

说明： #

• 写法1通过左连接保留所有DUIMAIN记录，再筛选出BREATHRESULTS关联字段为空的记录，即无匹配关联的情况。
• 写法2通过NOT EXISTS直接判断当前DUIMAIN在BREATHRESULTS中没有对应记录，逻辑更直观，部分数据库下性能更优。

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