ServiceStack.OrmLite Select<> throws npgsql syntax error when using WITH CTE

Question

From the error I thought this was an issue with Npgsql (see closed issue), however the error is with OrmLite Select<> as it's changing the executed sql.

Question:

1. Other than not using the WITH CTE is there another way around this error in OrmLite?
2. Is db.Select<> the wrong command to be using?

Note: WITH CTE works with OrmLite.Scalar

Postgres WITH CTE: http://www.postgresql.org/docs/current/static/queries-with.html

UPDATE: Issue seems to be with OrmLite preparing the SQL statement and it not starting with "SELECT" causes OrmLite to treat the SQL as a "WHERE" param.

[Test]
public void with_cte_ormlite_obj()
{
    using (var db = DbConnection)
    {
        var sql = "WITH w_cnt AS (SELECT 5 AS cnt, 'me' AS name) SELECT cnt, name FROM w_cnt";

// An exception of type 'Npgsql.NpgsqlException' occurred in Npgsql.dll
// ERROR: 42601: syntax error at or near "WITH w_cnt"
// Actual Exec Sql: 
// SELECT "cnt", "name" FROM "my_with_cte_obj" WHERE WITH w_cnt AS (SELECT 5 AS cnt, 'me' AS name) SELECT cnt, name FROM w_cnt

        var cnt = db.Select<MyWithCteObj>(sql);
        var first = cnt.First();
        Assert.AreEqual(5, first.Cnt);
        Assert.AreEqual("me", first.Name);
    }
}

public class MyWithCteObj
{
    public int Cnt { get; set; }
    public string Name { get; set; }
}

Answer 1

The db.Select<T>() API should only by used for SQL SELECT statements.

The db.SqlList<T>() API should be used for non-SELECT queries, e.g:

using (var db = DbConnection)
{
    var cnt = db.SqlList<MyWithCteObj>(
        "WITH w_cnt AS (SELECT 5 AS cnt, 'me' AS name) SELECT cnt, name FROM w_cnt");
}

See the docs for more custom SQL APIs examples.