如何在C#中使用SqlKata实现NOT IN查询？

需求与现状 #

你需要用SqlKata构建包含NOT IN的SQL查询，目标SQL如下：

SELECT [User].UID as UserUID, FirstName, LastName FROM [USER] 
WHERE [User].[AccountUID] = @p2  
AND UID NOT IN (SELECT  [User].UID
FROM [User] 
OUTER APPLY [User].[UserProducts].nodes('/ArrayOfUserProduct/UserProduct') AS XmlData(UserProductXMLData)
LEFT OUTER JOIN [UserFormProducts] ON [UserFormProducts].[UserUID] = [User].[UID] 
WHERE [User].[DeleteDate] IS NULL 
AND (([UserFormProducts].[Product] = 0 AND [UserFormProducts].[FormUID] = @p1) OR [UserFormProducts].[UserUID] IS NULL)
AND [User].[AccountUID] = @p2 
)

注：目标SQL中子查询的SELECT [User]应为笔误，实际需返回[User].UID才能匹配NOT IN条件。

你已完成子查询的部分逻辑，现有代码如下：

var countQuery = new Query("User")
                        .Join("User.[UserProduts].nodes('/ArrayOfUserProducts/UserProducts') as XmlData(UserProductXMLData)", j => j, "OUTER APPLY")
                        .LeftJoin("UserFormProducts", "UserFormProducts.UserUID", "User.UID")
                        .WhereNull("User.DeleteDate")
                        .Where(x => x.Where("UserFormProducts.Product", 0).OrWhereNull("UserFormProducts.UserUID"))
                        .Where("UserFormProducts.FormUID", formUID)
                        .Where("User.AccountUID", accountUID)

实现步骤 #

1. 修正并完善子查询 #

首先调整子查询，明确返回User.UID字段，同时修正拼写错误（比如UserProduts改为UserProducts，XML节点路径与目标SQL对齐）：

var subQuery = new Query("User")
    .Join("User.[UserProducts].nodes('/ArrayOfUserProduct/UserProduct') as XmlData(UserProductXMLData)", j => j, "OUTER APPLY")
    .LeftJoin("UserFormProducts", "UserFormProducts.UserUID", "User.UID")
    .WhereNull("User.DeleteDate")
    .Where(x => x
        .Where("UserFormProducts.Product", 0)
        .Where("UserFormProducts.FormUID", formUID)
        .OrWhereNull("UserFormProducts.UserUID")
    )
    .Where("User.AccountUID", accountUID)
    .Select("User.UID"); // 指定返回UID字段，用于NOT IN匹配

2. 构建主查询并添加NOT IN条件 #

使用SqlKata的WhereNotIn方法，将子查询作为参数传入，实现目标SQL的NOT IN逻辑：

var mainQuery = new Query("User")
    .Select("User.UID as UserUID", "FirstName", "LastName")
    .Where("User.AccountUID", accountUID)
    .WhereNotIn("User.UID", subQuery); // 核心：用WhereNotIn实现NOT IN子查询

关键说明 #

• WhereNotIn是SqlKata原生支持的方法，专门用于构建NOT IN条件，第一个参数为要匹配的字段，第二个参数可以直接传入子查询对象
• 注意修正代码中的拼写错误，确保表名、XML节点路径与目标SQL完全一致，避免执行报错

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