SqlKata NotIn Command

Question

I am using SqlKata purely to build SQL queries in C#. The query I need contains the NOT IN command but I don't know how to write it in C#.

This is my SQL query:

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

I am trying to get the above query, I am here now

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)

Does anyone know how can I write the part "UID NOT IN" I wrote in SQL?

Answer 1

Use WhereNotIn(). Documentation.

Pass an IEnumerable to apply the SQL WHERE IN condition.

new Query("Posts").WhereNotIn("AuthorId", new [] {1, 2, 3, 4, 5});

SELECT * FROM [Posts] WHERE [AuthorId] NOT IN (1, 2, 3, 4, 5)

You can pass a Query instance to filter against a sub query

var blocked = new Query("Authors").Where("Status", "blocked").Select("Id");
new Query("Posts").WhereNotIn("AuthorId", blocked);

SELECT * FROM [Posts] WHERE [AuthorId] NOT IN (SELECT [Id] FROM [Authors] WHERE [Status] = 'blocked')

Note: The sub query should return one column