private SqlProvider VisitPagingRowNumber(PagingProvider provider)
{
var fromParameterBinding = CreateLimitOffsetParameterBinding(provider.From);
var toParameterBinding = CreateLimitOffsetParameterBinding(provider.To);
var bindings = new List <QueryParameterBinding> {
fromParameterBinding, toParameterBinding
};
var compiledSource = Compile(provider.Source);
var source = compiledSource.Request.Statement;
var queryRef = SqlDml.QueryRef(source);
var query = SqlDml.Select(queryRef);
var rowNumberColumn = queryRef.Columns.Last();
query.Columns.AddRange(queryRef.Columns.Cast <SqlColumn>());
query.Where = SqlDml.Between(rowNumberColumn,
fromParameterBinding.ParameterReference,
toParameterBinding.ParameterReference);
return(CreateProvider(query, bindings, provider, compiledSource));
}
public void Test020()
{
string nativeSql = @"SELECT
f.TrackId,
f.Name,
f.UnitPrice
FROM
track f
WHERE
f.Milliseconds BETWEEN 50 AND 40000
ORDER BY f.TrackId";
SqlTableRef track = SqlDml.TableRef(schema.Tables["track"], "f");
SqlSelect select = SqlDml.Select(track);
select.Columns.AddRange(track["TrackId"], track["Name"], track["UnitPrice"]);
select.Where = SqlDml.Between(track["Milliseconds"], 50, 40000);
select.OrderBy.Add(track["TrackId"]);
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
public void Test020()
{
string nativeSql = @"SELECT
f.film_id,
f.title,
f.description,
f.release_year,
f.rental_rate
FROM
film f
WHERE
f.rental_duration BETWEEN 4 AND 5
ORDER BY f.film_id";
SqlTableRef film = SqlDml.TableRef(schema.Tables["film"], "f");
SqlSelect select = SqlDml.Select(film);
select.Columns.AddRange(film["film_id"], film["title"], film["description"], film["release_year"], film["rental_rate"]);
select.Where = SqlDml.Between(film["rental_duration"], 4, 5);
select.OrderBy.Add(film["film_id"]);
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}