Parameterisation of pagination clauses

ServiceStack.OrmLite SQL Server
1

This OrmLite statement

db.Select(db.From<Customer>().Limit(1).Where(c => c.Name == "Mythz"));

Generates the following SQL (SqlServer2012Dialect):

SELECT "Id", "Name" 
FROM "Customer"
WHERE ("Name" = @0) ORDER BY 1 OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY

Ideally I’d like the page size to be parameterized so plans can be reused like this:

SELECT "Id", "Name" 
FROM "Customer"
WHERE ("Name" = @0) ORDER BY 1 OFFSET 0 ROWS FETCH NEXT @1 ROWS ONLY

(I’m not actually using it for paging and there is a large range of values for @1.)

Is there an option for this without resorting to custom SQL or should I raise a feature request? I’m using SS 5.11 on .NET 5.

Thanks :slight_smile:

2

Not the way it’s been designed, all SqlExpression’s calls their Dialect’s ToSelectStatement() to return the generated SQL string conforming to their RDBMS dialect.

You have an opportunity to modify the SQL that gets executed with a Custom SqlExpression Filter where you’d basically need to parse the SQL suffix and replace the value with a param, you should be able to add a SqlExpression<T>.ParameterizedLimit(limit) extension method to make calling it nicer.

The other way to avoid parsing is to inherit from SqlServerExpression<T> and override string ToSelectStatement() to use your own custom implementation that constructs the SQL with params.

You can make use of it by creating a custom SQL Server provider and returning your SqlServerExpression<T> in its SqlExpression<T>() method, e.g:

public class MySqlServerOrmLiteDialectProvider 
    : SqlServer2012OrmLiteDialectProvider
{
        public new static MySqlServerOrmLiteDialectProvider Instance = 
            new MySqlServerOrmLiteDialectProvider();

        public override SqlExpression<T> SqlExpression<T>() => 
            new MySqlServerExpression<T>(this);
}

Then you’d use your custom dialect provider implementation instead:

var dbFactory = new OrmLiteConnectionFactory(connString, 
    MySqlServerOrmLiteDialectProvider.Instance);
1 Like
3

Thanks for the suggestions