Dynamic Select Statements

It is also possible to query tables without having a corresponding Type by specifying the table, schema and column name explicitly.

The return value for dynamic methods is IEnumerable<dynamic>

Dynamic Select Statement are still experimental and API may change in future version of SimpleStack. Feedback is more than welcome !

conn.Select("TestType2",x => x.Select("id","textcol")
                              .Where("textcol",(string y) => y.Substring(0, 1) == "a")
                              .And("id", (int y) => y > 10)
                              .Limit(1, 1));
//SELECT id, textcol FROM TestType2 WHERE SUBSTR(textcol,0,1) = 'a' AND id > 10 LIMIT 1 OFFSET 1

The column name or where expression can actually contains any valid SQL, it is therefore possible to do some more complex queries.

conn.Select("TestType2",
            x => x.Select("id","CASE WHEN mod(id,2) = 0 THEN 'E' ELSE 'O' END AS even_odd")
                  .Where<char>("CASE WHEN mod(id,2) = 0 THEN 'E' ELSE 'O' END", y => y == 'O'));
// SELECT id FROM TestType2 WHERE (case when mod(id,2) = 0 then 'E' else 'O' end) = 'O'

The statements are sent as is to the backend database, therefore pay attention that :

It's the caller responsibility to sanitize these statements to avoid SQL injections, ...
The query can contains database specific statement that could break port to multiple database

You can use the DialectProvider on the Connection to help you generating database specific queries:

var idCol = conn.DialectProvider.GetQuotedColumnName("id");
var textCol = conn.DialectProvider.GetQuotedColumnName("textcol")

conn.Select("TestType2",x => x.Select(idCol,textCol)
                              .Where(textCol,
                                     (string y) => y.Substring(0, 1) == "a")
                              .And(idCol, (int y) => y > 10)
                              .Limit(1, 1));
//SELECT id, textcol FROM TestType2 WHERE SUBSTR(textcol,0,1) = 'a' AND id > 10 LIMIT 1 OFFSET 1

Method signatures

Dynamic queries can be used when you do not have a Poco and want to generate database specific queries.

Task<IEnumerable<dynamic>> SelectAsync(string tableName, 
                                       Action<DynamicSelectStatement> selectStatement,
                                       CommandFlags flags = CommandFlags.Buffered)
Task<IEnumerable<dynamic>> SelectAsync(string tableName, 
                                       string schemaName,
                                       Action<DynamicSelectStatement> selectStatement,
                                       CommandFlags flags = CommandFlags.Buffered)
Task<long> CountAsync(string tableName, 
                      Action<DynamicSelectStatement> expression)
Task<long> CountAsync(string tableName,
                      string schemaName, 
                      Action<DynamicSelectStatement> expression)

SimpleStack - Querying Data - Dynamic

Dynamic Select Statements

Method signatures