How to add braces to where-query using sqlkata

Question

I have the following concern: I have been dealing with sqlkata for a few hours, but I am not getting anywhere with the following problem: I want to create a select statements with linked where clauses. However, I can't find a way to set braces. My current SQL statement looks like this:

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

What I need though is:

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

Note the added braces in the third line.

My current sqlkata statement looks like this:

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)

I'm sure it's not a hard problem. I guess I miss the solution all the time. Thanks to all who help.

score 3 · Accepted Answer · answered Apr 30 '21 at 17:35

According to Nested Conditions documentation from SqlKata:

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

So in your example it would be:

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)

How to add braces to where-query using sqlkata

1 Answers1