SqlKata Tips and Tricks
While SqlKata have a simple API and it’s easy to use, here are some semi-hidden features that you may find them useful.
- Group where conditions inside brackets
Use the Where(q => q.Where(…)) overload
2. WhereNull, WhereTrue, WhereFalse and WhereLike
SqlKata provides some shortcuts to perform defined operations so you can save some keystrokes
3. Perform string operations
You can use the WhereContains, WhereStarts and WhereEnds instead of using the like method
Note: all these methods perform a case insensitive comparison, passing a boolean true will force a case sensitive one.
4. Write complex Where Conditions
You can use WhereRaw to write some free expressions, but be aware of SQL injections, use the ? to pass a parameter.
Wrap the columns with the [] so SqlKata considers them as columns identifier, useful for Case Sensitive Databases like PostgreSQL.
Check more info on the SqlKata website https://sqlkata.com.
5. Select from SQL functions
This one is very similar to the previous tip, sometimes you need the full flexibility to select from an arbitrary function, so the FromRaw will be handy in this case.
You can also use this for using some unsupported features, like the CROSS APPLY in SQL server or using in PostgreSQL.