vb.net - 如何使用sqlkata将大括号添加到where-query

Question

我有以下问题:我已经处理 sqlkata 几个小时了，但我没有解决以下问题:我想创建一个带有链接 where 子句的 select 语句。但是，我找不到设置大括号的方法。我当前的 SQL 语句如下所示:

SELECT [id], [filename], [extension], [ocr_full_ready], [pdf_type] FROM [document] 
WHERE [extension] IN ('.eml', '.msg', '.docx', '.xlsx', '.doc', '.xls', '.txt', '.lnk') 
AND [ocr_full_ready] IS NULL OR [ocr_full_ready] BETWEEN 3 AND 9 
AND NOT (LOWER([filename]) like 'gobd%') 
ORDER BY [id] DESC

不过我需要的是:

SELECT [id], [filename], [extension], [ocr_full_ready], [pdf_type] FROM [document] 
WHERE [extension] IN ('.eml', '.msg', '.docx', '.xlsx', '.doc', '.xls', '.txt', '.lnk') 
AND ([ocr_full_ready] IS NULL OR [ocr_full_ready] BETWEEN 3 AND 9)
AND NOT (LOWER([filename]) like 'gobd%') 
ORDER BY [id] DESC

注意第三行添加的大括号。

我当前的 sqlkata 语句如下所示:

Dim query = qf.Query("document").Select("id", "filename", "extension", "ocr_full_ready", "pdf_type") _
                        .WhereIn("extension", New String() {".eml", ".msg", ".docx", ".xlsx", ".doc", ".xls", ".txt", ".lnk"}) _
                        .WhereNull("ocr_full_ready").OrWhereBetween("ocr_full_ready", 3, 9) _
                        .WhereNotLike("filename", "Gobd%") _
                        .OrderByDesc("id") _
                        .Limit(1)

我相信这不是一个难题。我想我一直想念解决方案。感谢所有提供帮助的人。

Answer 1

根据 Nested Conditions来自 SqlKata 的文档:

To group your conditions, just wrap them inside another Where block.

因此在您的示例中它将是:

Dim query = qf.Query("document").Select("id", "filename", "extension", "ocr_full_ready", "pdf_type") _
    .WhereIn("extension", New String() {".eml", ".msg", ".docx", ".xlsx", ".doc", ".xls", ".txt", ".lnk"}) _
    .Where(Function(w) w.WhereNull("ocr_full_ready").OrWhereBetween("ocr_full_ready", 3, 9)) _
    .WhereNotLike("filename", "Gobd%") _
    .OrderByDesc("id") _
    .Limit(1)