Changes in 2.3.8

Ad-Hoc Inline Table

Contributed by @freakingawesome

the support of Ad-Hoc queries, where you can inject a collection of values, and they will be treated as an inline table inside your query.

    var rateValues = new object[][] {
        new object[] { "2022-01-01", 100 },
        new object[] { "2022-01-03", 200 },
        new object[] { "2022-02-03", 180 },
    };

    var query = new Query("Rates").With(
        "Rates",
        new[] { "Date", "Rate" },
        rateValues
    );

This is equivalent to

WITH [Rates] AS (
    SELECT [Date], [Rate] FROM (
        VALUES ('2022-01-01', 100), ('2022-01-03',  200), ('2022-02-03', 180)
    ) AS tbl ([Date], [Rate])
)

SELECT * FROM [Rates]

New overload for Select()

Contributed by @asherber

A new overload is now available for the Select() method, that takes an IEnumerable<string> as parameter.

var columns = new List<string> {"Id", "Date", "Title"};

new Query("Table").Select(columns);

Where(boolean) is now identical to WhereTrue and WhereFalse

Contributed by @ahmad-moussawi

Previously when passing a boolean to the Where method, SqlKata will send it as is, this lead to inconsistent behavior as with the WhereTrue and WhereFalse method, now SqlKata will delegate the function call to WhereTrue and WhereFalse whenever detecting a boolean value.

Fixing a bug for Include when the foreign value is null.

Contributed by @User00015

Previously when the foreign value is NULL, an unhandled exception is thrown, this fix check for the foreign value before trying to map the values.

Case Insensitive Comparison enhancement for Postgres

Contributed by @ahmad-moussawi

The WhereLike("Column1", "%Upper Word%", false) method takes a third argument to indicate whether the comparison is case sensitive or not, where it uses the LOWER function (or similar) to do the comparison.

While this is handy, but it's not optimal in terms of performance, in this release we favor the native ilike function from Postgres instead.

SqlServer is now using the new Offset Pagination by default

Contributed by @Wulfheart

Previously SqlServerCompiler was using the legacy pagination by default for maximum compatibility, unless setting the UseLegacyPagination to true explicitly

By now, it's safe to set the new pagination as default.

Warning: If you still depend on an old version of SqlServer you have to change the value to true.

Github Action: Integration tests for MySql are now available in the pipeline

Contributed by @ahmad-moussawi

In terms of testing, SqlKata was covered by Unit Tests from the day one, but mostly for the Sql Generation part, in this release we worked hard to push it to the next level.

We've added the support for real integration tests, while this is available only for MySql at the moment, we are planning to expand the support for other vendors.